从【各大软件公司笔试压轴题】学习SQL语句

从博客园中看到一篇文章，介绍大软件公司面试时常常会出的两道SQL题（见附录）。

我觉得受益很多，在此之前，我一直觉得，SQL2008似乎提供了这方面的支持，但更低的版本，包括2005，非游标做不出来（水平够菜）。总结心得如下：

1、    强大的group by

在这里，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.表名’)的方式获得字段信息。

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

附录：

http://www.cnblogs.com/zhanglei644213943/archive/2009/12/27/1633356.html

      纵览各大 区、论坛，各大 ORM框架火得不行了，如NHibernate、LINQ to SQL、ADO.NET Entity framework等，还有最近市场上出版的一本叫《领域驱动设计与模式实战》，里面也凸显了不少NHibernate在领域驱动设计中的作用与地位，也算是第一本与NHibernate相关的书籍吧！不过就NHibernate而言还是没有官方文档介绍得详细呵呵，园子里Kiler 已经把他翻译成中文版的了，收益一大片仅仅是CET-4的人。不管你是用NHibernate也好，还是用LINQ to SQL也好，用profiler一跟踪，执行的都是SQL语句，所以所SQL是根。特别是对于那些以数据为中心的应用系统，在数据库中实现复杂的存储过程，复杂的 表查询，还是直接SQL来得痛快。当然 对于那些在基于.NET的中间层应用中，它们实现面向对象的业务模型和商业逻辑的应用，NHibernate是最有用的。不管怎样，NHibernate一定可以帮助你消除或者包装那些针对特定厂商的SQL代码，并且帮你把结果集从表格式的表示形式转换到一系列的对象去（官方文档）。

      有点跑题了，不再啰嗦—-直接晾出压轴题。

压轴题第一问

1.把表一转换为表二

表一：                                                                     

表二：

数据库代码如下：

首先大家会想到分两组

1  select stdname，····，from #student group by stdname
然后······中间该写什么呢/p>

  代码
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 [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,也是慢慢重构–调试–重构-······

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树SQL高级技巧CTE和递归查询31830 人正在系统学习中