mysql sql总结

1.两张表做积

select * from Students a, Teachers b

2.并列合并两张表

# column数量相同
select * from Scores union (select title,status from seed limit 2);

3.生成行标

select a.Score,@num:=@num+1 from Scores a,(select @num := 0) b;

4.排名不跳，连续加1(但是有bug 如果score只有一个且为0)

select a.Score,if(@pre_score!=a.Score,@num:=@num+1,@num),@pre_score:=a.Score from Scores a,(select @pre_score:=0, @num := 0) b order by a.Score desc;

5.排名连续，不连跳

select d.Score,e.Rank from Scores d join (select b.*,@num:=@num+1 as "Rank" from (select * from (select Score from Scores group by Score) a order by a.Score desc) b,(select @num:=0) c) e on d.Score=e.Score order by d.Score desc;
或者
select d.Score,e.Rank from Scores d join (select b.*,@num:=@num+1 as "Rank" from (select distinct Score from Scores order by Score desc) b,(select @num:=0) c) e on d.Score=e.Score order by d.Score desc;

解题思路：
1. 去重排序子表
select distinct Score from Scores order by Score desc
2. 对子表添加排序
select b.*,@num:=@num+1 as "Rank" from (select distinct Score from Scores order by Score desc) b,(select @num:=0) c
3. join 
select d.Score,e.Rank from Scores d join (select b.*,@num:=@num+1 as "Rank" from (select distinct Score from Scores order by Score desc) b,(select @num:=0) c) e on d.Score=e.Score order by d.Score desc;