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 脚本

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值