需求1：何时用in，何时用exists查询
当主表比从表大时，IN查询的效率较高，
当从表比主表大时，EXISTS查询的效率较高，
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
select phone,name from member t1where phone in(select phone from record t2 where win = true);
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
select phone,name from member t1 where exists(select 1 from record t2 where t1.phone=t2.phone and win =true);
需求2：排行榜Top50(按分数和获取时间排名)
set @rank = 0;
select phone, name, (@r2:=@r2 +1) as rank
from record
order by score desc, createTime asc;
需求3： 随机数， 将参与活动的用户，随机抽取6个中奖
select * from momchilovtsi.mslaaccesslog order by RAND() limit 6;
select min(id) ,max(id) momchilovtsi.mslaaccesslog
需求4：获取连续范围的随机数： FLOOR(i + RAND() * (j – i + 1))
随机获得 3333~9999的随机数
set @min = 3333;
set @max = 9999;
select FLOOR(@min+ (RAND() * (@max-@min+1)));
需求5：删除重复数据
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： 列转行统计
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 |
将列依据分隔符进行分割，并得到列转行的结果
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的数即可
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;
