mysql - 计算来自一个 SQL 查询的不同数据

Question

我希望在 SQL 2 中获得与 SQL 1 中相同的结果，但不知何故，SQL 查询仅根据 WHERE 计算每个日期的数据总量。

SQL 1(每天显示适量的数据)

SELECT DATE(datetime_logged) AS date,
COUNT(data_status) AS status_a

FROM activity
WHERE id_user = '1'
AND DATE(datetime_logged) != CURDATE()
AND data_status = 'online'
GROUP BY DATE(datetime_logged)
ORDER BY DATE(datetime_logged) DESC
LIMIT 40

SQL 2(每天显示错误的数据量)

SELECT DATE(datetime_logged) AS date,
(SELECT COUNT(data_status) 
         FROM activity 
         WHERE id_user = '1' 
         AND DATE(datetime_logged) != CURDATE() 
         AND data_status = 'online') AS status_a

FROM activity
GROUP BY DATE(datetime_logged)
ORDER BY DATE(datetime_logged) DESC
LIMIT 40

表格

CREATE TABLE IF NOT EXISTS `activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_channel` varchar(50) NOT NULL,
  `id_game` int(11) NOT NULL,
  `data_muted_server` tinyint(4) NOT NULL,
  `data_muted_self` tinyint(4) NOT NULL,
  `data_deafen_server` tinyint(4) NOT NULL,
  `data_deafen_self` tinyint(4) NOT NULL,
  `data_suppressed` tinyint(4) NOT NULL,
  `data_status` varchar(10) NOT NULL,
  `data_game` text,
  `datetime_logged` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `index_datelog` (`datetime_logged`)
)

我怎样才能做到这一点？

Answer 1

如果我理解正确，我认为您只需将相关子查询与主查询联系起来即可:

AND a.datetime_logged=sub.datetime_logged

示例:

SELECT DATE(a.datetime_logged) AS date,
(SELECT COUNT(sub.data_status) 
         FROM activity sub
         WHERE sub.id_user = '1' 
         AND DATE(sub.datetime_logged) != CURDATE() 
         AND sub.data_status = 'online'
         AND a.datetime_logged=sub.datetime_logged) AS status_a

FROM activity a
GROUP BY DATE(a.datetime_logged)
ORDER BY DATE(a.datetime_logged) DESC
LIMIT 40