mysql rownumer实现

100

101

102

103

104

-- 窗口行数
select
e.* ,row_number() over(partition by deptno order by empno) as ROW_NUMBER from emp e;
-- SQL
SELECT E.*,
   IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER,
   @DEPTNO := DEPTNO AS VAR1
FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) C
ORDER BY DEPTNO;
-- 窗口函数
求每个人员占他所在部门总工资的百分比
SELECT E.*,
   TRUNC(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 3) AS SALPERCENT
FROM EMP E
ORDER BY DEPTNO;
 
SELECT E.*,
   SAL / (SELECT SUMOVER
            FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
                    FROM emp E1
                   GROUP BY DEPTNO) X
           WHERE X.DEPTNO = E.DEPTNO) AS SalPercent
FROM emp E
ORDER BY DEPTNO;
 
拿部门第二的工资的人
SELECT *
FROM (SELECT E.*,
           ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN
      FROM EMP E)
WHERE RN = 2;
 
SELECT *
FROM (SELECT E.*,
           IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS RN,
           @DEPTNO := DEPTNO
      FROM EMP E, (SELECT @RN := 0, @DEPTNO := 0) C
     ORDER BY DEPTNO, SAL DESC) X
WHERE X.RN = 2;
 
dense_rank()函数
SELECT empno,
ename,
sal,
deptno,
rank() OVER(PARTITION BY deptno ORDER BY sal desc) as rank,
dense_rank() OVER(PARTITION BY deptno ORDER BY sal desc) as dense_rank
FROM emp e;
 
select
empno,ename,sal,deptno, 
if(@deptno = deptno,if(@sal=sal,@rn:=@rn,@rn3:=@rn3+1),@rn:=1) as "RANK() OVER", 
if(@sal =sal,@rn2:=@rn2 ,if(@deptno = deptno,@rn2:=@rn2+1,@rn2:=1)) as "DENSE_RANK() OVER", 
if(@deptno = deptno,@rn:=@rn+1,@rn:=1) as "ROW_NUMBER() OVER"
, @deptno:=deptno,@sal:=sal 
from
(select empno,ename,sal,deptno from emp a ,(select @rn:=1,@deptno:=0,@rn2:=0,@rn3:=0,@sal:=0,@i:=0) b order by deptno,sal desc) c;
 
 
 
连续获得冠军的有哪些
create table  nba as
SELECT '公牛' AS TEAM, '1991' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1992' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1993' AS Y FROM DUAL UNION ALL
SELECT '活塞' AS TEAM, '1990' AS Y FROM DUAL UNION ALL
SELECT '火箭' AS TEAM, '1994' AS Y FROM DUAL UNION ALL
SELECT '火箭' AS TEAM, '1995' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1996' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1997' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1998' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '1999' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2000' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2001' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2002' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '2003' AS Y FROM DUAL UNION ALL
SELECT '活塞' AS TEAM, '2004' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '2005' AS Y FROM DUAL UNION ALL
SELECT '热火' AS TEAM, '2006' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '2007' AS Y FROM DUAL UNION ALL
SELECT '凯尔特人' AS TEAM, '2008' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2009' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2010' AS Y FROM DUAL;
--Oracle
SELECT TEAM, MIN(Y), MAX(Y)
FROM (SELECT E.*,
           ROWNUM,
           ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS RN,
           ROWNUM - ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS DIFF
      FROM NBA E
     ORDER BY Y)
GROUP BY TEAM, DIFF
HAVING MIN(Y) != MAX(Y)
ORDER BY 2;
-- MySQL
SELECT TEAM, MIN(Y), MAX(Y)
FROM (SELECT TEAM,
           Y,
           IF(@TEAM = TEAM, @RN := @RN + 1, @RN := 1) AS RWN,
           @RN1 := @RN1 + 1 AS RN,
           @TEAM := TEAM
      FROM nba N, (SELECT @RN := 0, @TEAM := '', @RN1 := '') C) A
GROUP BY RN - RWN
HAVING MIN(Y) != MAX(Y)