数据库行转列的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.要善于分析问题的症结所在,即抓住问题的本质。

写到最后

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

目录
相关文章
|
7天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
47 10
|
24天前
|
SQL 存储 BI
【软件设计师备考 专题 】数据库语言(SQL)
【软件设计师备考 专题 】数据库语言(SQL)
89 0
|
7天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
66 6
|
4天前
|
SQL 数据库
数据库SQL语言实战(二)
数据库SQL语言实战(二)
|
4天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。
32 3
|
7天前
|
SQL 监控 数据库
数据库管理与电脑监控软件:SQL代码优化与实践
本文探讨了如何优化数据库管理和使用电脑监控软件以提升效率。通过SQL代码优化,如使用索引和调整查询语句,能有效提高数据库性能。同时,合理设计数据库结构,如数据表划分和规范化,也能增强管理效率。此外,利用Python脚本自动化收集系统性能数据,并实时提交至网站,可实现对电脑监控的实时性和有效性。这些方法能提升信息系统稳定性和可靠性,满足用户需求。
29 0
|
8天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境: 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5,划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检: 由于未知原因,Sql Server数据库文件丢失,丢失数据涉及到3个库,表的数量有3000左右。数据库文件丢失原因还没有查清楚,也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态,所幸没有大量数据写入。 将raid5中所有磁盘编号后取出,经过硬件工程师检测,没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像,镜像完成后将所
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
|
15天前
|
数据库 SQL 索引
什么是数据库 SQL Execution Plan
什么是数据库 SQL Execution Plan
10 0
|
15天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
|
15天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)