从【各大软件公司笔试压轴题】学习SQL语句-优快云博客

本文介绍了如何使用SQL将成绩数据从列表格式转换为表格格式及逆向操作的方法。通过group by和case语句组合，实现成绩汇总；利用动态SQL生成技术处理多科目情况，提升效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

1、强大的group by

1 selectstdname,
2 isnull(sum( case stdsubjectwhen ' 化学 ' thenResultend), 0 )[化学],
3 isnull(sum( case stdsubjectwhen ' 数学 ' thenResultend), 0 )[数学],
4 isnull(sum( case stdsubjectwhen ' 物理 ' thenResultend), 0 )[物理],
5 isnull(sum( case stdsubjectwhen ' 语文 ' thenResultend), 0 )[语文]
6 from#student
7 groupbystdname

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

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

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

1 declare@sqlvarchar( 4000 )
2 set @sql = ' selectstdname '
3 select@sql = @sql + ' ,isnull(sum(casestdsubjectwhen ''' + stdsubject + ''' thenResultend),0)[ ' + stdsubject + ' ] '
4 from(selectdistinctstdsubjectfrom#student) as a
5 select@sql = @sql + ' from#studentgroupbystdname '
6 print@sql
7 exec(@sql)

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

4 from(selectdistinctstdsubjectfrom#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 DROPtable#student
2 CREATETABLE#student(stdnamenvarchar( 10 ),stdsubjectnvarchar( 10 ),result int )
3 INSERTINTO#studentVALUES( ' 张三 ' , ' 语文 ' , 80 )
4 INSERTINTO#studentvalues( ' 张三 ' , ' 数学 ' , 90 )
5 INSERTINTO#studentVALUES( ' 张三 ' , ' 物理 ' , 85 )
6 INSERTINTO#studentVALUES( ' 李四 ' , ' 语文 ' , 85 )
7 INSERTINTO#studentvalues( ' 李四 ' , ' 数学 ' , 92 )
8 INSERTINTO#studentVALUES( ' 李四 ' , ' 物理 ' , 82 )
9 INSERTINTO#studentVALUES( ' 李四 ' , ' 化学 ' , 82 )
10 INSERTINTO#studentVALUES( ' 李四 ' , ' 化学 ' , 82 )
11 SELECT * FROM#student

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

首先大家会想到分两组

1 selectstdname，····，from#studentgroupbystdname

然后······中间该写什么呢？

代码

1 case stdsubjectwhen ' 化学 ' thenResultend
2 case stdsubjectwhen ' 语文 ' thenResultend
3 case stdsubjectwhen ' ··· ' thenResultend
4 case stdsubjectwhen ' ··· ' thenResultend
5 case stdsubjectwhen ' ··· ' thenResultend

表二里面得0是哪里来的呢？

代码

1 isnull(sum（ case stdsubjectwhen ' 化学 ' thenResultend）, 0 )
2 isnull(sum( case stdsubjectwhen ' 语文 ' thenResultend), 0 )
3 isnull(sum( case stdsubjectwhen ' ··· ' thenResultend), 0 )
4 isnull(sum( case stdsubjectwhen ' ··· ' thenResultend), 0 )
5 isnull(sum( case stdsubjectwhen ' ··· ' thenResultend), 0 )

所以得出：

代码

然后得出答案：

代码

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

表一：

表二：

数据库代码如下：

代码

1 DROPtable#student2
2 CREATETABLE#student2(stdnamenvarchar( 10 ),化学 int ,数学 int ,物理 int ,语文 int )
3 INSERTINTO#student2VALUES( ' 李四 ' , 164 , 92 , 82 , 85 )
4 INSERTINTO#student2VALUES( ' 张三 ' , 0 , 90 , 85 , 80 )
5 SELECT * FROM#student2

看到这题，直接想到：

代码

1 SELECT ' 李四 ' as stdname,stdname = ' 化学 ' ,化学 as resultfrom#student2 where stdname = ' 李四 '
2 unionall
3 SELECT ' 李四 ' as stdname,stdname = ' 数学 ' ,数学 as resultfrom#student2 where stdname = ' 李四 '
4 unionall
5 SELECT ' 李四 ' as stdname,stdname = ' 物理 ' ,物理 as resultfrom#student2 where stdname = ' 李四 '
6 unionall
7 SELECT ' 李四 ' as stdname,stdname = ' 语文 ' ,语文 as resultfrom#student2 where stdname = ' 李四 '

8 unionall
9 SELECT ' 张三 ' as stdname,stdname = ' 化学 ' ,化学 as resultfrom#student2 where stdname = ' 张三 '
10 unionall
11 SELECT ' 张三 ' as stdname,stdname = ' 数学 ' ,数学 as resultfrom#student2 where stdname = ' 张三 '
12 unionall
13 SELECT ' 张三 ' as stdname,stdname = ' 物理 ' ,物理 as resultfrom#student2 where stdname = ' 张三 '
14 unionall
15 SELECT ' 张三 ' as stdname,stdname = ' 语文 ' ,语文 as resultfrom#student2 where stdname = ' 张三 '

重构到：

代码

1 declare@sql2varchar( 4000 )
2 set @sql2 = ''
3 SELECT@sql2 = @sql2 +
4 ' SELECT ''' + stdname + ''' asstdname,stdname= '' 化学 '' ,化学asresultfrom#student2wherestdname= ''' + stdname + '''
5 unionall
6 SELECT ''' +stdname+ ''' as stdname,stdname = '' 数学 '' ,数学 as resultfrom#student2 where stdname = ''' +stdname+ '''
7 unionall
8 SELECT ''' +stdname+ ''' as stdname,stdname = '' 物理 '' ,物理 as resultfrom#student2 where stdname = ''' +stdname+ '''
9 unionall
10 SELECT ''' +stdname+ ''' as stdname,stdname = '' 语文 '' ,语文 as resultfrom#student2 where stdname = ''' +stdname+ ''' unionall '
11 from(SELECTstdnameFROM#student2) as a
12 SELECT@sql2 = LEFT(@sql2,LEN(@sql2) - 10 )
13 PRINT(@sql2)
14 exec(@sql2)

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

代码

1 select[name]into#tmpCloumns
2 fromtempdb.dbo.syscolumns
3 where id = object_id( ' tempdb.dbo.#student2 ' )
4 and[name] <> ' stdname '
5 select * from#tmpCloumns
6
7 declare@strSqlnvarchar( 800 )
8 select@strSql = ''
9 select@strSql = @strSql + ' unionall ' + 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)) + ' orderbystdname,[科目] '
15 -- print@strSql
16 exec(@strsql)

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