php - 如何在sql中获取多个重复值计数

Question

我有一个数据库，其中存储了用户设备 ID 和设备注册日期 (cr_date)，我想获取当天已注册设备总数的列表(按日期排列)

下面显示表结构

device_ID   | cr_date 
--------------------
    1       | 2016-06-02 18:02:13   
    2       | 2016-06-02 18:03:58
    3       | 2016-06-02 18:04:11
    4       | 2016-06-03 18:04:33
    5       | 2016-06-03 18:04:33
    6       | 2016-06-04 18:04:44 

我想要以下结果

count   |  date 
---------------------------------
    3   |   2016-06-02
    2   |   2016-06-03
    1   |   2016-06-04 

我使用了下面的查询，但没有成功

SELECT device_type, count(*) AS duplicate_count
FROM (
 SELECT device_type FROM tbl_app_deviceregister
 GROUP BY device_type HAVING COUNT(device_type) > 0
) AS t

Answer 1

你想要这个吗；)

SELECT count(1) as `count`, date_format(cr_date, '%Y-%m-%d') as `date`
FROM tbl_app_deviceregister
GROUP BY `date`
ORDER BY `count` DESC