开发者社区> 阿笨net> 正文

数据库行转列的sql语句(zt)

简介: 转载:http://www.cnblogs.com/Charles2008/archive/2008/03/04/1090162.html 问题描述 假设有张学生成绩表(CJ)如下 Name Subject Result 张三 语文 80 张三 数学 90 张三 物理 85 李四 语文...
+关注继续查看

转载:http://www.cnblogs.com/Charles2008/archive/2008/03/04/1090162.html

问题描述
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82

现在 想写 sql 语句     查询后结果 为   
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82       该怎么实现 ?

研究意义

        这是个并不复杂的问题,但却是数据库中行转列的一个典型例子,只要把这个抽象出来的具有普遍意义的问题研究透彻,其他类似的复杂问题迎刃而解。

问题分析

       首先介绍下行转列的概念,也许书上并没有这个概念,行转列说的是这样一类问题:有时候为了数据库表的设计满足用户的动态要求(比如添加字段),我们采用定义字段名表,然后定义一个字段值的表,这样就达到了用静态来表达动态,换句话说就是把数据库表中本来应该是横向的延伸转化为纵向的延伸,再换句话说就是把数据库表中本来应该是字段的增加转化为记录条数的增加。然而,在这样设计下,固然灵活,确带来了统计分析的麻烦,因为统计分析时,应该是以直观的形式进行表现。换言之,统计分析时,我们又应该显示为字段更多的那种。如果同时做到了数据存储时列的增加转化为行的增加,数据提取时又可得到列增加了的数据,数据库表的这种设计就对用户透明了。

        本文前面提出的这个问题就是一个典型的在数据提取时要把以行增加形式的数据转化为以列增加形式的数据。为什么这样说呢?我们注意subject字段,subject里的内容在数据库存储时是以不同数据行的形式,换言之,是以行增加的形式,而输出时,这里面的内容我们要变成字段名了。

        衡量这个问题解决好坏我们有几个标准:1.当数据正好就是上面这个样子时,解决办法能否得到正确的解;2.如果增加科目了科目的种类,解决方法是否仍然能行得通;3.如果有些人的某们课程的成绩还没有下来,换言之,数据库中不是每个人每门课的成绩都可以找到,数据库缺少某个人某门课的成绩的记录。在这种情况下程序还能否得到合理的结果。

试验环境

       本试验使用MS SQL Server 2005环境测试。

试验过程

       1.建立数据表,录入数据

        CREATE TABLE [dbo].[CJ](
[name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[result] [int] NULL,
CONSTRAINT [PK_CJ] PRIMARY KEY CLUSTERED
(
[name] ASC,
[subject] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

     通过可视化界面或者用insert语句录入数据

     2.第一个最直接,最简单的做法

         select distinct c.[name] as 姓名,
(select result from CJ where [name] = c.[name] and subject = '语文' )as 语文,
(select result from CJ where [name] = c.[name] and subject = '数学' )as 数学,
(select result from CJ where [name] = c.[name] and subject = '物理' )as 物理
from CJ c

          主要思想就是把任务分成两步,第一步:把第一列生成出来。第二步:根据第一列每行的姓名取值,查询该同学的各科成绩join到第一步生成的只有一列表。distinct不能省略。

          该方法能够完成该任务,但只能满足前文所述的评价标准1和标准3。当科目增多或者实际科目没有这么多时统计的结果就不那么完美了。换言之,这种方法是静态的,将科目在sql语句里写死了。另外中间的几个sql语句查询效率似乎并不那么高,还需要扫描整个表,实际上应该只需要在一个学生对应的几条记录里找就可以了。

       3.较好的办法

      先不管标准2,想想能不能解决那个扫描的效率问题。于是得到了下面的办法。

      select   [name] as 姓名,
sum(case when subject='语文' then result end) as 语文,
sum(case when subject='数学' then result end) as 数学,
sum(case when subject='物理' then result end) as 物理
from CJ group by [name]

      该办法大致思想类似前一种。最大的改进是用了group by,由于用了group by后字段名除了group by的那个其他不能直接用,加了个集函数,实际上这个Sum只会加一项,因为这个表的主键是name + subject。用了group by就会解决扫描的效率问题,因为sum是计算的每个分组之类的。本方法的技巧之处在于case when的使用。

       这个办法还是不能满足标准2。

      4.较完美的办法

     现在就是怎么解决subject“由死到活”的问题。想到了一种办法如下:

    declare @s nvarchar(1000)
select @s = 'select [name] as 姓名'
select @s = @s + ',sum(case when subject=''' + cast(subject as varchar) + ''' then result end) as ' + subject from CJ group by subject
select @s = @s + ' from CJ group by [name]'
exec(@s)

     其实思想是基于前面那种办法的,关键的地方就是通过动态生成sql语句,然后执行之。

    在@s的第一次累加中的代码中一句from CJ group by subject很是有技巧性,可见简单的select * from table t where .. 也是这么变化无穷,不得不佩服sql或者说关系型数据库的智慧。

本人收获

    a.认真的分析一个简单的问题的来龙去脉是很有意义的事情,浮躁的学风会让你花费大量的时间结果一无所获。

    b.解决一个问题要有清晰的思路,在一时不知道完美的答案时,可试图一步一步优化,向完美的方向靠近。

    c.要善于分析问题的症结所在,即抓住问题的本质。

写到最后

    这个问题暂时就说到这里,之所以把文章写出来是基于两个目的,首先,作为学习心得,不敢独享,希望更多的人能从中得到启发。其次,简单的问题也包含很多高深的知识,希望更多的高手能加入探讨,分析本文的不当之处,并给出更好的办法,或者提供更多的类似的例子,本文希望起到抛砖引玉的作用。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
重温SQL Server的行转列和列转行,面试常考题
行转列,列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 的运算符PIVOT来实现。用传统的方法,比较好理解。层次清晰,而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。
30 0
sqlserver数据库查询某个字段 为null的所有行数据
sqlserver数据库查询某个字段 为null的所有行数据
10 0
SQL 列转行的实现
--列转行,逗号拼接指定列的值SQL Server中写法:SELECT STUFF(( SELECT  ',' + Field1 from TableA FOR XML PATH('')), 1, 1, '') Oracle中写法: 方法一:wmsys.
805 0
Cloud Toolkit 数据库 SQL 执行器
Cloud Toolkit是一个IDE 插件,帮助开发者更高效地开发、测试、诊断并部署应用。 使用本插件,开发者能够方便地将本地应用一键部署到任意机器,或 ECS、EDAS、Kubernetes; 并支持高效执行终端命令和 SQL 等。
4219 0
SQL server 动态行转列
用聚合函数配合CASE语句实现行转列功能: 现在分享一下具体实现代码: 转换前效果: PlanName PlanType PlanLimit 计划1 计划类型1 RMB 1,000,000 计划1 计划类型2 RMB 1,000,000 计划1 计划类型3 ...
856 0
回顾十年数据库流行度,哪款是你的最爱
从数据库流行度总体走势再到不同类型数据库的流行度变化情况
1294 0
+关注
842
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载