我的SELECT会产生任何结果吗 - Does my SELECT produce anything?

Does my SELECT produce anything?

本文关键字：任何结果 SELECT 我的 | 更新日期: 2024-05-21

我的SELECT有什么问题吗？它应该计算每天不同IP的数量，然后计算总和。以下是查询：

SELECT SUM(NumIpsPerDay) AS user_hits
FROM
  (SELECT DATE(tracking.timestamp) AS DayTrackCount,
          COUNT(DISTINCT tracking.ip) AS NumIpsPerDay
   FROM USER,
        tracking
   GROUP BY DATE(tracking.timestamp)
   WHERE tracking.user_id = $user_id
   GROUP BY $user_id LIMIT 1)

我看着它，我想这可能是日期。

我的SELECT有什么问题吗？

SELECT SUM(NumIpsPerDay) AS user_hits 
FROM 
(
    SELECT DATE(tracking.timestamp) AS DayTrackCount, 
           COUNT(DISTINCT tracking.ip) AS NumIpsPerDay 
    FROM user, tracking 
    WHERE tracking.user_id = $user_id 
    GROUP BY DATE(tracking.timestamp) 
) x

试试这个：

SELECT SUM(tab.NumIpsPerDay) AS user_hits
FROM
   (SELECT DATE(tracking.timestamp) AS DayTrackCount,
   COUNT(DISTINCT tracking.ip) AS NumIpsPerDay
   FROM USER, tracking
   WHERE tracking.user_id = $user_id
   GROUP BY DATE(tracking.timestamp)) as tab

为什么要输入子查询日期（tracking.timestamp）？

嗯，是的。您正在执行user和tracking之间的cross join（笛卡尔乘积）。但是，您没有使用user表中的任何字段。你可能根本不需要那张桌子。

试试这个：

SELECT SUM(NumIpsPerDay) AS user_hits
FROM (SELECT DATE(tracking.timestamp) AS DayTrackCount,
             COUNT(DISTINCT tracking.ip) AS NumIpsPerDay
      FROM tracking
      WHERE tracking.user_id = $user_id
      GROUP BY DATE(tracking.timestamp
     ) t
GROUP BY $user_id;

此外：

子查询没有别名
不需要limit
where子句出现在group by之后