首页 mysql基础教程[basic] 详情

---

mysql8.0以下不支持开窗，如果还想实现开窗函数的功能，可以使用变量的方式，以下是例子

一个例子

样例数据

-- 建表
drop table if exists Movie;    
drop table if exists Rating;    
drop table if exists Reviewer;    
create table Movie(mID integer, title varchar(100), year integer, director varchar(100)); 
create table Reviewer(rID integer, name varchar(100));  
create table Rating(rID integer, mID integer, stars integer, ratingDate date); 

insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming'); 
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');  
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise'); 
insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');  
insert into Movie values(105, 'Titanic', 1997, 'James Cameron');  
insert into Movie values(106, 'Snow White', 1937, null);  
insert into Movie values(107, 'Avatar', 2009, 'James Cameron');  
insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');       
insert into Reviewer values(201, 'Sarah Martinez');  
insert into Reviewer values(202, 'Daniel Lewis');  
insert into Reviewer values(203, 'Brittany Harris');  
insert into Reviewer values(204, 'Mike Anderson');  
insert into Reviewer values(205, 'Chris Jackson');  
insert into Reviewer values(206, 'Elizabeth Thomas');  
insert into Reviewer values(207, 'James Cameron');  
insert into Reviewer values(208, 'Ashley White');
insert into Rating values(201, 101, 2, date_format('2012-01-22','%Y-%m-%d')); 
insert into Rating values(201, 101, 4, date_format('2013-01-27','%Y-%m-%d')); 
insert into Rating values(202, 106, 4, null);    
insert into Rating values(203, 103, 2, date_format('2008-01-20','%Y-%m-%d')); 
insert into Rating values(203, 108, 4, date_format('2002-01-12','%Y-%m-%d')); 
insert into Rating values(203, 108, 2, date_format('2009-01-30','%Y-%m-%d')); 
insert into Rating values(204, 101, 3, date_format('2010-01-09','%Y-%m-%d')); 
insert into Rating values(205, 103, 3, date_format('2010-01-27','%Y-%m-%d')); 
insert into Rating values(205, 104, 2, date_format('2010-01-22','%Y-%m-%d')); 
insert into Rating values(205, 108, 4, null);    
insert into Rating values(206, 107, 3, date_format('2013-01-15','%Y-%m-%d')); 
insert into Rating values(206, 106, 5, date_format('2014-01-19','%Y-%m-%d')); 
insert into Rating values(207, 107, 5, date_format('2000-01-20','%Y-%m-%d')); 
insert into Rating values(208, 104, 3, date_format('1999-01-02','%Y-%m-%d'));

题目

--  For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, 
-- return the reviewer's name and the title of the movie.
select
    t3.name,t2.title 
from(
    select 
        rID,mID
    from(
        select 
            case when @rID=a.rID then @lag:=@stars else @lag:=-1 end as b_stars,
            @rID:=a.rID as trID,
            @stars:=a.stars as tstars,
            a.*
        from (  
            select a1.* 
            from rating a1
            join(select rID,mID from rating group by rID,mID having count(*) = 2) a2 on a1.mID = a2.mID and a1.rID = a2.rID
            order by a1.rID,a1.ratingDate
        ) a,
        (select @lag:=null,@rID:=0,@stars:=0) b
    ) t
    where b_stars != -1 and stars > b_stars
) t1
join movie t2 on t2.mID = t1.mID
join reviewer t3 on t3.rID = t1.rID

--  For each movie that has at least one rating, find the movie title 
-- and total number of stars,the highest star and the person who gave highest star. 
select 
 t1.title,t2.t_stars,t2.stars,t4.name
from movie t1
join(
  select mID,sum(stars) as t_stars,max(stars) as stars from rating group by mID
) t2 on t1.mID = t2.mID
join(
  select rID,mID
  from(
    select a.*,
      @dense_rank := case when @lag_part != a.mID then 1 when @lag_dense = a.stars then @dense_rank else @dense_rank + 1 end dr,
      @lag_part := a.mID lag_part,
      @lag_dense := a.stars lag_dense
      from 
        (select * from rating order by mID, stars desc) a,
        (select @dense_rank := 1 , @lag_part := '' , @lag_dense := '' ) b
  ) t
  where dr = 1
) t3 on t3.mID = t2.mID
join reviewer t4 on t4.rID = t3.rID