MYSQL COUNT值有日期范围select和没有日期范围select是不同的

Question

我有这个查询来获取范围日期中的计数值(使用一个唯一的日期过滤器到一天以查看选择细节):

SELECT `dates`.`date`, COUNT(*)
FROM (
SELECT CURDATE() - INTERVAL (units.mul + (10 * tens.mul) + (100 * hundreds.mul) + (200 * thousands.mul)) DAY AS `date`
FROM       (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS units
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS hundreds
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS thousands
) `dates`
LEFT JOIN `prices` ON (`prices`.`date` = `dates`.`date`)
WHERE `dates`.`date` = '2020-07-07'
GROUP BY `dates`.`date`
ORDER BY `dates`.`date` ASC;
+------------+-----------+
| date       | COUNT(*)  |
+------------+-----------+
| 2020-07-07 |    150840 |
+------------+-----------+
1 row in set (0.06 sec)

但是同样的查询只在价格表上，结果是:

SELECT COUNT(*) FROM `prices` WHERE `date` = '2020-07-07';
+----------+
| COUNT(*) |
+----------+
|    37710 |
+----------+
1 row in set (0.01 sec)

为什么第一个查询结果不是:

+------------+----------+
| date       | COUNT(*) |
+------------+----------+
| 2020-07-07 |    37710 |
+------------+----------+
1 row in set (0.06 sec)

谢谢!

Answer 1

37710 * 4 = 150840检查你的查询没有连接，你有4行与2020-07-07我怀疑错字200 *数千。Mul应该是1000 * thousand . Mul

SELECT `dates`.`date`, COUNT(*)
FROM (
SELECT CURDATE() - INTERVAL (units.mul + (10 * tens.mul) + (100 * hundreds.mul) + (200 * thousands.mul)) DAY AS `date`
FROM       (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS units
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS hundreds
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS thousands
) `dates`
#LEFT JOIN `prices` ON (`prices`.`date` = `dates`.`date`)
WHERE `dates`.`date` = '2020-07-07'
GROUP BY `dates`.`date`
ORDER BY `dates`.`date` ASC;
+------------+----------+
| date       | COUNT(*) |
+------------+----------+
| 2020-07-07 |        4 |
+------------+----------+
1 row in set (0.011 sec)

Answer 2

子查询返回4次2020-07-07然后左连接匹配4次与价格表。试着用不同的:

SELECT `dates`.`date`, COUNT(*)
FROM (
SELECT distinct CURDATE() - INTERVAL (units.mul + (10 * tens.mul) + (100 * hundreds.mul) + (200 * thousands.mul)) DAY AS `date`
FROM       (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS units
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS hundreds
CROSS JOIN (SELECT 0 AS mul UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS thousands
) `dates`
LEFT JOIN `prices` ON (`prices`.`date` = `dates`.`date`)
WHERE `dates`.`date` = '2020-07-07'
GROUP BY `dates`.`date`
ORDER BY `dates`.`date` ASC;