代码之家 › 专栏 › 技术社区 › Sumon Sarker

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

mysql

Sumon Sarker · 技术社区 · 7 年前

MySQL表: 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 是日期 .

我的问题:

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')

我的查询结果:

+---------+----------+-----------+
| Month   | TotalNum | UniqueNum |
+---------+----------+-----------+
| 2018-01 |    1     |     1     |
+---------+----------+-----------+
| 2018-02 |    1     |     1     |
+---------+----------+-----------+
| 2018-03 |    1     |     1     |
+---------+----------+-----------+
| 2018-04 |    2     |     1     |
+---------+----------+-----------+
| 2018-05 |    1     |     1     |
+---------+----------+-----------+

但我的预期结果是:

+---------+----------+-----------+
| Month   | TotalNum | UniqueNum |
+---------+----------+-----------+
| 2018-01 |    1     |     1     |
+---------+----------+-----------+
| 2018-02 |    1     |     1     |
+---------+----------+-----------+
| 2018-03 |    1     |     0     |
+---------+----------+-----------+
| 2018-04 |    2     |     1     |
+---------+----------+-----------+
| 2018-05 |    1     |     0     |
+---------+----------+-----------+

的结果 UniqueNum 应该是 0 月份 2018-03 . 因为 11已存在于月份中 .

的结果应该是 0 月份 . 因为 22个月内已存在 2018-02

我想找出每个月的唯一号码。如何通过更新我的 ? 请帮忙。

2 回复 | 直到 7 年前

fancyPants 7 年前

您只需将表连接到它本身,然后只返回与以前创建的日期不存在的数字。

SELECT
  DATE_FORMAT(n.CREATED,'%Y-%m') AS Month,
  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

Guy Louzon 7 年前

通常对我有效的解决方案是嵌套查询希望这有帮助

SELECT
    Month,
    SUM(NUMCNT) AS TotalNum,
    COUNT(NUM) AS UniqueNum
FROM
    (
SELECT
    DATE_FORMAT(CREATED,'%Y-%m') AS Month,
    NUM,
    COUNT(NUM) AS NUMcnt
FROM
    `numbers`
GROUP BY
    Month,
    NUM
) a
;

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前