mysql实现oracle分析函数功能 over

部分内容来源:http://blog.csdn.net/jgmydsai/article/category/3139929

Mysql的if函数

格式:IF(Condition,A,B)

意义:当ConditionTRUE时,返回A;当ConditionFALSE时,返回B

作用:作为条件语句使用。

例子:

SELECT
    fullName,
    actualHour,
    planhour,
    annotatorId,
   IF(actualHour-planhour>0.01,1,IF(actualHour-planhour<-0.01,-1,0)) ASratio
FROM

   Table01

 

mysql查询结果添加序列号

第一种方法:

select   (@i:=@i+1)  as   i,table_name.*   from   table_name,(select   @i:=0)   as  it 

第二种方法:

 

set @rownum=0;
select @rownum:=@rownum+1 as rownum, t.username from auth_user t limit 1,5;

 

mysql之 sum() over(partition by)

[sql] view plaincopy

1.  SELECT empno,  

2.         ename,  

3.         sal,  

4.         IF(@deptno = deptno, @sal := @sal, @sal := s_sal) AS s_sal,  

5.         @deptno := deptno  

6.  FROM  

7.  (  

8.  SELECT empno,  

9.         ename,  

10.        sal,  

11.        IF(@deptno = deptno, @sal := @sal + sal, @sal := sal) AS s_sal,  

12.        @deptno := deptno AS deptno  

13.   FROM (SELECT @sal := 0, @deptno := 0) a0, emp t  

14.  ORDER BY deptno, empno  

15. )e  

16.  ORDER BY deptno, empno DESC;  

 

1.  +-------+--------+------+-------+-------------------+  

2.  | empno | ename  | sal  | s_sal | @deptno := deptno |  

3.  +-------+--------+------+-------+-------------------+  

4.  |  7934 | MILLER | 1300 |  8750 |                10 |  

5.  |  7839 | KING   | 5000 |  8750 |                10 |  

6.  |  7782 | CLARK  | 2450 |  8750 |                10 |  

7.  |  7902 | FORD   | 3000 | 10875 |                20 |  

8.  |  7876 | ADAMS  | 1100 | 10875 |                20 |  

9.  |  7788 | SCOTT  | 3000 | 10875 |                20 |  

10. |  7566 | JONES  | 2975 | 10875 |                20 |  

11. |  7369 | SMITH  |  800 | 10875 |                20 |  

12. |  7900 | JAMES  |  950 |  9400 |                30 |  

13. |  7844 | TURNER | 1500 |  9400 |                30 |  

14. |  7698 | BLAKE  | 2850 |  9400 |                30 |  

15. |  7654 | MARTIN | 1250 |  9400 |                30 |  

16. |  7521 | WARD   | 1250 |  9400 |                30 |  

17. |  7499 | ALLEN  | 1600 |  9400 |                30 |  

18. +-------+--------+------+-------+-------------------+  

19. 14 rows in set (0.01 sec)  

max() over()

今天有人问max() over()的需求在mysql中怎么写。

同以前讲过的几个例子一样,虽然mysql中没有分析函数,可max() over()的方法一样很容易实现

 

max() over()

[sql] view plaincopy

1.  mysql> set @max_sal=0;  

2.  Query OK, 0 rows affected (0.00 sec)  

3.    

4.  mysql> select if(@max_sal=0,@max_sal:=sal,@max_sal) as max_sal,sal from emp order by sal desc;  

5.  +---------+------+  

6.  | max_sal | sal  |  

7.  +---------+------+  

8.  |    5000 | 5000 |  

9.  |    5000 | 3000 |  

10. |    5000 | 3000 |  

11. |    5000 | 2975 |  

12. |    5000 | 2850 |  

13. |    5000 | 2450 |  

14. |    5000 | 1600 |  

15. |    5000 | 1500 |  

16. |    5000 | 1300 |  

17. |    5000 | 1250 |  

18. |    5000 | 1250 |  

19. |    5000 | 1100 |  

20. |    5000 |  950 |  

21. |    5000 |  800 |  

22. +---------+------+  

23. 14 rows in set (0.00 sec)  

特殊的行转列需求

[sql] view plaincopy

1.  mysql> select * from ddd;  

2.  +------+------+------+------+  

3.  | stat | yi   | er   | san  |  

4.  +------+------+------+------+  

