行转列的部分语句看不懂

sqlsuper1 2017-08-23 03:32:37

if object_id('tb') IS NOT NULL

DROP TABLE tb

GO



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,指课程不止语文、数学、物理这三门课程。(以下同)

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) asAS

PRINT @sql

set @sql = @sql + ' from tb group by 姓名'

exec(@sql)

我在学习exec的用法时看到行转列的例子，但是有几行语法看不懂，大神帮解释一下。
1.select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'这句里的‘’‘+课程+’‘’是什么意思？
2['+课程+‘]是什么意思？这里的+号还有’‘’我都不明白是什么作用，还有书名号
3.为什么用MAX函数
4.set @sql = @sql + ' from tb group by 姓名'这句话也不明白是什么语法，是什么意思

...全文

177 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

吉普赛的歌版主 2017-08-23

打赏
举报

引用 10 楼 a448511789 的回复:

非常感谢，通过你说的min返回的是0,我真正理解了这几个聚合函数在这里的作用

嗯，没事就结贴吧。

sqlsuper1 2017-08-23

打赏
举报

引用 7 楼 yenange的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 1、这是一个动态拼接sql语句的过程，最后想要的结果楼主可以把exec(@sql)换成print @sql看看拼接完的语句是什么。 2、加上[]这个的意思是怕有数据库关键字造成拼接错误。 3、不一定要用max，其实就是一个聚合函数，配合group by来用，用min\SUM等都行 4、同1

3这个说法是有问题的， min 不行：

select 姓名  
, min(case 课程 when '数学' then 分数 else 0 end) [数学] 
, min(case 课程 when '物理' then 分数 else 0 end) [物理] 
, min(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	0	0	0
张三	0	0	0
*/

select 姓名  
, 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 [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

select 姓名  
, SUM(case 课程 when '数学' then 分数 else 0 end) [数学] 
, SUM(case 课程 when '物理' then 分数 else 0 end) [物理] 
, SUM(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

[/quote] 非常感谢，通过你说的min返回的是0,我真正理解了这几个聚合函数在这里的作用

吉普赛的歌版主 2017-08-23

打赏
举报

引用 8 楼 sinat_28984567 的回复:

[quote=引用 7 楼 yenange 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 1、这是一个动态拼接sql语句的过程，最后想要的结果楼主可以把exec(@sql)换成print @sql看看拼接完的语句是什么。 2、加上[]这个的意思是怕有数据库关键字造成拼接错误。 3、不一定要用max，其实就是一个聚合函数，配合group by来用，用min\SUM等都行 4、同1

3这个说法是有问题的， min 不行：

select 姓名  
, min(case 课程 when '数学' then 分数 else 0 end) [数学] 
, min(case 课程 when '物理' then 分数 else 0 end) [物理] 
, min(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	0	0	0
张三	0	0	0
*/

select 姓名  
, 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 [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

select 姓名  
, SUM(case 课程 when '数学' then 分数 else 0 end) [数学] 
, SUM(case 课程 when '物理' then 分数 else 0 end) [物理] 
, SUM(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

[/quote] 好吧，确实欠妥帖……额我的意思是min\sum\max都可以用。但是他仨case when的写法稍微有点改动，如果要是min，else后边就不能是0了，得改成100之类的了

select 姓名  
, min(case 课程 when '数学' then 分数 else 100 end) [数学] 
, min(case 课程 when '物理' then 分数 else 100 end) [物理] 
, min(case 课程 when '语文' then 分数 else 100 end) [语文] 
from tb group by [姓名]

当然按照楼主写的else后边是0，min确实不对……[/quote]

二月十六版主 2017-08-23

打赏
举报

引用 7 楼 yenange 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 1、这是一个动态拼接sql语句的过程，最后想要的结果楼主可以把exec(@sql)换成print @sql看看拼接完的语句是什么。 2、加上[]这个的意思是怕有数据库关键字造成拼接错误。 3、不一定要用max，其实就是一个聚合函数，配合group by来用，用min\SUM等都行 4、同1

3这个说法是有问题的， min 不行：

select 姓名  
, min(case 课程 when '数学' then 分数 else 0 end) [数学] 
, min(case 课程 when '物理' then 分数 else 0 end) [物理] 
, min(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	0	0	0
张三	0	0	0
*/

select 姓名  
, 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 [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

select 姓名  
, SUM(case 课程 when '数学' then 分数 else 0 end) [数学] 
, SUM(case 课程 when '物理' then 分数 else 0 end) [物理] 
, SUM(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

select 姓名  
, min(case 课程 when '数学' then 分数 else 100 end) [数学] 
, min(case 课程 when '物理' then 分数 else 100 end) [物理] 
, min(case 课程 when '语文' then 分数 else 100 end) [语文] 
from tb group by [姓名]

当然按照楼主写的else后边是0，min确实不对……

吉普赛的歌版主 2017-08-23

打赏
举报

引用 1 楼 sinat_28984567 的回复:

1、这是一个动态拼接sql语句的过程，最后想要的结果楼主可以把exec(@sql)换成print @sql看看拼接完的语句是什么。 2、加上[]这个的意思是怕有数据库关键字造成拼接错误。 3、不一定要用max，其实就是一个聚合函数，配合group by来用，用min\SUM等都行 4、同1

3这个说法是有问题的， min 不行：

select 姓名  
, min(case 课程 when '数学' then 分数 else 0 end) [数学] 
, min(case 课程 when '物理' then 分数 else 0 end) [物理] 
, min(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	0	0	0
张三	0	0	0
*/

select 姓名  
, 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 [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

select 姓名  
, SUM(case 课程 when '数学' then 分数 else 0 end) [数学] 
, SUM(case 课程 when '物理' then 分数 else 0 end) [物理] 
, SUM(case 课程 when '语文' then 分数 else 0 end) [语文] 
from tb group by [姓名]
/*
姓名	数学	物理	语文
李四	84	94	74
张三	83	93	74
*/

二月十六版主 2017-08-23

打赏
举报

引用 5 楼 a448511789 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 1、这是一个动态拼接sql语句的过程，最后想要的结果楼主可以把exec(@sql)换成print @sql看看拼接完的语句是什么。 2、加上[]这个的意思是怕有数据库关键字造成拼接错误。 3、不一定要用max，其实就是一个聚合函数，配合group by来用，用min\SUM等都行 4、同1

意思就是说虽然max,min,sum的作用完全不同，最大值，最小值，求和，，但是因为case when所得出的结果都是同一个课程名，所以用什么聚合函数，结果还是那个课程名，所以只要是聚合函数就可以，因为有group by存在，所以select后跟的必须是聚合函数，要不然会语法错误，我这样理解对吗？[/quote] 对

sqlsuper1 2017-08-23

打赏
举报

引用 1 楼 sinat_28984567 的回复:

1、这是一个动态拼接sql语句的过程，最后想要的结果楼主可以把exec(@sql)换成print @sql看看拼接完的语句是什么。 2、加上[]这个的意思是怕有数据库关键字造成拼接错误。 3、不一定要用max，其实就是一个聚合函数，配合group by来用，用min\SUM等都行 4、同1

意思就是说虽然max,min,sum的作用完全不同，最大值，最小值，求和，，但是因为case when所得出的结果都是同一个课程名，所以用什么聚合函数，结果还是那个课程名，所以只要是聚合函数就可以，因为有group by存在，所以select后跟的必须是聚合函数，要不然会语法错误，我这样理解对吗？

sqlsuper1 2017-08-23

打赏
举报

引用 2 楼 yenange 的回复:

其实你应该关注的是 print 之后的sql , 这样你就能看明白了：

select 姓名  
, 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 [姓名]

你列出这样的语句，我就完全明白了，要不然我完全看不懂

吉普赛的歌版主 2017-08-23

打赏
举报

一般来说，表名、列名等不应该用中文、关键字或其它怪异字符，一定要用最好是用 [] 括起来。当然，不管是3721，全部括起来是最保险了，只是比较麻烦一点。

吉普赛的歌版主 2017-08-23

打赏
举报

其实你应该关注的是 print 之后的sql , 这样你就能看明白了：

select 姓名  
, 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 [姓名]

二月十六版主 2017-08-23