MySQL TABLE :
numbers
+----+-----+------------+
| ID | NUM | CREATED |
+----+-----+------------+
| 1 | 11 | 2018-01-01 |
+----+-----+------------+
| 2 | 22 | 2018-02-01 |
+----+-----+------------+
| 3 | 11 | 2018-03-01 |
+----+-----+------------+
| 4 | 44 | 2018-04-01 |
+----+-----+------------+
| 6 | 44 | 2018-04-02 |
+----+-----+------------+
| 5 | 22 | 2018-05-01 |
+----+-----+------------+
在此表中,ID是主键. NUM是随机值,CREATED是Date.
My Query:
SELECT
DATE_FORMAT(CREATED,'%Y-%m') AS Month,COUNT(NUM) AS TotalNum,COUNT(DISTINCT(NUM)) AS UniqueNum
FROM
`numbers`
GROUP BY
DATE_FORMAT(CREATED,'%Y-%m')
Result of My Query:
+---------+----------+-----------+
| Month | TotalNum | UniqueNum |
+---------+----------+-----------+
| 2018-01 | 1 | 1 |
+---------+----------+-----------+
| 2018-02 | 1 | 1 |
+---------+----------+-----------+
| 2018-03 | 1 | 1 |
+---------+----------+-----------+
| 2018-04 | 2 | 1 |
+---------+----------+-----------+
| 2018-05 | 1 | 1 |
+---------+----------+-----------+
But my Expected Result is :
+---------+----------+-----------+
| Month | TotalNum | UniqueNum |
+---------+----------+-----------+
| 2018-01 | 1 | 1 |
+---------+----------+-----------+
| 2018-02 | 1 | 1 |
+---------+----------+-----------+
| 2018-03 | 1 | 0 |
+---------+----------+-----------+
| 2018-04 | 2 | 1 |
+---------+----------+-----------+
| 2018-05 | 1 | 0 |
+---------+----------+-----------+
在2018-03月,UniqueNum的结果应为0.因为NUM 11已存在于2018-01月.也
在2018-05年,UniqueNum的结果应为0.因为NUM 22已存在于2018-02月.
我想找到每个月的唯一编号.如何通过更新我的MySQL查询来获得我期望的结果？请帮忙.
最佳答案
您只需将表连接到自身并仅返回与先前创建日期不存在的数字.
SELECT
DATE_FORMAT(n.CREATED,COUNT(n.NUM) AS TotalNum,COUNT(un.NUM) AS UniqueNum
FROM
`numbers` n
LEFT JOIN (SELECT ID,NUM FROM numbers n1 WHERE NOT EXISTS (SELECT 1 FROM numbers n2 WHERE n1.NUM = n2.NUM AND n2.CREATED < n1.CREATED)) un ON n.ID = un.ID
GROUP BY
DATE_FORMAT(n.CREATED,'%Y-%m')
>看到它在sqlfiddle现场工作