最近,在博客园看到一篇文章,又教会了我不少的知识. 传送门:http://www.cnblogs.com/zhanglei644213943/archive/2009/12/27/1633356.html
只所以要收藏这篇文章,并不是做不出来.. 相反. SQL的思路和步骤和文章那一样有条理性,有步骤性.
而让我感受颇深的是最后一步的简化. 利用声明 参数 与 distinct from 结合 而生成的一个SQL 语句. 可以大大的简化了对SQL 列名的依赖性. 简单来说,就是重用性.
数据库代码如下:
代码
1
DROP table #student
2 CREATE TABLE #student (stdname nvarchar( 10 ),stdsubject nvarchar( 10 ),result int )
3 INSERT INTO #student VALUES ( ' 张三 ' , ' 语文 ' , 80 )
4 INSERT INTO #student values ( ' 张三 ' , ' 数学 ' , 90 )
5 INSERT INTO #student VALUES ( ' 张三 ' , ' 物理 ' , 85 )
6 INSERT INTO #student VALUES ( ' 李四 ' , ' 语文 ' , 85 )
7 INSERT INTO #student values ( ' 李四 ' , ' 数学 ' , 92 )
8 INSERT INTO #student VALUES ( ' 李四 ' , ' 物理 ' , 82 )
9 INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
10 INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
11 SELECT * FROM #student
2 CREATE TABLE #student (stdname nvarchar( 10 ),stdsubject nvarchar( 10 ),result int )
3 INSERT INTO #student VALUES ( ' 张三 ' , ' 语文 ' , 80 )
4 INSERT INTO #student values ( ' 张三 ' , ' 数学 ' , 90 )
5 INSERT INTO #student VALUES ( ' 张三 ' , ' 物理 ' , 85 )
6 INSERT INTO #student VALUES ( ' 李四 ' , ' 语文 ' , 85 )
7 INSERT INTO #student values ( ' 李四 ' , ' 数学 ' , 92 )
8 INSERT INTO #student VALUES ( ' 李四 ' , ' 物理 ' , 82 )
9 INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
10 INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
11 SELECT * FROM #student
可能很多老手们,一看到这题目就有了答案。当然,贴出答案来不是我的目的,我要带着SQL新手们重构到答案。用李建忠老师最爱说的话就是------我不建议一上来就套用模式,而应该从重构到模式。
首先大家会想到分两组
1 select stdname,····,from #student group by stdname
然后······中间该写什么呢?


1
case
stdsubject when
'
化学
'
then Result end
2 case stdsubject when ' 语文 ' then Result end
3 case stdsubject when ' ··· ' then Result end
4 case stdsubject when ' ··· ' then Result end
5 case stdsubject when ' ··· ' then Result end
2 case stdsubject when ' 语文 ' then Result end
3 case stdsubject when ' ··· ' then Result end
4 case stdsubject when ' ··· ' then Result end
5 case stdsubject when ' ··· ' then Result end
表二里面得0是哪里来的呢?
代码
1
isnull(sum(
case
stdsubject when
'
化学
'
then Result end),
0
)
2 isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 )
3 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
4 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
5 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
2 isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 )
3 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
4 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
5 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
所以得出:
代码
1
select stdname,
2 isnull(sum( case stdsubject when ' 化学 ' then Result end), 0 ) [化学],
3 isnull(sum( case stdsubject when ' 数学 ' then Result end), 0 ) [数学],
4 isnull(sum( case stdsubject when ' 物理 ' then Result end), 0 ) [物理],
5 isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 ) [语文]
6 from #student
7 group by stdname
2 isnull(sum( case stdsubject when ' 化学 ' then Result end), 0 ) [化学],
3 isnull(sum( case stdsubject when ' 数学 ' then Result end), 0 ) [数学],
4 isnull(sum( case stdsubject when ' 物理 ' then Result end), 0 ) [物理],
5 isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 ) [语文]
6 from #student
7 group by stdname
然后得出答案:
代码
1
declare @sql varchar(
4000
)
2 set @sql = ' select stdname '
3 select @sql = @sql + ' ,isnull(sum(case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] '
4 from (select distinct stdsubject from #student) as a
5 select @sql = @sql + ' from #student group by stdname '
6 print @sql
7 exec(@sql)
8
2 set @sql = ' select stdname '
3 select @sql = @sql + ' ,isnull(sum(case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] '
4 from (select distinct stdsubject from #student) as a
5 select @sql = @sql + ' from #student group by stdname '
6 print @sql
7 exec(@sql)
8