如何通过查询在MySQL组中找到唯一值？

概述MySQL TABLE : numbers +----+-----+------------+ | ID | NUM | CREATED | +----+-----+------------+ | 1 | 11 | 2018-01-01 | +----+-----+------------+ | 2 | 22 | 2018-02-01 | +-

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 UniqueNumFROM  `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查询来获得我期望的结果？请帮忙.

最佳答案@H_404_56@您只需将表连接到自身并仅返回与先前创建日期不存在的数字.

SELECT  DATE_FORMAT(n.CREATED,COUNT(n.NUM) AS TotalNum,COUNT(un.NUM) AS UniqueNumFROM  `numbers` nleft 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现场工作 总结

以上是内存溢出为你收集整理的如何通过查询在MySQL组中找到唯一值？全部内容，希望文章能够帮你解决如何通过查询在MySQL组中找到唯一值？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。