分组选择数据

 

               举例：      

                      

SELECT  a.username,b.timestr,b.kills
FROM user1 a JOIN userkills b
ON a.id = b.userid
WHERE username='孙悟空'
ORDER BY b.kills DESC
LIMIT 2

 

               查询结果：每个人打怪最多的两天 ，倒序（DESC）

               我们可以对每个人分别执行上面的查询

             

               试想，如果分类很多呢，上面的查询则不符合要求，则

 

                 优化1：

                       

WITH tmp AS(
SELECT a.username ,b.timestr,b,kills,
ROW_NUMBER()  over
    (partition by a.username
     order by b.kills) cnt
     FROM user1 a JOIN userkills b
     ON a.id = b.userid
     )
SELECT * FROM tmp WHERE cnt <=2
)

 

                       MySQL中不支持ROW_NUMBER() 语句

 

 

                  优化2：

                          

SELECT d.username,c.timestr,kills
FROM(
   SELECT userid,timestr,kills,
   (SELECT COUNT(*) FROM userkills b 
   WHERE b.userid = a.userid AND a.kills <= b.kills) AS cnt
   FROM userkills a
   GROUP BY userid,timestr,kills
   ) c  JOIN user1 d ON c.userid = d.id
WHERE cnt <= 2
)