sql 取最大值对应的记录
【问题】
I have a database of baseball plays with a PlayerID and a TypeID (the kind of play: double, strike out, etc). The data looks something like this:
+----------+--------+
| playerid | typeid |
+----------+--------+
| 2 | 4 |
| 2 | 4 |
| 2 | 7 |
| 3 | 7 |
| 3 | 7 |
| 3 | 7 |
| 3 | 26 |
| 3 | 7 |
I'm trying to find which players had the most of each kind of play. E.g. Jim (PlayerID 3) had the most strike outs (TypeID 7) and Bob (PlayerID 2) had the most home runs (TypeID 4) which should result in the following table:
+----------+--------+----------------+
| playerid | typeid | max(playcount) |
+----------+--------+----------------+
| 2 | 4 | 12 |
| 3 | 7 | 9 |
| 3 | 26 | 1 |
My best attempt so far is to run:
SELECT playerid,typeid,MAX(playcount) FROM
(
SELECT playerid,typeid,COUNT(*) playcount FROM plays GROUP BY playerid,typeid
) AS t GROUP BY typeid;
Which returns the proper maximums of each type, but the associated PlayerIDs are all wrong and I can't figure out why. I'm sure I'm missing something simple (or making this overly complicated) but can't figure it out. Any ideas?
有人给出正确答案：
SELECT a.*
FROM (
SELECT playerid
,typeid
,COUNT(*) playcount
FROM plays
GROUP BY playerid,typeid
) a
LEFT JOIN
(
SELECT playerid
,typeid
,COUNT(*) playcount
FROM @lays
GROUP BY playerid,typeid
) b
ON a.typeid = b.typeid
AND a.playcount < b.playcount
WHERE b.playerid IS NULL
【回答】
SQL不能直接取出最大值对应的记录，要用子查询再比一下，读出来在外部算要容易得多，SPL写出来只要一句：
A |
|
1 |
$SELECT playerid,typeid,COUNT(*) playcount FROM plays GROUP BY playerid,typeid |
2 |
=A1.group(typeid).(~.maxp(playcount)) |
A1：sql取数，按照playerid,typeid分组统计计数
A2： 对A1按照typeid分组，取出每一种比赛中表现最棒的队员，也就是每组中playcount最大的那条记录
这个脚本可以方便的集成到应用程序中，具体可以参考Java 如何调用 SPL 脚本
