SQL行转列应用的动态实现方式

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

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

概述:

SQL行转列的需求,在项目中还是经常可见的,尤其报表类的应用,更是非常广泛!上期我们讲了SQL行转列的静态实现方式,本期搞一下行转列的动态实现方案,解决方案并不唯一,这里采用存储过程的实现方式!

接下来我们详细讲解下SQL动态行转列的实现步骤:

创建模拟数据:

这里还是老套路,IT编程人入门的经典学生选课表系列,学生表、课程表、成绩表!就拿这套耳熟能详的表结构进行讲解!

SQL行转列应用的动态实现方式-开源基础软件社区

 

SQL行转列应用的动态实现方式-开源基础软件社区

插入模拟的数据,用于动态行转案例的使用!SQL行转列应用的动态实现方式-开源基础软件社区

先写好静态行转列SQL:

这一步相对还是比较重要,毕竟我们要在一个静态的行转列基础之上,构建动态的行转列应用,课程数据会有动态变化,学生也会选择新开的课程,这样静态模式势必不会有效,但参照静态模板,去开发动态的模式,则更加有参照性!

SELECT S.SID,S.sname, 
MAX(case c.cname when '数学' then sc.score else 0 end) as 数学,
MAX(case c.cname when '语文' then sc.score else 0 end) as 语文,
MAX(case c.cname when '英语' then sc.score else 0 end) as 英语
FROM Student as S
LEFT JOIN SC AS SC ON S.sid = SC.SID
LEFT JOIN Course AS C ON C.cid = SC.CID
GROUP BY S.sid,S.sname

SQL行转列应用的动态实现方式-开源基础软件社区

通过测试,数据效果没有问题,正是我们期待的样子!

编写动态脚本:

动态行转列无疑需要使用SQL编程的技术,动态的递归课程名称,这样才可以一劳永逸的解决问题!

先编写动态的SQL脚本:

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = ' SELECT S.SID,S.SNAME '
SELECT @SQL = @SQL + ' , ISNULL(MAX(CASE c.cname WHEN '''+cname+''' THEN sc.score END ),0) AS '''+c.cname+''' ' 
FROM Course  AS C
print @sql
SELECT @SQL = @SQL + ' FROM Student as S
LEFT JOIN SC AS SC ON S.sid = SC.SID
LEFT JOIN Course AS C ON C.cid = SC.CID 
GROUP BY S.sid,S.sname'
print @sql
EXEC (@SQL)

测试结果与静态SQL完全一致,看来问题已经解决,接下来就是优化的问题了!SQL行转列应用的动态实现方式-开源基础软件社区

将上述的动态脚本封装成存储过程,第一可以尽量地提升查询效率,第二方便代码段的调用!

CREATE PROC StudentScore_Proc
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = N' SELECT S.SID,S.SNAME '
SELECT @SQL = @SQL + N' , ISNULL(MAX(CASE c.cname WHEN '''+cname+''' THEN sc.score END ),0) AS '''+c.cname+''' ' 
FROM Course  AS C
SELECT @SQL = @SQL + N' FROM Student as S
LEFT JOIN SC AS SC ON S.sid = SC.SID
LEFT JOIN Course AS C ON C.cid = SC.CID 
GROUP BY S.sid,S.sname'
print @sql
EXECUTE sp_executesql
@STMT = @SQL
END

封装完存储过程,我们再执行一下,看看结果!果然没有任何问题,与预期完全一致!SQL行转列应用的动态实现方式-开源基础软件社区

这时候我们更改一下数据,课程表中新增物理、化学两门课程,诺克萨斯之手分别选择了两门课程,盖伦仅仅选择了化学,武器大师逃学,俩门课都没有选择。

INSERT INTO Course SELECT 4,'物理'
INSERT INTO Course SELECT 5,'化学'
INSERT INTO SC SELECT 1,4,99
INSERT INTO SC SELECT 1,5,88
INSERT INTO SC SELECT 2,5,77
EXEC dbo.StudentScore_Proc

数据改变之后,我们继续测试一下,再次执行我们编写好的存储过程,结果非常完美,随着数据的变化,查询的结果集也是对应的变化,非常NICE,大功告成了!SQL行转列应用的动态实现方式-开源基础软件社区

总结一下:

连续俩篇的文章更新,SQL行转列在项目中的应用都已经涵盖了。即将步入年底了,肯定有很多小伙伴被客户、领导追着搞各种报表,希望对小伙伴们有些许的帮助。#学编程##互联网##IT#

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