【问题】
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 不能直接取出最大值对应的记录，要用子查询再比一下，这样一来就比较麻烦，使用集算器只要简单两句就能搞定：

这个代码也可以方便的集成到应用程序中，用起来很简单，可参考Java 如何调用 SPL 脚本。