group的魅力

1、    强大的group by

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


在这里,group by与sum + case结合,可以将表1中的记录(行)变成表2的字段(列)。Sum里面如果没有case,那么出来的值,只能是全部科目的总和,用了case以后,就是某科的成绩;然后这里用了好几个sum,每个科目一个sum,于是表1中本来某人某科占一条记录的“行”就变成了表2里某人一条记录,每科做一个字段了。


这种心思巧妙和对语法的熟练运用让人击节赞叹。


2、    利用select from (select from)的模式生成SQL语句

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)


为了自动写上所有的科目,这里先将科目信息提炼出来:

4  from (select distinct stdsubject from #student)  as  a

利用之拼接生成SQL语句。当然现实中,如果#student表很大,这种做法并不妥,应该都有一个专门的科目类别表的。


3、    在临时库中提炼出字段名。临时表是真实存在的表,保存在[tempdb]中,可以利用object_id('tempdb.dbo.表名')的方式获得字段信息。

============================================

 

压轴题第一问

1.把表一转换为表二

表一:                                                                     

表二:

数据库代码如下:

代码
 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

 可能很多老手们,一看到这题目就有了答案。当然,贴出答案来不是我的目的,我要带着SQL新手们重构到答案。用MVP李建忠老师最爱说的话就是------我不建议一上来就套用模式,而应该从重构到模式。

首先大家会想到分两组

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

表二里面得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 )

所以得出:

代码
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

然后得出答案:

代码
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  

压轴题第二问:把表二转化为表一

表一:

表二:

数据库代码如下:

代码
1   DROP table #student2
2   CREATE TABLE #student2 (stdname nvarchar( 10 ),化学  int ,数学  int ,物理  int  ,语文  int  )
3   INSERT INTO #student2 VALUES ( ' 李四 ' , 164 , 92 , 82 , 85 )
4   INSERT INTO #student2 VALUES ( ' 张三 ' , 0 , 90 , 85 , 80 )
5   SELECT  *  FROM #student2 

看到这题,直接想到:

代码
 1   SELECT ' 李四 ' as  stdname,stdname = ' 化学 ' , 化学  as  result from #student2  where  stdname = ' 李四 '
 2   union all
 3   SELECT ' 李四 ' as  stdname,stdname = ' 数学 ' , 数学  as  result from #student2  where  stdname = ' 李四 '
 4   union all
 5   SELECT ' 李四 ' as  stdname,stdname = ' 物理 ' , 物理  as  result from #student2  where  stdname = ' 李四 '
 6   union all
 7   SELECT ' 李四 ' as  stdname,stdname = ' 语文 ' , 语文  as  result from #student2  where  stdname = ' 李四 '  
  8  union all 
 9   SELECT ' 张三 ' as  stdname,stdname = ' 化学 ' , 化学  as  result from #student2  where  stdname = ' 张三 '
10   union all
11   SELECT ' 张三 ' as  stdname,stdname = ' 数学 ' , 数学  as  result from #student2  where  stdname = ' 张三 '
12   union all
13   SELECT ' 张三 ' as  stdname,stdname = ' 物理 ' , 物理  as  result from #student2  where  stdname = ' 张三 '
14   union all
15   SELECT ' 张三 ' as  stdname,stdname = ' 语文 ' , 语文  as  result from #student2  where  stdname = ' 张三 '

 重构到:

代码
 1   declare @sql2 varchar( 4000 )
 2     set  @sql2  =   ''  
 3   SELECT @sql2 = @sql2 +  
 4   ' SELECT ''' + stdname + ''' as stdname,stdname= '' 化学 '' , 化学 as result from #student2 where stdname= ''' + stdname + '''
 5   union all
 6   SELECT ''' +stdname+ ''' as  stdname,stdname = '' 数学 '' , 数学  as  result from #student2  where  stdname = ''' +stdname+ '''
 7   union all
 8   SELECT ''' +stdname+ ''' as  stdname,stdname = '' 物理 '' , 物理  as  result from #student2  where  stdname = ''' +stdname+ '''
 9   union all
10   SELECT ''' +stdname+ ''' as  stdname,stdname = '' 语文 '' , 语文  as  result from #student2  where  stdname = ''' +stdname+ '''  union all  '
11   from (SELECT stdname FROM #student2)  as  a
12   SELECT @sql2  =  LEFT(@sql2,LEN(@sql2)  -   10 )
13   PRINT(@sql2)
14   exec(@sql2)

如果要求不能出现  化学  数学  物理 语文 这样的关键字,那么可以这样写:

代码
 1   select [name] into #tmpCloumns
 2   from tempdb.dbo.syscolumns
 3   where  id = object_id( ' tempdb.dbo.#student2 ' )
 4   and [name] <> ' stdname '
 5   select  *   from #tmpCloumns
 6  
 7   declare @strSql nvarchar( 800 )
 8   select @strSql = ''
 9   select @strSql = @strSql + ' union all ' + char ( 10 ) + char ( 13 ) +
10                    ' select [stdname], ''' + [name] + '''  as [科目],[ ' + [name] + ' ] ' + char ( 10 ) + char ( 13 ) +
11                    ' from [#student2] ' + char ( 10 ) + char ( 13 )
12   from #tmpCloumns
13  
14   select @strSql = substring(@strSql, 11 ,len(@strSql)) + ' order by stdname,[科目] '
15   -- print @strSql
16   exec(@strsql) 

 这种题目,在各种笔试中出现的概率还是非常大的,大家不用死记。以前有的朋友看着复杂的报表查询,几百行SQL,望而生畏,然后说:"这是哪个SQL超人写的啊!"其实,谁一上来不可能写出那么长的SQL,也是慢慢重构--调试--重构-······

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值