英文:
Data from two tables filtered by a column
问题
SELECT
t2.userId,
t1.name,
MAX(t2.level) as level,
MAX(t2.score) as score
FROM
myTab1 t1
INNER JOIN
myTab2 t2
ON
t1.userId = t2.userId
GROUP BY
t2.userId, t1.name
ORDER BY
level DESC
LIMIT 3
这是你需要的SQL查询，它将返回具有最高级别的前三个用户及其名字，按级别降序排列。
英文:
In MySQL, I have a myTab1 which has columns like userId, name, gender, birthMonth. Here userId is the primary key.
There can be only 1 unique userId in the entire table.
I have another table myTab2 which has columns like userId, level, score.
There is no primary key in this table and it is just for the transactions.
Example:
myTab1
| userId | name | gender | birthMonth |
|---|---|---|---|
| abc | name1 | Male | January |
| xyz | name2 | Female | March |
| mno | name3 | Male | July |
myTab2
| userId | level | score |
|---|---|---|
| abc | 1 | 10 |
| abc | 2 | 9 |
| abc | 3 | 11 |
| abc | 4 | 10 |
| abc | 5 | 23 |
| xyz | 1 | 11 |
| xyz | 2 | 10 |
| mno | 1 | 8 |
Now I need only the top 3 users with the highest level along with their name which is in myTab1 as below
| userId | name | level | score |
|---|---|---|---|
| abc | name1 | 5 | 23 |
| xyz | name2 | 2 | 10 |
| mno | name3 | 1 | 8 |
Following is what I wrote but not sure how to get the result like above. I am not good at DB queries and looking for some help.
SELECT
b.*,
a.name
FROM
myTab1 AS b
INNER JOIN myTab2 as a ON b.userId=a.userId
ORDER BY level DESC
limit 3
答案1
得分: 0
你可以尝试这样做...
SELECT
t1.userId,
t1.name,
MAX(t2.level) AS level,
MAX(t2.score) AS score
FROM
myTab1 t1
JOIN
myTab2 t2 ON t1.userId = t2.userId
GROUP BY
t1.userId, t1.name
ORDER BY
level DESC
LIMIT 3;
输出：
英文:
You can try this...
SELECT
t1.userId,
t1.name,
MAX(t2.level) AS level,
MAX(t2.score) AS score
FROM
myTab1 t1
JOIN
myTab2 t2 ON t1.userId = t2.userId
GROUP BY
t1.userId, t1.name
ORDER BY
level DESC
LIMIT 3;
Output
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论