1.查找表中重复n次的数据
select name
from class
group by name
having count(name) > n;
2.查找第N高的数据
select distinct score #去掉重复值！！
from scores
order by score desc
limit 1 offset n-1 #倒序后跳过前n-1条数据，然后读取一条数据
3.查询不在表中的数据
法1：多表联结
select name
from 表1 as a left join 表2 as b
on a.xx = b.xx
where b.xxx is null
法2：子查询
select name
from 表1 as a
where a.xx not in (
select b.xx
from 表2 as b)
4.计算当前在职员工的薪水涨幅
员工表：编号，雇用日期；薪水表：编号，薪水，起始日期，结束日期
法1:多表联结
select a.编号,(c.薪水-b.薪水) as 薪水涨幅
from 员工表 as a inner join 薪水表 as b on a.编号 = b.编号
inner join 薪水表 as c on a.编号 = c.编号
where a.雇佣日期 = b.起止日期
and c.结束日期 = '2004-01-01'
法2：子查询
select m.编号,当前薪水-入职薪水 as 薪水涨幅
from
(select 编号,薪水 as 当前薪水
from 薪水表
where 结束日期 = '2004-01-01') as m
inner join
(select a.编号,薪水 as 入职薪水
from 员工表 as a
left join 薪水表 as b
on a.编号 = b.编号
where a.雇佣日期 = b.起止日期) as n
on m.编号 = n.编号
5.找出比前一天营业额高的数据
select *
from 日销 as a inner join 日销 as b on datediff(a.日期,b.日期) = 1
where a.销售额 > b.销售额
6.查询系统内人员的满意度
满意度：教师，学生，是否满意；用户：编号，是否在系统
第一步查询出在系统的人员，此处三表联结，每一行均同时满足两个on里的条件！！：
第二步加入计算满意度代码，即满意数/总数，终代码：
select sum(case when 是否满意 = '是' then 1 else 0 end) / count(是否满意)
from 满意度
inner join (select 编号 from 用户 where 是否在系统 = '是') as 教师
on (满意度.教师 = 教师.编号)
inner join (select 编号 from 用户 where 是否在系统 = '是') as 学生
on (满意度.学生 = 学生.编号)
7.查询4种行为：已购买、购买未收藏、收藏未购买、收藏且购买
select o.用户id,o.商品id,(case when a.用户id is not null then 1 else 0 end) as '已购买',
(case when a.用户id is not null and b.用户id is null then 1 else 0 end) as '购买未收藏',
(case when a.用户id is null and b.用户id is not null then 1 else 0 end) as '收藏未购买',
(case when a.用户id is not null and b.用户id is not null then 1 else 0 end) as '收藏且购买'
from (select 用户id,商品id from 用户订单表
union
select 用户id,商品id from 用户收藏商品表) as o
left join 用户订单表 as a on o.用户id = a.用户id and o.商品id = a.商品id
left join 用户收藏商品表 as b on o.用户id = b.用户id and o.商品id = b.商品id
order by o.用户id
8.交换数据
select (case when mod(id,2) != 0 and id != (
select count(id) from seat) then id + 1
when mod(id,2) = 0 then id - 1
else id
end) as id2,student
from seat
order by id2
9.统计每个班的平均分上80的同学人数及占比
学生表：姓名，学号，班级；成绩表：学号，课程号，分数
select a.班级, sum(case when b.平均分 >= 80 then 1 else 0 end) as 人数,
sum(case when b.平均分 >= 80 then 1 else 0 end) / count(a.学号) as 占比
from 学生表 as a left join (
select 学号,avg(分数) as 平均分 from 成绩表 group by 学号) as b
on a.学号 = b.学号
group by 班级
order by 班级
10.行列互换
select A,
max(case B when 'm' then C else 0 end) as 'm',
max(case B when 'n' then C else 0 end) as 'n'
from cook
group by A
11.计算相机应用的次日留存用户数
情况1：表中无重复数据：
select a.登陆时间,
count(case when timestampdiff(day,a.登陆时间,b.登陆时间)=1 then 1 else null end) as 次日留存数
from 留存问题 as a inner join 留存问题 as b
on a.用户id = b.用户id
where a.应用名称 = '相机' and b.应用名称='相机'
group by a.登陆时间
order by a.登陆时间
情况1：表中有重复数据，需要去重
select c.at,
count(case when timestampdiff(day,c.at,c.bt)=1 then 1 else null end) as 次日留存数
from (select distinct a.用户id,a.`登陆时间` as at,b.`登陆时间` as bt
from 留存问题 as a inner join 留存问题 as b
on a.用户id = b.用户id
where a.应用名称 = '相机' and b.应用名称='相机') as c
group by c.at
order by c.at
注意问题：自联结时，临时表中的相同字段名需要有别名！！！（如情况2中c表中的字段名）
