一个sql中常遇到的表结构转换问题

score表结构如

name  subject  score
-----------------------------------
大都    语文     59
大都    数学     78
大都    外语     89
大都    物理     98
小都    语文     90
小都    外语     80
小都    物理     70
德国    语文     90
德国    数学     50
德国    外语     80
德国    物理     89
哈哈    语文     99
哈哈    数学     80
哈哈    物理     89

现需转换成表结构如：

姓名 数学 外语 物理 语文
-----------------------------------------
大都   78    89     98     59
德国   50    80     89     90
哈哈   80    0       89     99
小都   0      80     70     90


解决之道：

--当出现的subject的个数不定的时候

declare @sql varchar(8000)
set @sql='SELECT name as 姓名'
select @sql=@sql+',['+subject+']=MAX(CASE subject WHEN '''+subject+''' THEN score ELSE 0 END)' from score group by subject
exec(@sql+' FROM score GROUP BY name')

--当出现的subject的个数确定的时候

select name as 姓名,
数学=MAX(CASE WHEN subject='数学' THEN score ELSE 0 END),
外语=MAX(CASE WHEN subject='外语' THEN score ELSE 0 END),
物理=MAX(CASE WHEN subject='物理' THEN score ELSE 0 END),
语文=MAX(CASE WHEN subject='语文' THEN score ELSE 0 END)
from score
group by name

 

原来的静态方法:

比如有表 t1

Id subj mark

1 Chinese 89

1 Maths 98

2 Chinese 100

2 Maths 78

2 English 100

3 Chinese 99

转换为

id,Chinese,Maths,English 的格式

可以是

(sql#1)

Select id,max(Chinese) as Chinese,max(Maths) as Maths,max(English) as English

From

(

Select id

,case when(subj =’Chinese’) then mark  else 0 end as [Chinese]

,case when(subj =’Maths’) then mark  else 0 end as [Maths]’

,case when(subj =’English’) then mark  else 0 end as [English]

 

From t1) as t2

 

倘若里面有一百个科目呢?你也要写100个case吗?

这里有个动态的方法,在介绍之前,

先把上面的写法换一个形式

 

(sql#2)

Select id

,Chinese = max(case when subj =’Chinese’ then mark else 0 end)

,Maths = max(case when subj =’Maths’ then mark else 0 end)

,English = max( case when subj =’English’ then mark else 0 end)

From t1

Group by id

与前面的区别在于,前面是先构造了下面的表

Id Chinese Maths English

1 89        0     0

1 0        98     0

2 100       0     0

2 0         78    0

2 0          0    100

3 99

然后再按id进行group by,取得各列的最大值.

 

这里呢,其实还是这样的,只是这个过程被迷惑了(根本没有取消这个过程)

由于max和group 是最后进行的,所以

上面的sql其实就是先统计了如上的一个表

之后再group by ,在max

 

下面进入正题,动态的生成上面的语句

declare  @sql  varchar( 8000 )
set  @sql = 'select [id], '

-- 语句的构造由一个 select 语句完成 , 即按照
select  @sql = @sql + quotename (subj) + '=max(case when style=' + quotename (subj,  '''' ) + ' then rtrim(amount) else ''-'' end),'  from  t1 group  by subj
-- 除去末尾的那个逗号 .

select  @sql = left ( @sql ,  len ( @sql ) - 1 ),  @sql = @sql + ' from t1group by [id]'
exec ( @sql )

 

说明 :quotename( 字符串 ) 把一个 unicode 字符变为 sql server 的标识符 , 这里是列名 .

       quotename( 字符串 ,’’’’) 是把列名显示为 varchar 的形式以便于比较 .

 

同时也看到 select 与 set 在赋值方面是差别很大的 . 以前只是认为 set 只能一次赋值一个 , 而 select 可以给多个

现在呢 , 应该是  set 只能赋值一次 , 而 select 可以多次 .

 

从数学上看 ,

Sql#1 和 sql#2 的差别在于

前者相当于

F(g(x1),g(x2),g(x3))

后者相当于

F(g(x1,x2,x3))

其中 g(x) 相当于 case 操作 , 即列转换操作

F(x) 相当于求最值操作 .

 

那么下面的题目就容易理解了

create  table  T( 名字  varchar( 10 ),  科目  varchar( 10 ),  分数  int)
insert  T  select  '1' ,  ' 语文 ' ,  90
union  all  select  '1' ,  ' 数学 ' ,  80
union  all  select  '2' ,  ' 语文 ' ,  90
union  all  select  '2' ,  ' 数学 ' ,  85
union  all  select  '2' ,  ' 外语 ' ,  70

declare  @sql  varchar( 8000 )
declare  @sql2  varchar( 1000 )
set  @sql = ''
set  @sql2 = ' 總分 ='
select  @sql = @sql + quotename ( 科目 ) + '=isnull( max(case when  科目 =' + quotename ( 科目 ,  '''' ) + ' then  分数  end), 100),' ,
@sql2 = @sql2 + 'tmp.' + 科目 + '+'
from  T
group  by  科目
select
@sql2 = left ( @sql2 ,  len ( @sql2 ) - 1 ),
@sql = left ( @sql ,  len ( @sql ) - 1 ),
@sql = 'select tmp.*,' + @sql2 + ' from (select  名字 ,' + @sql ,
@sql = @sql + ' from T group by  名字 ) tmp'
exec ( @sql )