帶有日期范圍選擇的MYSQLCOUNT值與沒有日期范圍的不同

我有這個查詢來獲取范圍日期中的計數值（使用唯一的日期過濾器來查看選擇詳細資訊）：

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)

謝謝！

uj5u.com熱心網友回復：

37710 * 4 = 150840 在沒有連接的情況下檢查您的查詢，并且您有 4 行 2020-07-07 我懷疑錯字 200 *數千.mul 應該是 1000 *數千.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)

uj5u.com熱心網友回復：

子查詢回傳 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;

標籤：mysql 日期 数数 范围

上一篇：在谷歌表格中查找給定一周和一天的可用人員

下一篇：如何在laravel中將日期增加一個月？