SQL Server数码库行转列的sql语句(zt)

转载: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.要善于分析问题之症结所在,即引发问题的本质。

描绘到最后

   
这个问题暂时虽说交此处,之所以把文章写出来是冲两个目的,首先,作为学习心得,不敢独自享,希望再度多之人头能从中得到启示。其次,简单的问题也暗含众多深的文化,希望再多的高手能进入追,分析本文的不当之处,并给闹更好之艺术,或者提供更多的类的例子,本文希望由及抛砖引玉的用意。

相关文章