1. 使用一個表的數(shù)據(jù)更新另一個表
網(wǎng)上一般搜到的都是
update table_one set table_one.column_one = table_two.column_one from table_two where table_one.id = table_two.id;
報錯粤咪，據(jù)說是MySQL 和 SQL Server不一樣梯浪，這種方法是SQL Server的混稽。MySQL應(yīng)該這么寫：
update table_one inner join table_two on table_one.id = table_two.id set table_one.column_one = table_two.column_one;
2. 排名
根據(jù)得分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. 使用一個表數(shù)據(jù)插入另一個表
insert into table_two (id, name) select id, name from table_one;
