从各大软件公司笔试压轴题学习SQL语句_求职面试_非技术区_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 非技术区 > 求职面试 > 从各大软件公司笔试压轴题学习SQL语句

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

 2010/11/3 11:59:40    天极网软件频道  我要评论(0)
  • 摘要:从博客园中看到一篇文章,介绍大软件公司面试时常常会出的两道SQL题(见附录)。我觉得受益很多,在此之前,我一直觉得,SQL2008似乎提供了这方面的支持,但更低的版本,包括2005,非游标做不出来(水平够菜)。总结心得如下:1、强大的groupby1selectstdname,2isnull(sum(casestdsubjectwhen'化学'thenResultend),0)[化学],3isnull(sum(casestdsubjectwhen'数学'thenResultend),0
  • 标签:软件公司 笔试 SQL语句

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

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

  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.表名')的方式获得字段信息。

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

  附录:

  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 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,也是慢慢重构--调试--重构

发表评论
用户名: 匿名