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

孙凤霞 發表於 2023-6-15 00:00:00

概述：


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

<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/4a1d082ae94be54fdfa45712c8233ee1.jpg">
</center>
<h3>
接下来我们详细讲解下SQL动态行转列的实现步骤：
</h3>
<h3>
创建模拟数据：
</h3>

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

<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/e8fbc96a2413bd4a4ce8d3e9f49e7fcc.jpg">
</center>
<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/5559b6d06bce977de877799e431ed0a3.jpg">
</center>

插入模拟的数据，用于动态行转案例的使用!

<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/f6335b7f210265f3a6b985a6b13fa815.jpg">
</center>

先写好静态行转列SQL：


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

<ol class="dp-sql">
<li class="alt">
SELECT S.SID,S.sname, 
</li>
<li>
MAX(case c.cname when '数学' then sc.score else 0 end) as 数学, 
</li>
<li class="alt">
MAX(case c.cname when '语文' then sc.score else 0 end) as 语文, 
</li>
<li>
MAX(case c.cname when '英语' then sc.score else 0 end) as 英语 
</li>
<li class="alt">
FROM Student as S 
</li>
<li>
LEFT JOIN SC AS SC ON S.sid = SC.SID 
</li>
<li class="alt">
LEFT JOIN Course AS C ON C.cid = SC.CID 
</li>
<li>
GROUP BY S.sid,S.sname 
</li>
</ol>
<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/e1e7a17a2c683456461fda831c8d0ce1.jpg">
</center>

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

<h3>
编写动态脚本：
</h3>

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


先编写动态的SQL脚本：

<ol class="dp-sql">
<li class="alt">
DECLARE @SQL VARCHAR(MAX) 
</li>
<li>

</li>
<li class="alt">
SELECT @SQL = ' SELECT S.SID,S.SNAME ' 
</li>
<li>
SELECT @SQL = @SQL + ' , ISNULL(MAX(CASE c.cname WHEN '''+cname+''' THEN sc.score END ),0) AS '''+c.cname+''' ' 
</li>
<li class="alt">
FROM Course AS C 
</li>
<li>

</li>
<li class="alt">
print @sql 
</li>
<li>

</li>
<li class="alt">
SELECT @SQL = @SQL + ' FROM Student as S 
</li>
<li>
LEFT JOIN SC AS SC ON S.sid = SC.SID 
</li>
<li class="alt">
LEFT JOIN Course AS C ON C.cid = SC.CID 
</li>
<li>
GROUP BY S.sid,S.sname' 
</li>
<li class="alt">

</li>
<li>
print @sql 
</li>
<li class="alt">

</li>
<li>
EXEC (@SQL) 
</li>
</ol>

测试结果与静态SQL完全一致，看来问题已经解决，接下来就是优化的问题了!

<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/83fbb3e8c0f111a5b8f3e404d933096d.jpg">
</center>

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

<ol class="dp-sql">
<li class="alt">
CREATE PROC StudentScore_Proc 
</li>
<li>
AS 
</li>
<li class="alt">
BEGIN 
</li>
<li>

</li>
<li class="alt">

</li>
<li>
DECLARE @SQL NVARCHAR(MAX) 
</li>
<li class="alt">

</li>
<li>
SELECT @SQL = N' SELECT S.SID,S.SNAME ' 
</li>
<li class="alt">
SELECT @SQL = @SQL + N' , ISNULL(MAX(CASE c.cname WHEN '''+cname+''' THEN sc.score END ),0) AS '''+c.cname+''' ' 
</li>
<li>
FROM Course AS C 
</li>
<li class="alt">

</li>
<li>

</li>
<li class="alt">
SELECT @SQL = @SQL + N' FROM Student as S 
</li>
<li>
LEFT JOIN SC AS SC ON S.sid = SC.SID 
</li>
<li class="alt">
LEFT JOIN Course AS C ON C.cid = SC.CID 
</li>
<li>
GROUP BY S.sid,S.sname' 
</li>
<li class="alt">

</li>
<li>
print @sql 
</li>
<li class="alt">

</li>
<li>
EXECUTE sp_executesql 
</li>
<li class="alt">
@STMT = @SQL 
</li>
<li>

</li>
<li class="alt">
END 
</li>
<li>

</li>
<li class="alt">
EXEC dbo.StudentScore_Proc 
</li>
</ol>

封装完存储过程，我们再执行一下，看看结果!果然没有任何问题，与预期完全一致!

<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/2711a71516280952ce6ec2ec37f579b6.jpg">
</center>

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

<ol class="dp-sql">
<li class="alt">
INSERT INTO Course SELECT 4,'物理' 
</li>
<li>
INSERT INTO Course SELECT 5,'化学' 
</li>
<li class="alt">

</li>
<li>
INSERT INTO SC SELECT 1,4,99 
</li>
<li class="alt">
INSERT INTO SC SELECT 1,5,88 
</li>
<li>
INSERT INTO SC SELECT 2,5,77 
</li>
<li class="alt">

</li>
<li>
EXEC dbo.StudentScore_Proc 
</li>
</ol>

数据改变之后，我们继续测试一下，再次执行我们编写好的存储过程，结果非常完美，随着数据的变化，查询的结果集也是对应的变化，非常NICE，大功告成了!

<center>
<img title="SQL行转列应用的动态实现方式" alt="SQL行转列应用的动态实现方式" src="https://zhuji.jb51.net/uploads/img/202305/b2fd21d5fb1c3b5aad0c044b65299628.jpg">
</center>
<h3>
总结一下：
</h3>

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


原文链接：https://www.toutiao.com/a7042121115925250571/

頁: [1]

琼殿技术论坛's Archiver

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