mysql - 需要选择 2019 年的不同日期并进行计数

Question

一旦我给出日期范围，这个查询就不起作用。当它像这样的时候它工作得很好

SELECT DISTINCT (receive_date) ,(SELECT COUNT(receive_date) from stocks WHERE receive_date = Count.receive_date) as `count` from stocks as Count ORDER BY receive_date DESC

但我只需要为 2019 年选择不同的日期

SELECT DISTINCT (receive_date) 
from stocks 
WHERE receive_date BETWEEN '2019-01-01' AND '2019-12-31',
(SELECT COUNT(receive_date) from stocks WHERE receive_date = Count.receive_date) as `count` from stocks as Count 
ORDER BY receive_date DESC

错误

静态分析:

分析过程中发现 4 个错误。

Unrecognized keyword. (near "as" at position 144) Unexpected token. (near "count" at position 147) This type of clause was previously parsed. (near "from" at position 155) Unrecognized statement type. (near "from" at position 155)

SQL 查询:文档

SELECT DISTINCT (receive_date) from stocks WHERE receive_date BETWEEN '2019-01-01' AND '2019-12-31',(SELECT COUNT(receive_date) from stocks WHERE receive_date = Count.receive_date) as `count` from stocks as Count ORDER BY receive_date DESC

MySQL 说:文档

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT COUNT(receive_date) from stocks WHERE rdate = Count.receive_date) as count from stocks' at line 1

Answer 1

如果您想获取不同的 receive_date 及其计数，只需使用 GROUP BY 子句即可。

SELECT 
    receive_date, 
    COUNT(0) AS cnt 
FROM stocks 
WHERE receive_date BETWEEN '2019-01-01' AND '2019-12-31'
GROUP BY receive_date
ORDER BY receive_date DESC;