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

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

MySQL TABLE : numbers

  1. +----+-----+------------+
  2. | ID | NUM | CREATED |
  3. +----+-----+------------+
  4. | 1 | 11 | 2018-01-01 |
  5. +----+-----+------------+
  6. | 2 | 22 | 2018-02-01 |
  7. +----+-----+------------+
  8. | 3 | 11 | 2018-03-01 |
  9. +----+-----+------------+
  10. | 4 | 44 | 2018-04-01 |
  11. +----+-----+------------+
  12. | 6 | 44 | 2018-04-02 |
  13. +----+-----+------------+
  14. | 5 | 22 | 2018-05-01 |
  15. +----+-----+------------+
  16. @H_502_13@
  17. 在此表中,ID是主键. NUM随机值,CREATEDDate.

  18. My Query:

  19.  
  20. SELECT
  21.   DATE_FORMAT(CREATED,'%Y-%m') AS Month,COUNT(NUM) AS TotalNum,COUNT(DISTINCT(NUM)) AS UniqueNum
  22. FROM
  23.   `numbers`
  24. GROUP BY
  25.   DATE_FORMAT(CREATED,'%Y-%m')
  26. @H_502_13@
  27. Result of My Query:

  28. +---------+----------+-----------+
  29. | Month   | TotalNum | UniqueNum |
  30. +---------+----------+-----------+
  31. | 2018-01 |    1     |     1     |
  32. +---------+----------+-----------+
  33. | 2018-02 |    1     |     1     |
  34. +---------+----------+-----------+
  35. | 2018-03 |    1     |     1     |
  36. +---------+----------+-----------+
  37. | 2018-04 |    2     |     1     |
  38. +---------+----------+-----------+
  39. | 2018-05 |    1     |     1     |
  40. +---------+----------+-----------+
  41. @H_502_13@
  42. But my Expected Result is :

  43. +---------+----------+-----------+
  44. | Month   | TotalNum | UniqueNum |
  45. +---------+----------+-----------+
  46. | 2018-01 |    1     |     1     |
  47. +---------+----------+-----------+
  48. | 2018-02 |    1     |     1     |
  49. +---------+----------+-----------+
  50. | 2018-03 |    1     |     0     |
  51. +---------+----------+-----------+
  52. | 2018-04 |    2     |     1     |
  53. +---------+----------+-----------+
  54. | 2018-05 |    1     |     0     |
  55. +---------+----------+-----------+
  56. @H_502_13@
  57. 2018-03月,UniqueNum的结果应为0.因为NUM 11已存在于2018-01月.也
    2018-05年,UniqueNum的结果应为0.因为NUM 22已存在于2018-02月.

  58. 我想找到每个月的唯一编号.如何通过更新我的MySQL查询来获得我期望的结果?请帮忙.

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

  1. SELECT
  2. DATE_FORMAT(n.CREATED,COUNT(n.NUM) AS TotalNum,COUNT(un.NUM) AS UniqueNum
  3. FROM
  4. `numbers` n
  5. 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
  6. GROUP BY
  7. DATE_FORMAT(n.CREATED,'%Y-%m')
  8. @H_502_13@
  9. >看到它在sqlfiddle现场工作

猜你在找的MySQL相关文章