这条sql是怎么工作的?

[liangCK]

CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[score] [numeric](18, 0) NULL ,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into test([name],[subject],[score]) values('张三','语文',60)
insert into test([name],[subject],[score]) values('张三','数学',76)
insert into test([name],[subject],[score]) values('张三','英语',80)

insert into test([name],[subject],[score]) values('李四','语文',90)
insert into test([name],[subject],[score]) values('李四','数学',88)
insert into test([name],[subject],[score]) values('李四','英语',50)


declare @sql nvarchar(4000)
select @sql='select name,'
select @sql=@sql+'sum(case subject when '''+subject+'''
then score else 0 end) as '''+subject+''','
from (select distinct subject from test) as a

print @sql

为什么这条语句..能够输出
select name,sum(case subject when '数学'
then score else 0 end) as '数学',sum(case subject when '英语'
then score else 0 end) as '英语',sum(case subject when '语文'
then score else 0 end) as '语文',

这样?它是怎么工作的?

[mustudent]

學習 我也不明白 很漂亮的東西

[fengming2222]

楼主是什么意思？

[liangCK]

就是当我
declare @sql nvarchar(4000)
select @sql='select name,'
select @sql=@sql+'sum(case subject when '''+subject+'''
then score else 0 end) as '''+subject+''','
from (select distinct subject from test) as a

print @sql
输入@sql的时候.好像是实现了记录的循环.但又不知道是如何工作的.

[lost_queen]

哈哈
就是一个存储过程，它把句子存在一个文件里了
最后print @sql
就输出了，没有什么问题啊

[sunhonglei2004]

CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[score] [numeric](18, 0) NULL ,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
------------建立表
insert into test([name],[subject],[score]) values('张三','语文',60)
insert into test([name],[subject],[score]) values('张三','数学',76)
insert into test([name],[subject],[score]) values('张三','英语',80)

insert into test([name],[subject],[score]) values('李四','语文',90)
insert into test([name],[subject],[score]) values('李四','数学',88)
insert into test([name],[subject],[score]) values('李四','英语',50)
-----------插入六条记录
declare @sql nvarchar(4000) ---定义变量@sql
select @sql='select name,' ---------给@sal赋值
select @sql=@sql+'sum(case subject when '''+subject+'''
then score else 0 end) as '''+subject+''',' --再次给@sql赋值

from (select distinct subject from test) as a 取出test表中subject中不重复的记录

[lost_queen]

select @sql=@sql+'sum(case subject when '''+subject+'''
～～～～～～看这里

用表a里的subject字段里的值替换我划线的部分
对每个字段都作一次，不是就有3句了吗？
这下明白了吗？

[sunhonglei2004]

case subject when '''+subject+'''
then score else 0 end) as '''+subject+''','
-----subject有一条不重复的记录,有循环显示一次,所以是"数学,语言,英语"三条记录

[lost_queen]

楼上说的对