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

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

导读：

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

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

行转列之内容应用：

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

我们以这个案例为例，以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

结果就是我们需要的样子，怎么样看起来还不错吧!至于深度的优化，还是需要看小伙伴们的智慧了!

总结一下：

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

行转列的标准模式：

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

这种方式主要使用的知识点：聚合函数、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

此时出现的结果为下图：但离我们要求的还是差了一步，毕竟一个人的数据，存在多行的情况!

接下来我们再进一步，使用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版学生报表成绩单!

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

另外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

按照官方的语法格式进行编写后，发现了一个严重的问题，那就是行专列失败了!究其原因很简单，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即可，完全不需要有这些烦恼之处，但是肯定是比较笨的一种选择，有的时候急于交差，可以视为一种解决方案了!

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动态行转列的具体应用。