sql常见面试题（未完待续）

1.长表转宽表

原表结构为：

SELECT year,
       max(case when month=1 then amount else null end) m1,
	   max(case when month=2 then amount else null end) m2,
	   max(case when month=3 then amount else null end) m3,
	   max(case when month=4 then amount else null end) m4
FROM a
group by year;

2.考察窗口函数（rows参数）

查询表中连续三个月以上degree均为A的记录（考察窗口函数的使用，尤其是rows参数的用法）

select name,month,degree,
	   sum(if(degree='A',1,0)) over(partition by name order by month rows between 2 preceding and current row) score1,
	   sum(if(degree='A',1,0)) over(partition by name order by month rows between 1 preceding and 1 following) score2,
	   sum(if(degree='A',1,0)) over(partition by name order by month rows between current row and 2 following) score3
from b;

select t.name,t.month,t.degree
from(
	select name,month,degree,
		   sum(if(degree='A',1,0)) over(partition by name order by month rows between 2 preceding and current row) score1,
		   sum(if(degree='A',1,0)) over(partition by name order by month rows between 1 preceding and 1 following) score2,
		   sum(if(degree='A',1,0)) over(partition by name order by month rows between current row and 2 following) score3
	from b) t
where t.score1=3 or t.score2=3 or t.score3=3

网友Pray、PF提供了一种新思路，供大家参考：

select *
from(
			SELECT t2.*,
			month-rnk AS res
			FROM(
						SELECT t.*,
						row_number() over(PARTITION BY NAME ORDER BY month) AS rnk
						FROM(
									SELECT *
									FROM b
									WHERE degree='A'
							) t
				
				) t2
		) t3
where (name,res) in(
select name,res
from(
			SELECT t2.*,
			month-rnk AS res
			FROM(
						SELECT t.*,
						row_number() over(PARTITION BY NAME ORDER BY month) AS rnk
						FROM(
									SELECT *
									FROM b
									WHERE degree='A'
							) t
				
				) t2
	) t3
group by name,res
having count(*) >=3
);

3.用一条SQL 语句 查询出每门课都大于70分的学生姓名

方法1：
嵌套子查询，子查询要从反面查才能查全

select distinct username 
from score
where username not in(
					select distinct username
					from score
					where per < 70);

方法2：
group by方法

SELECT username FROM score
group by username
having min(per)>70;

4.拷贝表( 拷贝数据, 源表名：a目标表名：b)

create table s1 as
select * from s;

5.复制表( 只复制结构, 源表名：a新表名：b)

目的：让条件不成立为null即可

create table s2 as 
select * from s
where 1=2;

6.删除除了自动编号不同, 其他都相同的学生冗余信息

delete from s
where auto not in(
		select min(auto) as auto
		from s
		group by sid,sname,cid,cname,sc);

注：这样写会报错
翻译为：不能先select出同一表中的某些值，再update这个表(在同一语句中)

正确写法如下

delete from s
where auto not in(
select t.auto
from(
	select min(auto) as auto
	from s
	group by sid,sname,cid,cname,sc) t
	);

也就是说，需要把查询结果当做临时表再查询一次

7.一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合

select *
from name n1
join name n2
on n1.name < n2.name
order by n1.name;

8.从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。请注意：TestDB 中有很多科目，都有1 －6 月份的发生额。

方法1：

select t1.*
from testdb t1
join testdb t2
on t1.id <> t2.id and t1.month = t2.month
where t2.id = 101 and t1.sc > t2.sc
order by t1.id;

这里还有个小知识点：表连接 on and 和 on where 的区别
可参照链接: MySQL表连接 on and 和 on where 的区别.

方法2：

select t1.* 
from testdb t1
join(
	select month,max(sc) sc
	from testdb
	where id = 101
	group by month) t2
on t1.month = t2.month
where t1.sc > t2.sc;

最后结果：

select t1.id
from testdb t1
join testdb t2
on t1.id <> t2.id and t1.month = t2.month
where t2.id = 101 and t1.sc > t2.sc
group by t1.id
having count(t1.id) = 6
order by t1.id;

9.相邻id进行调换

方法1：终极无敌究极麻烦法
（用了表的自连接，当时我怎么想的！！！能再麻烦点吗！！）

select s1.id,s2.student
from(
    select *,max(id) over() m
    from seat
)s1 
join seat s2
on s1.id != s2.id or s1.id=s2.id
where if(s1.id mod 2=1,s1.id,null)+1=s2.id or if(s1.id mod 2=0,s1.id,null)-1=s2.id or if(m mod 2=1,(s1.id=m and s1.id=s2.id),null)
order by s1.id

方法2：case-when简洁法
case
when 条件1 then 结果1
…
else 结果3
end
注：这里可将最大id直接与原表一起from，而不用什么窗口函数

select
    (case 
    when id mod 2=0 then id-1
    when id mod 2=1 and id=m then id
    else id+1
    end) id,
    student
from seat,(select max(id) m from seat) t
order by id

方法3：if语句法
这里mod建议还是用mod函数的形式，易于观看

select if(mod(s.id,2)=0,s.id-1,(if(s.id=t.m,s.id,s.id+1))) id
         ,student
from seat s,(select max(id) m from seat) t
order by id

10.oppo面试题

用户登陆表表名：user_time，字段名：uid（用户id）, time（登陆时间）
1.1求2020-08-01日至2020-08-07日每天的7日留存率（7日留存：一个用户当日活跃，7天后依然活跃）

select fd,count(case when datediff(date_format(u.time,'%Y-%m-%d'),fd)=7 then u.uid else null end)/count(distinct uid) 7_re_rate
from user_time u
left join 
	(select uid,min(date_format(time,'%Y-%m-%d')) fd
	from user_time
	where date_format(time,'%Y-%m-%d') between '2020-08-01' and '2020-08-07'
	group by uid) t
on u.uid = t.uid
group by fd
order by fd;

1.2求每个用户相邻两次登陆时间之差小于3分钟的次数

select uid,count(*)
from(
	select uid,time,lag(time,1) over(partition by uid) l
	from(
		select uid,time
		from user_time
		order by uid,time) t
	) t1
where timestampdiff(minute,l,time) < 3 
group by uid;