企业官网建设流程全解析

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

1.行列转换

表tb1：

表tb2：

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

selectnameas'姓名',max(if(course='语文',score,0))as'语文',-- 也可以用summax(if(course='数学',score,0))as'数学',max(if(course='英语',score,0))as'英语'fromtb1groupbyname;

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

select姓名asname,'语文'ascourse,-- 带引号表示此处按字符串对待，而不是标识符语文asscorefromtb2unionallselect姓名asname,'数学'ascourse,数学asscorefromtb2unionallselect姓名asname,'英语'ascourse,英语asscorefromtb2;

2.分组求top-n

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

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

selectname,course,scorefrom(select*,dense_rank()over(partitionbycourseorderbyscoredesc)asrnfromtb1)tbwheretb.rn=1;

结果：

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

select*fromtb1where(selectcount(*)fromtb1 tb2wheretb2.course=tb1.courseandtb2.score>tb1.score)=0;

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

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

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

selectt.uid,min(t.login_date),-- 连续登录的起始日期max(t.login_date),-- 连续登录的结束日期count(*)-- 连续登天数from(select*,date_sub(login_date,intervalrow_number()over(partitionbyuidorderbylogin_date)day)assub_datefromlogin)tgroupbyt.uid,-- 这里分组要带上uid，因为不同用户登录日期作差之后结果可能相等t.sub_datehavingcount(*)>=3;

查询结果：

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

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

查询结果：

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

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

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

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

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

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

查询结果：

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

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

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

5.直播间同时在线人数统计

朋友面试中遇到的一个题，原题：

现在有一张表，记录了一个用户在直播间的进入时间点和退出时间点，表的结构如下：
live_id(直播间id) user_id(用户id) start_tms(进入直播间的时间) end_tms(退出直播间的时间)
1 1 2025-10-01 12:00:01 2025-10-01 12:30:01
2 2 2025-10-01 12:01:01 2025-10-01 12:30:01
1 2 2025-10-01 12:05:01 2025-10-01 12:20:01
2 1 2025-10-01 12:09:01 2025-10-01 12:10:01
业务需求：
1: 哪一个时间段(小时),退出直播间的人数最多?
2: 每一个直播间最大同时在线人数是多少？
3: 每一个直播间人数维持在峰值人数的80%的时长是多久？有几次达到该人数水平？

1、第一问很好解答，hour函数对end_tms取下小时，分组求和排序就行。
2、第二问需要把数据进行拆分，每条数据拆成“进入直播间”+“退出直播间”，进入时给个字段值1，退出时给个字段值-1，用开窗函数对每个直播间按时间排序sum求和，之后找出每个直播间最大的sum结果就行。
自己构造了些测试数据（为了方便答案验证只构造了单个直播间数据）：

withrecordas(SELECT1ASlive_id,101ASuser_id,'2025-10-01 10:00:00'ASstart_tms,'2025-10-01 10:45:00'ASend_tmsUNIONALLSELECT1,102,'2025-10-01 10:05:00','2025-10-01 10:30:00'UNIONALLSELECT1,104,'2025-10-01 10:10:00','2025-10-01 10:25:00'UNIONALLSELECT1,107,'2025-10-01 10:15:00','2025-10-01 10:40:00'UNIONALLSELECT1,102,'2025-10-01 11:05:00','2025-10-01 11:30:00'UNIONALLSELECT1,104,'2025-10-01 11:10:00','2025-10-01 11:25:00'UNIONALLSELECT1,107,'2025-10-01 11:15:00','2025-10-01 11:40:00'),t1as(selectlive_id,user_id,start_tmsasoperate_tms,1asoperatefromrecordunionallselectlive_id,user_id,end_tmsasoperate_tms,-1asoperatefromrecord),t2as(select*,sum(operate)over(partitionbylive_idorderbyoperate_tms,operatedesc)asonline_cntfromt1)selectlive_id,max(online_cnt)fromt2groupbylive_id;

3、第二问计算出了直播间的最大同时在线人数。其中临时表t2是每个进入退出事件发生时刻的最大同时在线人数。用t2表关联每个直播间的最大同时在线人数，根据是否满足阈值对每行数据打标。之后可以通过找每个满足的区间的起始标识转成连续登陆问题计算。
下面为了测试方便将阈值修改为50%计算：

withrecordas(SELECT1ASlive_id,101ASuser_id,'2025-10-01 10:00:00'ASstart_tms,'2025-10-01 10:45:00'ASend_tmsUNIONALLSELECT1,102,'2025-10-01 10:05:00','2025-10-01 10:30:00'UNIONALLSELECT1,104,'2025-10-01 10:10:00','2025-10-01 10:25:00'UNIONALLSELECT1,107,'2025-10-01 10:15:00','2025-10-01 10:40:00'UNIONALLSELECT1,102,'2025-10-01 11:05:00','2025-10-01 11:30:00'UNIONALLSELECT1,104,'2025-10-01 11:10:00','2025-10-01 11:25:00'UNIONALLSELECT1,107,'2025-10-01 11:15:00','2025-10-01 11:40:00'),t1as(selectlive_id,user_id,start_tmsasoperate_tms,1asoperatefromrecordunionallselectlive_id,user_id,end_tmsasoperate_tms,-1asoperatefromrecord),t2as(select*,sum(operate)over(partitionbylive_idorderbyoperate_tms,operatedesc)asonline_cntfromt1),t3as(--第2问的结果selectlive_id,max(online_cnt)asmax_onlinefromt2groupbylive_id),t4as(--对所有同时在线人数按照是否满足「峰值*50%」打标selectt2.*,if(online_cnt>=max_online*0.5,1,0)asvalidfromt2joint3ont2.live_id=t3.live_id),t5as(--当前行中对齐上一行的打标结果和下一行记录的事件时间select*,lag(valid,1,0)over(partitionbylive_idorderbyoperate_tms,operatedesc)asprev_valid,lead(operate_tms,1,null)over(partitionbylive_idorderbyoperate_tms,operatedesc)asnext_operate_tmsfromt4),t6as(--过滤掉所有不符合阈值的时间段，在所有符合阈值时间段中按照「当前事件时间有效+上一事件时间无效」作为每个满足的区间的起始点select*,sum(if(valid=1andprev_valid=0,1,0))over(partitionbylive_idorderbyoperate_tms,operatedesc)asgroup_idfromt5wherevalid=1)selectlive_id,count(distinctgroup_id),--满足次数sum(unix_seconds(cast(next_operate_tmsastimestamp))-unix_seconds(cast(operate_tmsastimestamp)))astotal_valid_seconds--满足总时长 单位s PS:如果最后一条记录也包含在满足的区间内会漏算掉最后一次事件到之后的时间fromt6groupbylive_id;

最大同时在线人数4，满足>=50%阈值时为2。测试数据中满足连续>=2的区间为2025-10-01 10:05:00 ~ 2025-10-01 10:40:00和2025-10-01 11:10:00 ~ 2025-10-01 11:30:00，一共2次55min，输出结果符合预期。