需求1：何時用in，何時用exists查詢
當(dāng)主表比從表大時，IN查詢的效率較高，
當(dāng)從表比主表大時，EXISTS查詢的效率較高，
in是先執(zhí)行子查詢，得到一個結(jié)果集，將結(jié)果集代入外層謂詞條件執(zhí)行主查詢，子查詢只需要執(zhí)行一次
select phone,name from member t1where phone in(select phone from record t2 where win = true);
exists是先從主查詢中取得一條數(shù)據(jù)，再代入到子查詢中，執(zhí)行一次子查詢，判斷子查詢是否能返回結(jié)果，主查詢有多少條數(shù)據(jù)，子查詢就要執(zhí)行多少次
select phone,name from member t1 where exists(select 1 from record t2 where t1.phone=t2.phone and win =true);
需求2：排行榜Top50(按分?jǐn)?shù)和獲取時間排名)
set @rank = 0;
select phone, name, (@r2:=@r2 +1) as rank
from record
order by score desc, createTime asc;
需求3： 隨機數(shù)， 將參與活動的用戶，隨機抽取6個中獎
select * from momchilovtsi.mslaaccesslog order by RAND() limit 6;
select min(id) ,max(id) momchilovtsi.mslaaccesslog
需求4：獲取連續(xù)范圍的隨機數(shù)： FLOOR(i + RAND() * (j – i + 1))
隨機獲得 3333~9999的隨機數(shù)
set @min = 3333;
set @max = 9999;
select FLOOR(@min+ (RAND() * (@max-@min+1)));
需求5：刪除重復(fù)數(shù)據(jù)
select * from msg a
where id < (select max(id) from msg b
where a.aid= b.aid
and a.b_code=b.b_code
and a.add_timestamp=b.add_timestamp)
需求6： 列轉(zhuǎn)行統(tǒng)計
set names utf8;
select identity,
sum(ct),
sum(IF(channel = ‘1’, ct,0)) as channel_num_1,
sum(IF(channel = ‘2’, ct,0)) as channel_num_2,
sum(IF(channel = ‘3’, ct,0)) as channel_num_3,
sum(IF(channel = ‘unknown’, ct,0)) as channel_num_unknown
from (
select identity, ifnull(channel,’unknown’) channel, count(1) ct
from user group by identity,channel
) t
group by identity
格式：
id | value |
1 | 1,2,3 |
2 | 1,2 |
3 | 3 |
將列依據(jù)分隔符進(jìn)行分割，并得到列轉(zhuǎn)行的結(jié)果
id | value |
1 | 1 |
1 | 2 |
1 | 3 |
2 | 1 |
2 | 2 |
3 | 3 |
select * from name a;
select * from squence b; // 序列表，只有一列id,存放1~100的數(shù)即可
select a.id, substring_index(substring_index(a.answer,’,’,b.id),’,’,-1)
from name a join squence b
on b.id <= (length(a.answer) – length(replace(a.answer,',',''))+1)
order by a.id, b.id;
