英文:
Get distinct values from groups in MySQL
问题
我想获取每支球队（team字段）最低分的ID。我的查询可以工作，但我需要确保以下查询在大型表格中足够好。
我需要简化和优化。
查询：
SELECT T.id FROM teams AS T
INNER JOIN (
SELECT MIN(T1.points) AS P FROM teams AS T1
GROUP BY T1.team LIMIT 5
) TJOIN ON T.points IN (TJOIN.P)
GROUP BY T.team
ORDER BY T.points ASC LIMIT 5
表格 teams
| id | team (外键) | points (索引) |
|---|---|---|
| 1 | a | 100 |
| 2 | a | 101 |
| 3 | b | 106 |
| 4 | c | 105 |
| 5 | c | 102 |
结果
| id |
|---|
| 1 |
| 5 |
| 3 |
英文:
I want to get the id of the lowest points from each team (the team field).
My query works but i need to make sure the following query is good enough with a large table.
I need Simplification and Optimization.
Query:
SELECT T.id from teams as T
INNER JOIN (
SELECT MIN(T1.points) AS P FROM teams AS T1
GROUP BY T1.team LIMIT 5
) TJOIN ON T.points IN (TJOIN.P)
GROUP BY T.team
ORDER BY T.points ASC LIMIT 5
Table teams
| id | team (foreign_key) | points (indexed) |
|---|---|---|
| 1 | a | 100 |
| 2 | a | 101 |
| 3 | b | 106 |
| 4 | c | 105 |
| 5 | c | 102 |
Result
| id |
|---|
| 1 |
| 5 |
| 3 |
答案1
得分: 1
以下是翻译好的部分：
I believe the query you are looking for is:
SELECT MIN(T.id)
FROM teams as T
INNER JOIN (
SELECT team, MIN(points) AS min_points
FROM teams
GROUP BY team LIMIT 5
) TJOIN
ON T.team = TJOIN.team
AND T.points = TJOIN.min_points
GROUP BY T.team
ORDER BY T.points ASC
LIMIT 5
You need to join based on both the column being grouped by and the min value. Consider the result of your query if multiple teams had a score of 100.
Another way of doing this is to use ROW_NUMBER():
SELECT id
FROM (
SELECT id, points, ROW_NUMBER() OVER (PARTITION BY team ORDER BY points ASC, id ASC) rn
FROM teams
) t
WHERE rn = 1
ORDER BY points ASC
LIMIT 5
英文:
I believe the query you are looking for is:
SELECT MIN(T.id)
FROM teams as T
INNER JOIN (
SELECT team, MIN(points) AS min_points
FROM teams
GROUP BY team LIMIT 5
) TJOIN
ON T.team = TJOIN.team
AND T.points = TJOIN.min_points
GROUP BY T.team
ORDER BY T.points ASC
LIMIT 5
You need to join based on both the column being grouped by and the min value. Consider the result of your query if multiple teams had a score of 100.
Another way of doing this is to use ROW_NUMBER():
SELECT id
FROM (
SELECT id, points, ROW_NUMBER() OVER (PARTITION BY team ORDER BY points ASC, id ASC) rn
FROM teams
) t
WHERE rn = 1
ORDER BY points ASC
LIMIT 5
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论