5.  | a    |    1 |    2 |    3 |  

6.  | a    |    4 |    5 |    6 |  

7.  | b    |   11 |   22 |    3 |  

8.  | b    |    5 |   61 |   82 |  

9.  +------+------+------+------+  

10. rows in set (0.00 sec)  


把上面的数据行转列,结果如下

[sql] view plaincopy

1.  +------+------+------+------+------+------+------+------+------+------+  

2.  | stat | c1   | c2   | c3   | c4   | c5   | c6   | c7   | c8   | c9   |  

3.  +------+------+------+------+------+------+------+------+------+------+  

4.  | a    |    1 |    2 |    3 |    4 |    5 |    6 | NULL | NULL | NULL |  

5.  | b    |    3 |    5 |   11 |   22 |   61 |   82 | NULL | NULL | NULL |  

6.  +------+------+------+------+------+------+------+------+------+------+  

7.  rows in set (0.01 sec)  


这种需求在oracle中比较容易实现,因为oracle中有row_number可以生成序号。mysql中就只有另找方法了

[sql] view plaincopy

1.  set @rn=0;  

2.  set @stat='';  

3.  select stat,  

4.  max(case when rn = 1 then yi endas c1,  

5.  max(case when rn = 2 then yi endas c2,  

6.  max(case when rn = 3 then yi endas c3,  

7.  max(case when rn = 4 then yi endas c4,  

8.  max(case when rn = 5 then yi endas c5,  

9.  max(case when rn = 6 then yi endas c6,  

10. max(case when rn = 7 then yi endas c7,  

11. max(case when rn = 8 then yi endas c8,  

12. max(case when rn = 9 then yi endas c9  

13. from  

14. (  

15. SELECT stat,  

16.        yi,  

17.        IF(@stat = stat, @rn := @rn + 1, @rn := 1) AS rn,  

18.        @stat := stat AS last_stat  

19. from  

20. (  

21. select stat,yi from ddd  

22. union all  

23. select stat,er from ddd  

24. union all  

25. select stat,san from ddd  

26. as a  

27. order by stat,yi  

28. as a  

29. group by stat;  


通过分组生成的序号来定位,就可以把对应的值转到相应位置了

[sql] view plaincopy

1.  mysql> set @rn=0;  

2.  Query OK, 0 rows affected (0.00 sec)  

3.    

4.  mysql> set @stat='';  

5.  Query OK, 0 rows affected (0.00 sec)  

6.    

7.  mysql> select stat,  

8.      -> max(case when rn = 1 then yi endas c1,  

9.      -> max(case when rn = 2 then yi endas c2,  

10.     -> max(case when rn = 3 then yi endas c3,  

11.     -> max(case when rn = 4 then yi endas c4,  

12.     -> max(case when rn = 5 then yi endas c5,  

13.     -> max(case when rn = 6 then yi endas c6,  

14.     -> max(case when rn = 7 then yi endas c7,  

15.     -> max(case when rn = 8 then yi endas c8,  

16.     -> max(case when rn = 9 then yi endas c9  

17.     -> from  

18.     -> (  

19.     -> SELECT stat,  

20.     ->        yi,  

21.     ->        IF(@stat = stat, @rn := @rn + 1, @rn := 1) AS rn,  

22.     ->        @stat := stat AS last_stat  

23.     -> from  

24.     -> (  

25.     -> select stat,yi from ddd  

26.     -> union all  

27.     -> select stat,er from ddd  

28.     -> union all  

29.     -> select stat,san from ddd  

30.     -> ) as a  

31.     -> order by stat,yi  

32.     -> ) as a  

33.     -> group by stat;  

34. +------+------+------+------+------+------+------+------+------+------+  

35. | stat | c1   | c2   | c3   | c4   | c5   | c6   | c7   | c8   | c9   |  

36. +------+------+------+------+------+------+------+------+------+------+  

37. | a    |    1 |    2 |    3 |    4 |    5 |    6 | NULL | NULL | NULL |  

38. | b    |    3 |    5 |   11 |   22 |   61 |   82 | NULL | NULL | NULL |  

39. +------+------+------+------+------+------+------+------+------+------+  

40. rows in set (0.01 sec)  

mysqlwm_concat

[sql] view plaincopy

1.  SET @enames='';     

2.  SET @last_deptno=-1;    

3.  SELECT deptno, MAX(enames) AS enames  

4.    FROM (SELECT deptno,  

5.                 IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,  

6.                 IF(@last_deptno = deptno,  

7.                    @enames := concat(@enames, ',', ename),  

8.                    @enames := ename) AS enames,  

9.                 @last_deptno := deptno AS last_deptno  

10.           FROM emp  

11.          ORDER BY deptno, empno)a  

12.  GROUP BY deptno;  

13.   

14. +--------+--------------------------------------+  

15. | deptno | enames                               |  

16. +--------+--------------------------------------+  

17. |     10 | CLARK,KING,MILLER                    |  

18. |     20 | SMITH,JONES,SCOTT,ADAMS,FORD         |  

19. |     30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |  

20. +--------+--------------------------------------+  

21. rows in set (0.01 sec)  

mysql之累加

 

[sql] view plaincopy

1.  SET @add_sal=0;    

2.  SELECT deptno, empno, ename, sal, @add_sal := @add_sal + sal AS add_sal  

3.    FROM emp  

4.   ORDER BY empno;  

5.    

6.  +--------+-------+--------+------+---------+  

7.  | deptno | empno | ename  | sal  | add_sal |  

8.  +--------+-------+--------+------+---------+  

9.  |     20 |  7369 | SMITH  |  800 |     800 |  

10. |     30 |  7499 | ALLEN  | 1600 |    2400 |  

11. |     30 |  7521 | WARD   | 1250 |    3650 |  

12. |     20 |  7566 | JONES  | 2975 |    6625 |  

13. |     30 |  7654 | MARTIN | 1250 |    7875 |  

14. |     30 |  7698 | BLAKE  | 2850 |   10725 |  

15. |     10 |  7782 | CLARK  | 2450 |   13175 |  

16. |     20 |  7788 | SCOTT  | 3000 |   16175 |  

17. |     10 |  7839 | KING   | 5000 |   21175 |  

18. |     30 |  7844 | TURNER | 1500 |   22675 |  

19. |     20 |  7876 | ADAMS  | 1100 |   23775 |  

20. |     30 |  7900 | JAMES  |  950 |   24725 |  

21. |     20 |  7902 | FORD   | 3000 |   27725 |  

22. |     10 |  7934 | MILLER | 1300 |   29025 |  

23. +--------+-------+--------+------+---------+  

24. 14 rows in set (0.01 sec)  

 

[sql] view plaincopy

1.  SET @add_sal=0;       

2.  SET @last_deptno=-1;      

3.  SELECT deptno,    

4.         empno,    

5.         ename,    

6.         sal,    

7.         IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,    

8.         IF(@last_deptno = deptno, @add_sal := @add_sal + sal, @add_sal := sal) AS add_sal,    

9.         @last_deptno := deptno AS last_deptno    

10.   FROM emp    

11.  ORDER BY deptno, sal;  

12.   

13. +--------+-------+--------+------+------+---------+-------------+  

14. | deptno | empno | ename  | sal  | rn   | add_sal | last_deptno |  

15. +--------+-------+--------+------+------+---------+-------------+  

16. |     10 |  7934 | MILLER | 1300 |    1 |    1300 |          10 |  

17. |     10 |  7782 | CLARK  | 2450 |    2 |    3750 |          10 |  

18. |     10 |  7839 | KING   | 5000 |    3 |    8750 |          10 |  

19. |     20 |  7369 | SMITH  |  800 |    1 |     800 |          20 |  

20. |     20 |  7876 | ADAMS  | 1100 |    2 |    1900 |          20 |  

21. |     20 |  7566 | JONES  | 2975 |    3 |    4875 |          20 |  

22. |     20 |  7788 | SCOTT  | 3000 |    4 |    7875 |          20 |  

23. |     20 |  7902 | FORD   | 3000 |    5 |   10875 |          20 |  

24. |     30 |  7900 | JAMES  |  950 |    1 |     950 |          30 |  

25. |     30 |  7654 | MARTIN | 1250 |    2 |    2200 |          30 |  

26. |     30 |  7521 | WARD   | 1250 |    3 |    3450 |          30 |  

27. |     30 |  7844 | TURNER | 1500 |    4 |    4950 |          30 |  

28. |     30 |  7499 | ALLEN  | 1600 |    5 |    6550 |          30 |  

29. |     30 |  7698 | BLAKE  | 2850 |    6 |    9400 |          30 |  

30. +--------+-------+--------+------+------+---------+-------------+  

31. 14 rows in set (0.05 sec)  

mysql rank() over()

1.  SET @rn=0;  

2.  SET @dense_rank=0;  

3.  SET @last_deptno=-1;  

4.  SET @last_sal=-1;  

5.  SET @ADD = 0;  

6.  SELECT deptno,  

7.         empno,  

8.         ename,  

9.         sal,  

10.        IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,  

11.        IF(@last_sal = sal,@dense_rank := @dense_rank,@dense_rank := @rn) AS rank,  

12.        IF(@last_deptno = deptno, @last_sal := sal, @last_sal := -1) AS last_sal,  

13.        @last_deptno := deptno AS last_deptno  

14.   FROM EMP  

15.  ORDER BY deptno, sal;  

16.   

17. +--------+-------+--------+------+------+------------+----------+-------------+  

18. | deptno | empno | ename  | sal  | rn   | dense_rank | last_sal | last_deptno |  

19. +--------+-------+--------+------+------+------------+----------+-------------+  

20. |     10 |  7934 | MILLER | 1300 |    1 |          1 |       -1 |          10 |  

21. |     10 |  7782 | CLARK  | 2450 |    2 |          2 |     2450 |          10 |  

22. |     10 |  7839 | KING   | 5000 |    3 |          3 |     5000 |          10 |  

23. |     20 |  7369 | SMITH  |  800 |    1 |          1 |       -1 |          20 |  

24. |     20 |  7876 | ADAMS  | 1100 |    2 |          2 |     1100 |          20 |  

25. |     20 |  7566 | JONES  | 2975 |    3 |          3 |     2975 |          20 |  

26. |     20 |  7788 | SCOTT  | 3000 |    4 |          4 |     3000 |          20 |  

27. |     20 |  7902 | FORD   | 3000 |    5 |          4 |     3000 |          20 |  

28. |     30 |  7900 | JAMES  |  950 |    1 |          1 |       -1 |          30 |  

29. |     30 |  7654 | MARTIN | 1250 |    2 |          2 |     1250 |          30 |  

30. |     30 |  7521 | WARD   | 1250 |    3 |          2 |     1250 |          30 |  

31. |     30 |  7844 | TURNER | 1500 |    4 |          4 |     1500 |          30 |  

32. |     30 |  7499 | ALLEN  | 1600 |    5 |          5 |     1600 |          30 |  

33. |     30 |  7698 | BLAKE  | 2850 |    6 |          6 |     2850 |          30 |  

34. +--------+-------+--------+------+------+------------+----------+-------------+  

35. 14 rows in set (0.00 sec)  

mysq dense_rank() over()

1.  SET @rn=0;  

2.  SET @last_deptno=-1;  

3.  SET @last_sal=-1;  

4.  SELECT deptno,  

5.         empno,  

6.         ename,  

7.         sal,  

8.         IF(@last_deptno = deptno, @rn := @rn + IF(@last_sal = sal,0,1), @rn := 1) AS dense_rank,  

9.         IF(@last_deptno = deptno, @last_sal := sal, @last_sal := -1) AS last_sal,  

10.        @last_deptno := deptno AS last_deptno  

11.   FROM EMP  

12.  ORDER BY deptno, sal;  

13.   

14. +--------+-------+--------+------+------------+----------+-------------+  

15. | deptno | empno | ename  | sal  | dense_rank | last_sal | last_deptno |  

16. +--------+-------+--------+------+------------+----------+-------------+  

17. |     10 |  7934 | MILLER | 1300 |          1 |       -1 |          10 |  

18. |     10 |  7782 | CLARK  | 2450 |          2 |     2450 |          10 |  

19. |     10 |  7839 | KING   | 5000 |          3 |     5000 |          10 |  

20.   

21. |     20 |  7369 | SMITH  |  800 |          1 |       -1 |          20 |  

22. |     20 |  7876 | ADAMS  | 1100 |          2 |     1100 |          20 |  

23. |     20 |  7566 | JONES  | 2975 |          3 |     2975 |          20 |  

24. |     20 |  7788 | SCOTT  | 3000 |          4 |     3000 |          20 |  

25. |     20 |  7902 | FORD   | 3000 |          4 |     3000 |          20 |  

26.   

27. |     30 |  7900 | JAMES  |  950 |          1 |       -1 |          30 |  

28. |     30 |  7654 | MARTIN | 1250 |          2 |     1250 |          30 |  

29. |     30 |  7521 | WARD   | 1250 |          2 |     1250 |          30 |  

30. |     30 |  7844 | TURNER | 1500 |          3 |     1500 |          30 |  

31. |     30 |  7499 | ALLEN  | 1600 |          4 |     1600 |          30 |  

32. |     30 |  7698 | BLAKE  | 2850 |          5 |     2850 |          30 |  

33. +--------+-------+--------+------+------------+----------+-------------+  

34. 14 rows in set (0.00 sec)  

mysql row_number() over ()

1.  SET @rn=0;  

2.  SET @last_deptno=0;  

3.  SELECT deptno,  

4.         empno,  

5.         ename,  

6.         sal,  

7.         IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn,  

8.         @last_deptno := deptno AS last_deptno  

9.    FROM EMP  

10.  ORDER BY deptno, sal;  

11.   

12. +--------+-------+--------+------+------+-------------+  

13. | deptno | empno | ename  | sal  | rn   | last_deptno |  

14. +--------+-------+--------+------+------+-------------+  

15. |     10 |  7934 | MILLER | 1300 |    1 |          10 |  

16. |     10 |  7782 | CLARK  | 2450 |    2 |          10 |  

17. |     10 |  7839 | KING   | 5000 |    3 |          10 |  

18.   

19. |     20 |  7369 | SMITH  |  800 |    1 |          20 |  

20. |     20 |  7876 | ADAMS  | 1100 |    2 |          20 |  

21. |     20 |  7566 | JONES  | 2975 |    3 |          20 |  

22. |     20 |  7788 | SCOTT  | 3000 |    4 |          20 |  

23. |     20 |  7902 | FORD   | 3000 |    5 |          20 |  

24.   

25. |     30 |  7900 | JAMES  |  950 |    1 |          30 |  

26. |     30 |  7654 | MARTIN | 1250 |    2 |          30 |  

27. |     30 |  7521 | WARD   | 1250 |    3 |          30 |  

28. |     30 |  7844 | TURNER | 1500 |    4 |          30 |  

29. |     30 |  7499 | ALLEN  | 1600 |    5 |          30 |  

30. |     30 |  7698 | BLAKE  | 2850 |    6 |          30 |  

31. +--------+-------+--------+------+------+-------------+  

32. 14 rows in set (0.01 sec)  

mysql 生成 rownum

 

1.  SET @rn=0;  

2.  SELECT deptno,  

3.         empno,  

4.         ename,  

5.         @rn := @rn + 1 AS rn  

6.    FROM EMP  

7.   ORDER BY 1, 2;  

8.    

9.  mysql> SET @rn=0;  

10. Query OK, 0 rows affected (0.00 sec)  

11.   

12. mysql> SELECT deptno,  

13.     ->        empno,  

14.     ->        ename,  

15.     ->        @rn := @rn + 1 AS rn  

16.     ->   FROM EMP  

17.     ->  ORDER BY 1, 2;  

18. +--------+-------+--------+------+  

19. | deptno | empno | ename  | rn   |  

20. +--------+-------+--------+------+  

21. |     10 |  7782 | CLARK  |    1 |  

22. |     10 |  7839 | KING   |    2 |  

23. |     10 |  7934 | MILLER |    3 |  

24. |     20 |  7369 | SMITH  |    4 |  

25. |     20 |  7566 | JONES  |    5 |  

26. |     20 |  7788 | SCOTT  |    6 |  

27. |     20 |  7876 | ADAMS  |    7 |  

28. |     20 |  7902 | FORD   |    8 |  

29. |     30 |  7499 | ALLEN  |    9 |  

30. |     30 |  7521 | WARD   |   10 |  

31. |     30 |  7654 | MARTIN |   11 |  

32. |     30 |  7698 | BLAKE  |   12 |  

33. |     30 |  7844 | TURNER |   13 |  

34. |     30 |  7900 | JAMES  |   14 |  

35. +--------+-------+--------+------+  

36. 14 rows in set (0.00 sec)  

 

  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值