sql私房菜

查询每个门派年龄最大的三个人(类似与Hive中的开窗函数)
SET @rk=0;
SET @j=0;
SELECT t3.deptId, CONCAT_WS(',',t1.name,t2.name,t3.name)FROM
(
SELECT a.deptId, a.name, a.age FROM
(SELECT *,IF(@j=deptId, @rk:=@rk+1, @rk:=1) rk, @j:=deptId
FROM t_emp ORDER BY deptId, age DESC) a
WHERE a.rk=1) t1
RIGHT JOIN
(
SELECT a.deptId, a.name, a.age FROM
(SELECT *,IF(@j=deptId, @rk:=@rk+1, @rk:=1) rk, @j:=deptId
FROM t_emp ORDER BY deptId, age DESC) a
WHERE a.rk=2) t2
ON t1.deptId = t2.deptId
RIGHT JOIN
(
SELECT a.deptId, a.name, a.age FROM
(SELECT *,IF(@j=deptId, @rk:=@rk+1, @rk:=1) rk, @j:=deptId
FROM t_emp ORDER BY deptId, age DESC) a
WHERE a.rk=3) t3
ON t1.deptId = t3.deptId