限制每个唯一列组合的查询结果

huangapple go评论88阅读模式
英文:

Limit query result per unique columns combination

问题

我想要修改此查询,以限制结果每个组最多有2行(最新):

  1. select
  2. distinct clusterName,
  3. aksNamespace,
  4. acrName,
  5. acrImageName,
  6. acrImageVersion,
  7. date
  8. from
  9. (
  10. select
  11. clusterName,
  12. aksNamespace,
  13. acrName,
  14. acrImageName,
  15. acrImageVersion,
  16. date
  17. from
  18. aks_images
  19. order by
  20. acrImageName,
  21. date desc
  22. ) as t
  23. where
  24. acrName = "storage"
  25. order by
  26. clusterName,
  27. acrImageName,
  28. date desc

当前结果

clusterName aksNamespace acrName acrImageName acrImageVersion date
dev support storage app f74581b 17.02.2023 14:35
dev support storage app c6040a0 17.02.2023 7:45
dev support storage app 4410f39 16.02.2023 10:43
dev abc storage qwer 93241f1 15.02.2023 12:45
dev abc storage qwer 249b089 14.02.2023 13:15
dev abc storage qwer 1c40785 13.02.2023 13:30
prod support storage app 469a492 07.02.2023 14:15
test support storage app 07e22a6 17.02.2023 14:40
test support storage app daf975d 17.02.2023 13:40
test support storage app 7e1a50b 15.02.2023 13:10
test support storage app 8f27715 15.02.2023 9:35

期望结果

clusterName aksNamespace acrName acrImageName acrImageVersion date
dev support storage app f74581b 17.02.2023 14:35
dev support storage app c6040a0 17.02.2023 7:45
dev abc storage qwer 93241f1 15.02.2023 12:45
dev abc storage qwer 249b089 14.02.2023 13:15
prod support storage app 469a492 07.02.2023 14:15
test support storage app 07e22a6 17.02.2023 14:40
test support storage app daf975d 17.02.2023 13:40

Mysql 版本: 8.0.31

我将感激任何建议或解决方案。

英文:

I would like to modify this query to limit result to have max 2 rows (latest) per group:

  1. select
  2. distinct clusterName,
  3. aksNamespace,
  4. acrName,
  5. acrImageName,
  6. acrImageVersion,
  7. date
  8. from
  9. (
  10. select
  11. clusterName,
  12. aksNamespace,
  13. acrName,
  14. acrImageName,
  15. acrImageVersion,
  16. date
  17. from
  18. aks_images
  19. order by
  20. acrImageName,
  21. date desc
  22. ) as t
  23. where
  24. acrName = "storage"
  25. order by
  26. clusterName,
  27. acrImageName,
  28. date desc

Current result:

clusterName aksNamespace acrName acrImageName acrImageVersion `date`
dev support storage app f74581b 17.02.2023 14:35
dev support storage app c6040a0 17.02.2023 7:45
dev support storage app 4410f39 16.02.2023 10:43
dev abc storage qwer 93241f1 15.02.2023 12:45
dev abc storage qwer 249b089 14.02.2023 13:15
dev abc storage qwer 1c40785 13.02.2023 13:30
prod support storage app 469a492 07.02.2023 14:15
test support storage app 07e22a6 17.02.2023 14:40
test support storage app daf975d 17.02.2023 13:40
test support storage app 7e1a50b 15.02.2023 13:10
test support storage app 8f27715 15.02.2023 9:35

Expected result:

clusterName aksNamespace acrName acrImageName acrImageVersion `date`
dev support storage app f74581b 17.02.2023 14:35
dev support storage app c6040a0 17.02.2023 7:45
dev abc storage qwer 93241f1 15.02.2023 12:45
dev abc storage qwer 249b089 14.02.2023 13:15
prod support storage app 469a492 07.02.2023 14:15
test support storage app 07e22a6 17.02.2023 14:40
test support storage app daf975d 17.02.2023 13:40

Mysql version: 8.0.31

I'd be grateful for any advice or solutions.

答案1

得分: 1

在MySQL 8+中,我们可以使用 ROW_NUMBER()

  1. WITH cte AS (
  2. SELECT *, ROW_NUMBER() OVER (PARTITION BY clusterName, acrImageName
  3. ORDER BY date DESC) rn
  4. FROM aks_images
  5. WHERE acrName = 'storage'
  6. )
  7. SELECT
  8. clusterName,
  9. aksNamespace,
  10. acrName,
  11. acrImageName,
  12. acrImageVersion,
  13. date
  14. FROM cte
  15. WHERE rn <= 2
  16. ORDER BY
  17. clusterName,
  18. acrImageName,
  19. date DESC;
英文:

On MySQL 8+, we can use ROW_NUMBER():

<!-- language: sql -->

  1. WITH cte AS (
  2. SELECT *, ROW_NUMBER() OVER (PARTITION BY clusterName, acrImageName
  3. ORDER BY date DESC) rn
  4. FROM aks_images
  5. WHERE acrName = &#39;storage&#39;
  6. )
  7. SELECT
  8. clusterName,
  9. aksNamespace,
  10. acrName,
  11. acrImageName,
  12. acrImageVersion,
  13. date
  14. FROM cte
  15. WHERE rn &lt;= 2
  16. ORDER BY
  17. clusterName,
  18. acrImageName,
  19. date DESC;

huangapple
  • 本文由 发表于 2023年2月18日 00:54:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/75487130.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定