年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用

发布于 2022-4-25 16:09
浏览
0收藏

作者 | 跟鸟叔学编程
来源 | 今日头条

导读:

本章主要整理一下软件开发工程师笔试经典的数据库题目,SQL的行转列!年底了,正好也到了纷杂的编写各种年终数据报表的时刻了,趁这个机会好好复习一下该方面的经典应用,以备不时之需!

SQL的行转列应用,主要分成俩个层面的需求,第一是数据内容的转换,第二是列级别的转换!接下来让我们一一深入地学习一下:(本案例采用的是SQLSERVER数据库)

行转列之内容应用:

疫情期间,相信大家去任何公共的场所,都需要检验一下行程码!这就是一个很好的应用,你手机到达基站的范围,会有数据的通信,记录你所经过的位置,最终会展现在你手机的行程码之上!

我们以这个案例为例,以SQL的形式,来重现这日常生活中的经典应用!

新建两张表,一张用户表,另一张为出行途径记录表,二者的关系为经典的一对多强耦合关系!年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

接下里我们需要的数据格式为如下模式:

 

年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

 

这就需要我们将多行的内容,变成一个列的内容!行的内容到列的转换,就是这种情况!在这里需要注意的是,途径地址的数据没有取值范围,是动态的存在!所以我们不能写死,需要灵活地获取!主要采用SQL递归拼接字符串的知识点,就可以为我们解决这个难题!

首先编写SQL函数:该函数的作用,就是将每个用户对应的途径路径全部拼接出来,然后放在SQL中进行使用!

create function GetWayList(@uid int)
returns varchar(1000)
as
begin
	declare @content varchar(1000)
	set @content = ''
	select @content = uwayname +','+ @content from Users_Way where uid = @uid
	order by uwaytime desc 
	if(len(@content)>0)
	begin
		set @content = left(@content,len(@content)-1)
	end
	return @content
end

2.第二步直接在sql中嵌套使用即可,非常的便捷!

SELECT uid,uphone,dbo.GetWayList(uid) as 途径城市 from Users

结果就是我们需要的样子,怎么样看起来还不错吧!至于深度的优化,还是需要看小伙伴们的智慧了!年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

总结一下:

对于这方面的需求,更简单的方式SQLServer中需要XML PATH,这种方式性能很好,但一些云服务并不支持!MySQL中则有Group_concat()函数,则更加的简洁明了!

行转列的标准模式:

有的情况下,我们希望看到行作为列进行展示,这样更加直观地反应数据的情况,看起来比较舒服,也比较直观!例如:一周七天的进货数据,在数据库中是七行,对于开发人员觉得很正常,但老板看起来肯定不爽,周一、周二。。。。。星期日,这种格式的展现更适合非专业人士的审美观!

这种方式主要使用的知识点:聚合函数、SQL分组统计、SQL的多分支结构等!接下来让我们结合案例,实战一下!

下图为我们的数据结构:年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

我们希望最终的查询结果如下:年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

过程分析:

首先我们先编写如下的SQL:

SELECT SNO,SNAME,case subject when '语文' then score else 0 end as 语文,
case subject when '数学' then score else 0 end as 数学,
case subject when '英语' then score else 0 end as 英语
From StudentScore

此时出现的结果为下图:但离我们要求的还是差了一步,毕竟一个人的数据,存在多行的情况!年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

接下来我们再进一步,使用Group By 与 聚合函数(MAX、SUM)都可以,不影响最终的统计数据结果:

SELECT SNO,SNAME,MAX(case subject when '语文' then score else 0 end) as 语文,
MAX(case subject when '数学' then score else 0 end) as 数学,
MAX(case subject when '英语' then score else 0 end) as 英语
From StudentScore
GROUP BY SNO,SNAME

经过上述的努力,终于得到了我们想要的结果:年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

并且可以继续完善字段,搞一张漂漂亮亮的SQL版学生报表成绩单!

SELECT SNO,SNAME,MAX(case subject when '语文' then score else 0 end) as 语文,
MAX(case subject when '数学' then score else 0 end) as 数学,
MAX(case subject when '英语' then score else 0 end) as 英语,
SUM(score) as '总分',CAST((SUM(score)*1.0/3) as decimal(18,2)) as 平均分
From StudentScore
GROUP BY SNO,SNAME

年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

另外SQLServer中还提供了PIVOT函数,可以实现快速的行专列操作!PIVOT函数的格式如下:PIVOT(<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1],[行转列后的列名2],[行转列后的列名3],.......[行转列后的列名N]))

  • <聚合函数>就是我们使用的SUM,COUNT,AVG等Sql聚合函数,也就是行转列后计算列的聚合方式。
  • [聚合列值]要进行聚合的列名
  • [行转列前的列名]这个就是需要将行转换为列的列名。
SELECT SNO,SNAME,语文,数学,英语 FROM StudentScore AS T
PIVOT
(
	--score为汇总成绩列
	--subject 为需要转换的原始列
	--(语文,数学,英语) 转换成的列名
    SUM(score) for subject in(语文,数学,英语)
)TBL

年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

按照官方的语法格式进行编写后,发现了一个严重的问题,那就是行专列失败了!究其原因很简单,PIVOT函数行专列的方式,还是与我们手工编写的原理一样,只不过进行了封装,但是为PIVOT函数提供的数据源必须是干净的,有利于分组的,不能存在难以分组的列,例如自增的ID!接下来我们调整下代码,继续测试!

SELECT * FROM 
(
    SELECT SNO,SNAME,SCORE,SUBJECT FROM StudentScore
)AS T1
PIVOT
(
    SUM(score) for subject in(语文,数学,英语)
)T2
order by SNO 

调整后,我们得到了想要的SQL结果!年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

”贫民版“行专列:

其实行专列的写法,不仅逻辑上有一定的要求,并且需要具备一些空间想象力!但是贫民版的写法,只需要直接撸SQL即可,完全不需要有这些烦恼之处,但是肯定是比较笨的一种选择,有的时候急于交差,可以视为一种解决方案了!

SELECT s.sno,s.sname,sum(ISNULL(YuWen.score,0)) as 语文,
sum(ISNULL(ShuXue.score,0)) as 数学,sum(ISNULL(YingYu.score,0)) as 英语
FROM StudentScore AS S
LEFT JOIN 
(
	SELECT SID,SNO,SCORE FROM StudentScore where subject = '语文'
)AS YuWen on s.sid = YuWen.sid
LEFT JOIN 
(
	SELECT SID,SNO,SCORE FROM StudentScore where subject = '数学'
)AS ShuXue on s.sid = ShuXue.sid
LEFT JOIN 
(
	SELECT SID,SNO,SCORE FROM StudentScore where subject = '英语'
)AS YingYu on s.sid = YingYu.sid
Group by s.sno,s.sname

非常的直接,简单、粗暴的解决方式!年底了,又要搞数据报表了!整理SQL经典笔试题之行转列应用-开源基础软件社区

总结一下:

上述内容就是我们平时编程过程中,遇到的SQL行专列知识点!感兴趣的小伙伴们可以深入地研究一下,并且从性能的角度,给与足够的优化!

但是很多情况下,我们的列并非固定,需要根据实际情况动态生成!下一章我们聊一聊SQL动态行转列的具体应用。

收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