记录一些SQL语句

1. 使用一个表的数据更新另一个表

网上一般搜到的都是

update table_one set table_one.column_one = table_two.column_one from table_two where table_one.id = table_two.id;

报错，据说是MySQL 和 SQL Server不一样，这种方法是SQL Server的。MySQL应该这么写：

update table_one inner join table_two on table_one.id = table_two.id set table_one.column_one = table_two.column_one;

2. 排名

根据得分score算排名，同分相同排名，排名跳变

select id, score,
@curRank := if( @preScore= score, @curRank, @incRank) as rank,
@incRank := @incRank + 1 as num,
@preScore:= score as pre
from table_test, (select @preScore:= null, @curRank := 0, @incRank := 1) r
order by table_test.score

同分相同排名，排名不跳变

select id, score,
@curRank := if( @preScore= score, @curRank, @curRank+1) as rank,
@preScore:= score as pre
from table_test, (select @preScore:= null, @curRank := 0) r
order by table_test.score

同分不同排名

select id, score,
@incRank := @incRank + 1 as rank,
@preScore:= score as pre
from table_test, (select @preScore:= null, @incRank := 0) r
order by table_test.score

3. 使用一个表数据插入另一个表

insert into table_two (id, name) select id, name from table_one;