分享一些常见的SQL计算面试题

代码都是基于mysql实现，如果小伙伴们有其他的思路欢迎留言~

1.行列转换

表tb1：

表tb2：

行转列，也就是tb1->tb2，思路是按学生分组后组内分别求各科成绩。

select
	name as '姓名',
	max(if(course='语文',score,0)) as '语文',		-- 也可以用sum
	max(if(course='数学',score,0)) as '数学',
	max(if(course='英语',score,0)) as '英语'
from tb1 
	group by name;

列转行，tb2->tb1，思路是分别选出所有人的各科成绩，将各科成绩做个union all合并操作。

select
	姓名 as name,
	'语文' as course,		-- 带引号表示此处按字符串对待，而不是标识符
	语文 as score
from tb2
union all
select
	姓名 as name,
	'数学' as course,		
	数学 as score
from tb2
union all
select
	姓名 as name,
	'英语' as course,		
	英语 as score
from tb2;

2.分组求top-n

还用上面行列转换中的那张tb1表，现在求语数英中各科最高分的学生

方式1，最简单的直接使用开窗函数：

select 
	name,
	course,
	score
from (
	select
	*,
	dense_rank() over(partition by course order by score desc) as rn
from tb1
) tb where tb.rn=1;

结果：

方式2，下面这种可能没开窗函数那么直观理解，实现思路其实就是从tb1表里面逐条取数据，每次统计在该记录所属课程的所有记录中，比这个记录大的所有记录总数，如果是0，说明这条记录就是最大的。

select
	*
from tb1
	where (select count(*) from tb1 tb2 where tb2.course=tb1.course and tb2.score>tb1.score) = 0;

3.连续登录问题（包括日期可间断和不可间断）

假设有下面这样的一张登录信息表，表名login：

Q1：找出连续登录3天及以上的用户（日期不可间断）
求解思路是各组内对日期排序后在后面标记一个连续递增值，如果用户连续登录，那登录日期也应该是连续递增的，登陆日期减去这个连续递增值得到的都是同一个固定值。

select
	t.uid,
	min(t.login_date),		-- 连续登录的起始日期
	max(t.login_date),		-- 连续登录的结束日期
	count(*)				-- 连续登天数
from (
	select
		*,
		date_sub(login_date, interval row_number() over(partition by uid order by login_date) day) as sub_date
	from login
) t
group by 
	t.uid,			-- 这里分组要带上uid，因为不同用户登录日期作差之后结果可能相等
	t.sub_date
having
	count(*)>=3;

查询结果：

Q2：找出连续登录3天及以上的用户（间断不超过1天也算连续）
例如“2020-01-19”和“2020-01-21”这两天登录过，也算连续3天登录。

-- 对每组内的登陆日期排好序后下移一位，为了避免各组里面下移后的第一个值为null，用'1970-01-01'作为默认值
with t1 as (
	select
		*,
		lag(login_date,1,'1970-01-01') over(partition by uid order by login_date) as next_date
	from login
),
-- 在上表的基础上两日期列作差，如果差值<=2，说明前后两天连续或者间断不超过1天
t2 as (
	select
		*,
		datediff(login_date,next_date) as sub
	from t1
),
-- 在上表的基础上再次使用开窗函数，目的是对组内对录打标记，相同值的为连续登录组
t3 as (
	select
		*,
		sum(if(sub<=2,0,1)) over(partition by uid order by login_date) as groupid
	from t2
)
-- 最后根据用户uid和连续登录组进行分组，组内最大日期-最小日期>=2时满足连续3天登录
select
	uid,
	min(login_date),		-- 登录起始日
	max(login_date),		-- 不满足连续时的结束日
	datediff(max(login_date),min(login_date))+1 		-- 连续登录天数
from t3
group by 
	uid,
	groupid
having
	datediff(max(login_date),min(login_date))>=2;		-- 注意日期不连续的时候筛选条件就不能用count(*)了

查询结果：

上面的查询结果可能会出现同一个用户出现多次的情况，因为用户可能会在满足连续登录3天之后间断一段时间，之后又满足连续3天登录。所以也可以在上面的结果上基于用户的uid去个重。

为了方便理解，把上面sql查询中的t3表结果贴在下面：

4.找连续出现3次及以上的数字

比如说下面这张表，就叫tb表吧，从中选出连续出现3次及以上的数字。这是面试中被卡过得一道题，当时没啥好的思路，想通过变量计数类似代码编程的方式解决，下来后总觉得不妥，因为毕竟考察的是sql嘛，但又一直想不到好的解法，直到解决了上面那个可间断日期求连续的问题，突然发现这两题解题思路异曲同工。

下面直接给出代码，每个中间部分都有解释：

-- tb表中的num列整体下移一位作为新的一列，第一个值是空值用本身的num填充
with t1 as (
	select
		*,
		lag(num,1,num) over() as next_num
	from tb	
),
-- 在t1表的基础上，用num列减去新列值，如果前后两个数字连续，则差值为0
t2 as (
	select
		*,
		num-next_num as sub
	from t1
),
-- t3主要是用来打标记，将相同连续的数字后面打上相同的值
-- 思路是在t2表的基础上从上到下对sub列做累加，如果sub=0，则加上0，否则加上1
t3 as (
	select
		*,
		-- 这里需要显式指定窗口大小，因为over中没有进行排序操作，默认的窗口是整张表
		sum(if(sub=0,0,1)) over(rows between unbounded preceding and current row) as groupid
	from t2
)
-- 对t3表按照标记值groupid分组，组内数据条数>=3时满足至少连续3次
select
	num,				-- 满足条件的重复数字
	min(id),			-- 重复数字的起始id
	max(id),			-- 重复数字的结束id
	count(*)			-- 重复次数
from t3
group by
	groupid
having 
	count(*)>=3;

查询结果：

下面将原先那种变量计数的实现放在下面，可以了解一下：

select 
	distinct num			-- 这里需要做个去重，因为根据过滤条件，一个出现超过3次的数字会被多次挑选出来
from (
	select
		*,
		case
			-- @prev保存的是当前num的前一个num，如果相等，则计数+1
			when @prev=num then @count:=@count+1
			-- 如果上一步没有执行走到这一步，说明前后两个数字不相等，则将计数重新置为1，同时将num赋值给@prev变量
			when (@prev:=num) is not null then @count:=1
		end as cnt
	from tb
) as t
where cnt>=3;

这里补充一下mysql中=和:=的区别，因为开始在这我是有些迷糊的，导致面试中写的sql没有跑起来，简要来说，:=只有赋值的意思，而=包含赋值和比较是否相等两重意思，具体哪个意思取决于使用场景，所以会有人说当使用set @xxx=xxx为变量赋值时二者都可以，当使用select @xxx=xxx为变量赋值时必须用:=。
下面附上官方文档地址和部分截图：https://dev.mysql.com/doc/refman/8.0/en/assignment-operators.html#operator_assign-equal

4.