Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the realization method of Mysql-like oracle analysis function sum over". In daily operation, I believe that many people have doubts about the implementation of Mysql-like oracle analysis function sum over. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what is the realization method of Mysql-like oracle analysis function sum over?" Next, please follow the editor to study!

First, let's see how oracle implements it.

Select deptno,ename,sal,sum (sal) over (order by ename) from emp;-- continuous summation of name sort

Select deptno,ename,sal,sum (sal) over (order by deptno) from emp;-- all departments sort and sum continuously

Select deptno,ename,sal,sum (sal) over (partition by deptno) from emp;-the sum of all departments

Select deptno,ename,sal,sum (sal) over (partition by deptno order by ename) from emp;-continuous summation between departments

Select deptno,ename,sal,sum (sal) over (order by deptno,ename) from emp

Select deptno,ename,sal

Continuous summation of sum (sal) over (partition by deptno order by ename) departments-- "continuous" summation of salaries of each department

The sum of sum (sal) over (partition by deptno) departments-- the sum of department statistics. The sum of the same department remains the same.

100*round (sal/sum (sal) over (partition by deptno), 4) "sector share (%)"

Sum (sal) over (order by deptno, ename) continuous summation,-- "continuous" summation of salaries in all departments

The sum of sum (sal) over (), where sum (sal) over () is equivalent to sum (sal), the total salary of all employees

100*round (sal/sum (sal) over (), 4) "Total share (%)"

From emp

The realization of mysql

As follows:

SELECT a.id,a.user_id,a.borrow_id, a.repayment_money

(SELECT SUM (repayment_money) FROM rb_repayment_period WHERE id select * from tbl

->

+-+ +

| | id | col |

+-+ +

| | 1 | 26 |

| | 2 | 46 |

| | 3 | 35 |

| | 4 | 68 |

| | 5 | 93 |

| | 6 | 92 |

+-+ +

6 rows in set (0.00 sec)

To achieve one:

Implementation 2: solve the problem of duplicate bug (first create a digital table Nums (an int) insert 1-100)

Step 2:

MySQL [interface_hd_com] > select Nums.a+c.rownum as rank, col from (select A. col. count (*) as count, (select count (*) from testtt b where b.col SELECT MIN (id) start_pos,MAX (id) end_pos)

-> FROM

-> (SELECT id,alias1, id-alias1) AS diff FROM (SELECT id,@id:=@id+1 AS alias1 FROM tbl, (SELECT @ id:=0) AS id) b)

-> AS c

-> GROUP BY diff

+-+ +

| | start_pos | end_pos |

+-+ +

| | 11 | 16 | |

| | 18 | 19 |

+-+ +

2 rows in set (0.00 sec)

Experiment: find the same continuous segment of tel

According to the above ideas.

MySQL [interface_hd_com] > SELECT MIN (id) start_pos,MAX (id) end_pos,tel FROM (SELECT id,alias1, (id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab, (SELECT @ id:=0) AS id) b) as c GROUP BY diff,tel order by tel desc

+-+

| | start_pos | end_pos | tel | |

+-+

| | 3 | 7 | 187164 | |

| | 1 | 8 | 187163 | |

| | 9 | 9 | 19999 | |

+-+-there is bug in this way

It is found that this is not possible, because the id is continuous, so the diff of the same tel is the same, but in fact there are other tel in the middle.

Solution: seek the merger twice.

Union for a moment

MySQL [interface_hd_com] > SELECT MIN (id) start_pos,MAX (id) end_pos,tel FROM (SELECT id,alias1, (id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab, (SELECT @ id:=0) AS id where tel in (SELECT distinct (tel) from testtab where tel187164)) b) as c GROUP BY diff,tel order by tel desc

+-+

| | start_pos | end_pos | tel | |

+-+

| | 1 | 2 | 187163 | |

| | 5 | 6 | 187163 | |

| | 8 | 8 | 187163 | |

| | 9 | 9 | 19999 | |

+-+

4 rows in set (0.00 sec)

MySQL [interface_hd_com] > SELECT MIN (id) start_pos,MAX (id) end_pos,tel FROM (SELECT id,alias1, (id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab, (SELECT @ id:=0) AS id where tel in (187164)) b) as c GROUP BY diff,tel order by tel desc

+-+

| | start_pos | end_pos | tel | |

+-+

| | 3 | 4 | 187164 | |

| | 7 | 7 | 187164 | |

+-+

2 rows in set (0.00 sec)

MySQL [interface_hd_com] > select * from testtab

+-+ +

| | id | tel |

+-+ +

| | 1 | 187163 |

| | 2 | 187163 |

| | 3 | 187164 | |

| | 4 | 187164 |

| | 5 | 187163 | |

| | 6 | 187163 | |

| | 7 | 187164 | |

| | 8 | 187163 | |

| | 9 | 19999 | |

+-+ +

9 rows in set (0.00 sec)

Step 1: Mark

Mysql > SELECT id,@id:=@id+1 AS alias1 FROM tbl, (SELECT @ id:=0) AS id

+-+ +

| | id | alias1 |

+-+ +

| | 11 | 1 |

| | 12 | 2 |

| | 13 | 3 |

| | 14 | 4 |

| | 15 | 5 |

| | 16 | 6 |

| | 18 | 7 |

| | 19 | 8 |

+-+ +

8 rows in set (0.00 sec)

The first step is to figure out the number.

MySQL [interface_hd_com] > select A. coll count (*) as count, (select count (*) from testtt b where b.col select id,a.col, (select count (*) from tbl b where b.col select id,a.col, (select count (*) from tbl b where b.col)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.