关于表中行列转换的几个小问题

Question

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
取每个学生的前2条记录：
select 姓名,课程,分数
from tb as b
where(select count(*) 
from tb
 where 姓名=b.姓名 and 分数<b.分数)<2我这里有三个疑问，希望大家能给我指点指点。
1、静态SQL中，怎么有人用max，有人用sum，我试了一下，用min也可以，这是怎么回事呢？
2、动态SQL中，select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'中，''' + 课程 + ''' 为什么要用3个单引号，每一个都代表什么意思呢？我试着改了几下，把它减少或增加都会使结果产生变化。
3、取每个学生的前2条记录中，“ 分数<b.分数”我看不懂，为什么要这样比较。还有，取前两条记录，为什么是<2,而不是<3?我是刚学SQL SERVER 2000 一个月的新手，在此请教各位前辈了，呵呵。。

Answer 1

动态sql语句基本语法 
1 :普通SQL语句可以用Exec执行 eg:   Select * from tableName 
         Exec('select * from tableName') 
         Exec sp_executesql N'select * from tableName'    -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg:   
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName              -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName')     -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s)                -- 成功 
exec sp_executesql @s   -- 此句会报错 declare @s Nvarchar(1000)  -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s)                -- 成功     
exec sp_executesql @s   -- 此句正确 3. 输出参数 
declare @num int, 
        @sqls nvarchar(4000) 
set @sqls='select count(*) from tableName' 
exec(@sqls) 
--如何将exec执行结果放入变量中？ declare @num int, 
               @sqls nvarchar(4000) 
set @sqls='select @a=count(*) from tableName ' 
exec sp_executesql @sqls,N'@a int output',@num output 
select @num 

Answer 2

1、因为不管 max()\sum()\min()都只能取到一个有效数据，因此结果相同2、在字符串中一对单引号替代一个转义的单引号3、在子查询中已经有一个“<”的条件，过滤掉了一条记录；如果将其修改为"<="，则应该用3。 

Answer 3

print @sql
--exec(@sql)

Answer 4

IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(姓名 NVARCHAR(10), 课程 NVARCHAR(10), 分数 INT)
INSERT TB SELECT N'张三' ,N'语文', 74
INSERT TB SELECT N'张三' ,N'语文', 84 --如果此處加一筆相同的 用SUM 就看出區別了
INSERT TB SELECT N'张三' ,N'数学' ,83
INSERT TB SELECT N'张三' ,N'物理' ,93
INSERT TB SELECT N'李四' ,N'语文' ,74
INSERT TB SELECT N'李四' ,N'数学' ,84
INSERT TB SELECT N'李四', N'物理' ,94
select 姓名 as 姓名 ,
  SUM(case 课程 when N'语文' then 分数 else 0 end) 语文,
  max(case 课程 when N'数学' then 分数 else 0 end) 数学,
  max(case 课程 when N'物理' then 分数 else 0 end) 物理
from tb
group by 姓名
/*姓名         语文          数学          物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         158         83          93*/
select 姓名 as 姓名 ,
  MAX(case 课程 when N'语文' then 分数 else 0 end) 语文,
  max(case 课程 when N'数学' then 分数 else 0 end) 数学,
  max(case 课程 when N'物理' then 分数 else 0 end) 物理
from tb
group by 姓名
/*
姓名         语文          数学          物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         84          83          93*/
select 姓名 as 姓名 ,
  MIN(case 课程 when N'语文' then 分数 else 0 end) 语文,
  max(case 课程 when N'数学' then 分数 else 0 end) 数学,
  max(case 课程 when N'物理' then 分数 else 0 end) 物理
from tb
group by 姓名
/*姓名         语文          数学          物理          
---------- ----------- ----------- ----------- 
李四         0           84          94
张三         0           83          93*/

Answer 5

--2、在字符串中一对单引号替代一个转义的单引号
DECLARE @T TABLE(TEST NVARCHAR(10))
INSERT @T SELECT ''''
SELECT * FROM @T
/*
TEST       
---------- 
'
*/

Answer 6

运行一下:
declare @sql varchar(1000)
set @sql='set col1='+'''abc'''
select @sql
可得到:
set col1='abc'
如果只加一个单引号,则为
set col1=abc
这里的abc就会看成是另一个列.

Answer 7

1.用max() , min() , sum()都是分组用的,一般来说,字符,只取一个用max,用到合计则用sum,你拷贝我的行列转换中后面就有SUM的例.2.同样把print(@SQL)打印出来看.另引号的问题参考动态语句的语法.
--动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName 
Exec('select * from tableName') 
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg: 
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句正确 3. 输出参数 
declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select count(*) from tableName' 
exec(@sqls) 
--如何将exec执行结果放入变量中？ declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select @a=count(*) from tableName ' 
exec sp_executesql @sqls,N'@a int output',@num output 
select @num 
3.如果你对于<2不能理解,就采用in (top 2)的方法.
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name , a.val

Answer 8

2005中 提供了PIVOT 和 UNPIVOT 行转列的运算符，有条件的话搜下看看也好。http://msdn.microsoft.com/zh-cn/library/ms177410.aspx

Answer 9

(select count(*) 
from tb
 where 姓名=b.姓名 and 分数<b.分数)
这里我不明白原理啊，表连接自己。但是 这段where条件里面的查询结果是个数值啊。怎么能影响上面的select呢？
有高手顺道解答下。谢谢，楼主看明白的话，也讲讲啊

Answer 10

--动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName 
Exec('select * from tableName') 
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg: 
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句正确 3. 输出参数 
declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select count(*) from tableName' 
exec(@sqls) 
--如何将exec执行结果放入变量中？ declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select @a=count(*) from tableName ' 
exec sp_executesql @sqls,N'@a int output',@num output 
select @num 

Answer 11

用你的第2中方法我就明白了，感觉比<的好理解多了。
select 姓名,课程,分数 
from tb a 
where 分数 in (select top 2 分数 from tb where 姓名=a.姓名) 
这样就能查到每个同学的前2条记录了。

Answer 12

我也只能理解那in的，用<的我也还是不明白。具体的看第14楼用in的解答方法吧。

Answer 13

1、静态SQL中，怎么有人用max，有人用sum，我试了一下，用min也可以，这是怎么回事呢？ 
---------------------------
用max,sum都可能,用min不行,会得到0
因每科都只有一条记录
case ...end中的结果等同于
对
姓名 语文,数学,物理
张三  74  0    0
张三  0   83   0
张三  0   0    93
李四  74  0    0
李四  0   84   0
李四  0   0    94
进行统计,所有sum()为74+0+0,max()为74,0,0中的最大值74,min为最小值 0
用min时得到的将全部为0

Answer 14

/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)： 
姓名 语文 数学 物理 
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分 
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb    ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)： 
姓名 课程 分数 
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns 
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb