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

前端之家收集整理的这篇文章主要介绍了如何通过查询在MySQL组中找到唯一值?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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现场工作

猜你在找的MySQL相关文章