mysql - 在一个查询中获取所有 WHERE 案例

Question

我有这个查询

SELECT users.name 
FROM users 
LEFT JOIN weapon_stats ON users.id = weapon_stats.zp_id
WHERE weapon_stats.weapon = 26
ORDER BY weapon_stats.kills DESC
LIMIT 1;

问题是你需要得到从 1 到 30 变化的结果，这很奇怪..

是否可以在一次查询中获取所有结果？

我想让每个用户使用每种武器获得最多的杀戮

武器1

SELECT users.name 
FROM users 
LEFT JOIN weapon_stats ON users.id = weapon_stats.zp_id
WHERE weapon_stats.weapon = 01
ORDER BY weapon_stats.kills DESC
LIMIT 1;

武器2

SELECT users.name 
FROM users 
LEFT JOIN weapon_stats ON users.id = weapon_stats.zp_id
WHERE weapon_stats.weapon = 02
ORDER BY weapon_stats.kills DESC
LIMIT 1;

提前致谢!

Answer 1

select U.name, W.weapon, W.kills
FROM users U
INNER JOIN weapons W on W.zp_id = U.id
INNER JOIN 
  (SELECT WW.weapon, MAX(WW.kills) AS kills 
    FROM weapons WW 
    WHERE WW.weapon BETWEEN 1 AND 30
    GROUP BY WW.weapon) K
  ON W.kills = K.kills AND W.weapon = K.weapon

我确信它可以通过优化变得更快，但这最终有效

jsfiddle link