mysql使用自定义变量实现窗口函数

create table income_tl(
	user_id int,
	create_date date,
	income int
);
 
insert into income_tl values(1,'2016-03-01',100);
insert into income_tl values(1,'2016-03-02',300);
insert into income_tl values(1,'2016-03-03',200);
insert into income_tl values(1,'2016-03-04',500);
insert into income_tl values(1,'2016-03-05',500);
 
insert into income_tl values(2,'2016-03-01',200);
insert into income_tl values(2,'2016-03-02',300);
insert into income_tl values(2,'2016-03-03',300);
insert into income_tl values(2,'2016-03-04',500);
insert into income_tl values(2,'2016-03-05',400);



select * from income_tl;
-- 分析函数
-- 查询每个员工当月每天工资的，累计的方式

-- 在set语句中,给用户变量赋值,=或者:=都可以,但是在非set语句中,只能使用:=,例如select语句,因为在非set语句中=被视为一个比较操作符.
-- case
-- when condition then result
-- when condition then result
-- when condition then result
-- else result
-- 当满足某一条件时，执行某一result
--  (select @preVal:=null, @curVal:=null) r 初始化@preVal和@curVal变量
select it.user_id, 
       it.create_date, 
       it.income,
       case
          when @preVal = it.user_id then @curVal := @curVal + it.income
          when @preVal := it.user_id then @curVal := it.income
       end AS sum_income
from income_tl it, (select @preVal:=null, @curVal:=null) r
order by it.user_id asc, it.create_date asc;

-- mysql模拟窗口函数

CREATE TABLE T1(  
    ID INT,  
    DATAVALUE INT  
);
 
INSERT INTO T1 VALUES(1,10);  
INSERT INTO T1 VALUES(1,20);  
INSERT INTO T1 VALUES(1,100);  
INSERT INTO T1 VALUES(1,200);
 
INSERT INTO T1 VALUES(2,100);  
INSERT INTO T1 VALUES(2,200);  
INSERT INTO T1 VALUES(2,300);  
INSERT INTO T1 VALUES(2,400);
 
SELECT * FROM T1;

SELECT IT.ID,   
       IT.DATAVALUE,  
       CASE  
          WHEN @PREVAL = IT.ID THEN @CURVAL := @CURVAL + IT.DATAVALUE  
          WHEN @PREVAL := IT.ID THEN @CURVAL := IT.DATAVALUE  
       END AS F
FROM T1 IT, (SELECT @PREVAL:=NULL, @CURVAL:=NULL,@P_D,@C_D) R  ;







-- 案例
drop table  if exists order_info;

create table order_info
(
    order_id int primary key,
    user_no varchar(10),
    amount int,
    create_date datetime
);

insert into order_info values (1,'u0001',100,'2018-1-1');
insert into order_info values (2,'u0001',300,'2018-1-2');
insert into order_info values (3,'u0001',300,'2018-1-2');
insert into order_info values (4,'u0001',800,'2018-1-10');
insert into order_info values (5,'u0001',900,'2018-1-20');

insert into order_info values (6,'u0002',500,'2018-1-5');
insert into order_info values (7,'u0002',600,'2018-1-6');
insert into order_info values (8,'u0002',300,'2018-1-10');
insert into order_info values (9,'u0002',800,'2018-1-16');
insert into order_info values (10,'u0002',800,'2018-1-22');

select * from order_info;

SELECT * FROM 
(
    SELECT 
　　 IF(@y=a.user_no, @x:=@x+1, @x:=1) X ,
    IF(@y=a.user_no, @y, @y:=a.user_no) Y,
    a.*
    FROM order_info a, (SELECT @x:=0, @y:=NULL) b
    ORDER BY a.user_no, a.create_date desc
) c
WHERE X <= 1;