从两个表中通过一列筛选的数据

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

Data from two tables filtered by a column

问题

  1. SELECT
  2. t2.userId,
  3. t1.name,
  4. MAX(t2.level) as level,
  5. MAX(t2.score) as score
  6. FROM
  7. myTab1 t1
  8. INNER JOIN
  9. myTab2 t2
  10. ON
  11. t1.userId = t2.userId
  12. GROUP BY
  13. t2.userId, t1.name
  14. ORDER BY
  15. level DESC
  16. 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.

  1. SELECT
  2. b.*,
  3. a.name
  4. FROM
  5. myTab1 AS b
  6. INNER JOIN myTab2 as a ON b.userId=a.userId
  7. ORDER BY level DESC
  8. limit 3

答案1

得分: 0

你可以尝试这样做...

  1. SELECT
  2. t1.userId,
  3. t1.name,
  4. MAX(t2.level) AS level,
  5. MAX(t2.score) AS score
  6. FROM
  7. myTab1 t1
  8. JOIN
  9. myTab2 t2 ON t1.userId = t2.userId
  10. GROUP BY
  11. t1.userId, t1.name
  12. ORDER BY
  13. level DESC
  14. LIMIT 3;

输出:

从两个表中通过一列筛选的数据

英文:

You can try this...

  1. SELECT
  2. t1.userId,
  3. t1.name,
  4. MAX(t2.level) AS level,
  5. MAX(t2.score) AS score
  6. FROM
  7. myTab1 t1
  8. JOIN
  9. myTab2 t2 ON t1.userId = t2.userId
  10. GROUP BY
  11. t1.userId, t1.name
  12. ORDER BY
  13. level DESC
  14. LIMIT 3;

Output

从两个表中通过一列筛选的数据

huangapple
  • 本文由 发表于 2023年8月10日 17:05:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/76874208.html
匿名

发表评论

匿名网友

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

确定