典型SQL题目

从博客园中看到一篇文章&＃xff0c;介绍大软件公司面试时常常会出的两道SQL题&＃xff08;见附录&＃xff09;。

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

1、    强大的group by

1  select stdname,
2  isnull(sum( case  stdsubject when  &＃39; 化学 &＃39;  then Result end), 0 ) [化学],
3  isnull(sum( case  stdsubject when  &＃39; 数学 &＃39;  then Result end), 0 ) [数学],
4  isnull(sum( case  stdsubject when  &＃39; 物理 &＃39;  then Result end), 0 ) [物理],
5  isnull(sum( case  stdsubject when  &＃39; 语文 &＃39;  then Result end), 0 ) [语文]
6  from #student
7  group by stdname

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

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

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

1  declare &＃64;sql varchar( 4000 )
2  set  &＃64;sql  &＃61;   &＃39; select stdname &＃39; 
3  select &＃64;sql  &＃61;  &＃64;sql  &＃43;   &＃39; ,isnull(sum(case stdsubject when  &＃39;&＃39;&＃39; &＃43; stdsubject &＃43; &＃39;&＃39;&＃39;  then Result end),0) [ &＃39; &＃43; stdsubject &＃43; &＃39; ] &＃39; 
4  from (select distinct stdsubject from #student)  as  a
5  select &＃64;sql  &＃61;  &＃64;sql &＃43; &＃39;  from #student group by stdname &＃39; 
6  print &＃64;sql
7  exec(&＃64;sql)

为了自动写上所有的科目&＃xff0c;这里先将科目信息提炼出来&＃xff1a;

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

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

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

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

附录&＃xff1a;

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

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

      有点跑题了&＃xff0c;不再啰嗦----直接晾出压轴题。

压轴题第一问

1.把表一转换为表二

表一&＃xff1a;                                                                     

表二&＃xff1a;

数据库代码如下&＃xff1a;

  代码
 1  DROP table #student
 2  CREATE TABLE #student (stdname nvarchar( 10 ),stdsubject nvarchar( 10 ),result  int )
 3  INSERT INTO #student VALUES ( &＃39; 张三 &＃39; , &＃39; 语文 &＃39; , 80 )
 4  INSERT INTO #student values ( &＃39; 张三 &＃39; , &＃39; 数学 &＃39; , 90 )
 5  INSERT INTO #student VALUES ( &＃39; 张三 &＃39; , &＃39; 物理 &＃39; , 85 )
 6  INSERT INTO #student VALUES ( &＃39; 李四 &＃39; , &＃39; 语文 &＃39; , 85 )
 7  INSERT INTO #student values ( &＃39; 李四 &＃39; , &＃39; 数学 &＃39; , 92 )
 8  INSERT INTO #student VALUES ( &＃39; 李四 &＃39; , &＃39; 物理 &＃39; , 82 )
 9  INSERT INTO #student VALUES ( &＃39; 李四 &＃39; , &＃39; 化学 &＃39; , 82 )
10  INSERT INTO #student VALUES ( &＃39; 李四 &＃39; , &＃39; 化学 &＃39; , 82 )
11  SELECT  *  FROM #student
 可能很多老手们&＃xff0c;一看到这题目就有了答案。当然&＃xff0c;贴出答案来不是我的目的&＃xff0c;我要带着SQL新手们重构到答案。用MVP李建忠老师最爱说的话就是------我不建议一上来就套用模式&＃xff0c;而应该从重构到模式。

首先大家会想到分两组

1  select stdname&＃xff0c;····&＃xff0c;from #student group by stdname
然后······中间该写什么呢&＃xff1f;

  代码
1  case  stdsubject when  &＃39; 化学 &＃39;  then Result end
2  case  stdsubject when  &＃39; 语文 &＃39;  then Result end
3  case  stdsubject when  &＃39; ··· &＃39;  then Result end
4  case  stdsubject when  &＃39; ··· &＃39;  then Result end
5  case  stdsubject when  &＃39; ··· &＃39;  then Result end
表二里面得0是哪里来的呢&＃xff1f;

  代码
1  isnull(sum&＃xff08; case  stdsubject when  &＃39; 化学 &＃39;  then Result end&＃xff09;, 0 )
2  isnull(sum( case  stdsubject when  &＃39; 语文 &＃39;  then Result end), 0 )
3  isnull(sum( case  stdsubject when  &＃39; ··· &＃39;  then Result end), 0 )
4  isnull(sum( case  stdsubject when  &＃39; ··· &＃39;  then Result end), 0 )
5  isnull(sum( case  stdsubject when  &＃39; ··· &＃39;  then Result end), 0 )
所以得出&＃xff1a;

  代码
1  select stdname,
2  isnull(sum( case  stdsubject when  &＃39; 化学 &＃39;  then Result end), 0 ) [化学],
3  isnull(sum( case  stdsubject when  &＃39; 数学 &＃39;  then Result end), 0 ) [数学],
4  isnull(sum( case  stdsubject when  &＃39; 物理 &＃39;  then Result end), 0 ) [物理],
5  isnull(sum( case  stdsubject when  &＃39; 语文 &＃39;  then Result end), 0 ) [语文]
6  from #student
7  group by stdname
然后得出答案&＃xff1a;

  代码
1  declare &＃64;sql varchar( 4000 )
2  set  &＃64;sql  &＃61;   &＃39; select stdname &＃39; 
3  select &＃64;sql  &＃61;  &＃64;sql  &＃43;   &＃39; ,isnull(sum(case stdsubject when  &＃39;&＃39;&＃39; &＃43; stdsubject &＃43; &＃39;&＃39;&＃39;  then Result end),0) [ &＃39; &＃43; stdsubject &＃43; &＃39; ] &＃39; 
4  from (select distinct stdsubject from #student)  as  a
5  select &＃64;sql  &＃61;  &＃64;sql &＃43; &＃39;  from #student group by stdname &＃39; 
6  print &＃64;sql
7  exec(&＃64;sql)
8 
压轴题第二问&＃xff1a;把表二转化为表一

表一&＃xff1a;

表二&＃xff1a;

数据库代码如下&＃xff1a;

  代码
1  DROP table #student2
2  CREATE TABLE #student2 (stdname nvarchar( 10 ),化学  int ,数学  int ,物理  int  ,语文  int  )
3  INSERT INTO #student2 VALUES ( &＃39; 李四 &＃39; , 164 , 92 , 82 , 85 )
4  INSERT INTO #student2 VALUES ( &＃39; 张三 &＃39; , 0 , 90 , 85 , 80 )
5  SELECT  *  FROM #student2 
看到这题&＃xff0c;直接想到&＃xff1a;

  代码
 1  SELECT &＃39; 李四 &＃39; as  stdname,stdname &＃61; &＃39; 化学 &＃39; , 化学  as  result from #student2  where  stdname &＃61; &＃39; 李四 &＃39;
 2  union all
 3  SELECT &＃39; 李四 &＃39; as  stdname,stdname &＃61; &＃39; 数学 &＃39; , 数学  as  result from #student2  where  stdname &＃61; &＃39; 李四 &＃39;
 4  union all
 5  SELECT &＃39; 李四 &＃39; as  stdname,stdname &＃61; &＃39; 物理 &＃39; , 物理  as  result from #student2  where  stdname &＃61; &＃39; 李四 &＃39;
 6  union all
 7  SELECT &＃39; 李四 &＃39; as  stdname,stdname &＃61; &＃39; 语文 &＃39; , 语文  as  result from #student2  where  stdname &＃61; &＃39; 李四 &＃39;  
  8  union all
 9  SELECT &＃39; 张三 &＃39; as  stdname,stdname &＃61; &＃39; 化学 &＃39; , 化学  as  result from #student2  where  stdname &＃61; &＃39; 张三 &＃39;
10  union all
11  SELECT &＃39; 张三 &＃39; as  stdname,stdname &＃61; &＃39; 数学 &＃39; , 数学  as  result from #student2  where  stdname &＃61; &＃39; 张三 &＃39;
12  union all
13  SELECT &＃39; 张三 &＃39; as  stdname,stdname &＃61; &＃39; 物理 &＃39; , 物理  as  result from #student2  where  stdname &＃61; &＃39; 张三 &＃39;
14  union all
15  SELECT &＃39; 张三 &＃39; as  stdname,stdname &＃61; &＃39; 语文 &＃39; , 语文  as  result from #student2  where  stdname &＃61; &＃39; 张三 &＃39;
 重构到&＃xff1a;

  代码
 1  declare &＃64;sql2 varchar( 4000 )
 2    set  &＃64;sql2  &＃61;   &＃39;&＃39; 
 3  SELECT &＃64;sql2 &＃61; &＃64;sql2 &＃43; 
 4  &＃39; SELECT &＃39;&＃39;&＃39; &＃43; stdname &＃43; &＃39;&＃39;&＃39; as stdname,stdname&＃61; &＃39;&＃39; 化学 &＃39;&＃39; , 化学 as result from #student2 where stdname&＃61; &＃39;&＃39;&＃39; &＃43; stdname &＃43; &＃39;&＃39;&＃39;
 5  union all
 6  SELECT &＃39;&＃39;&＃39; &＃43;stdname&＃43; &＃39;&＃39;&＃39; as  stdname,stdname &＃61; &＃39;&＃39; 数学 &＃39;&＃39; , 数学  as  result from #student2  where  stdname &＃61; &＃39;&＃39;&＃39; &＃43;stdname&＃43; &＃39;&＃39;&＃39;
 7  union all
 8  SELECT &＃39;&＃39;&＃39; &＃43;stdname&＃43; &＃39;&＃39;&＃39; as  stdname,stdname &＃61; &＃39;&＃39; 物理 &＃39;&＃39; , 物理  as  result from #student2  where  stdname &＃61; &＃39;&＃39;&＃39; &＃43;stdname&＃43; &＃39;&＃39;&＃39;
 9  union all
10  SELECT &＃39;&＃39;&＃39; &＃43;stdname&＃43; &＃39;&＃39;&＃39; as  stdname,stdname &＃61; &＃39;&＃39; 语文 &＃39;&＃39; , 语文  as  result from #student2  where  stdname &＃61; &＃39;&＃39;&＃39; &＃43;stdname&＃43; &＃39;&＃39;&＃39;  union all  &＃39;
11  from (SELECT stdname FROM #student2)  as  a
12  SELECT &＃64;sql2  &＃61;  LEFT(&＃64;sql2,LEN(&＃64;sql2)  -   10 )
13  PRINT(&＃64;sql2)
14  exec(&＃64;sql2)
如果要求不能出现  化学  数学  物理 语文 这样的关键字&＃xff0c;那么可以这样写&＃xff1a;

  代码
 1  select [name] into #tmpCloumns
 2  from tempdb.dbo.syscolumns
 3  where  id &＃61; object_id( &＃39; tempdb.dbo.#student2 &＃39; )
 4  and [name] <> &＃39; stdname &＃39;
 5  select  *   from #tmpCloumns
 6 
 7  declare &＃64;strSql nvarchar( 800 )
 8  select &＃64;strSql &＃61; &＃39;&＃39;
 9  select &＃64;strSql &＃61; &＃64;strSql &＃43; &＃39; union all &＃39; &＃43; char ( 10 ) &＃43; char ( 13 ) &＃43;
10                   &＃39; select [stdname], &＃39;&＃39;&＃39; &＃43; [name] &＃43; &＃39;&＃39;&＃39;  as [科目],[ &＃39; &＃43; [name] &＃43; &＃39; ] &＃39; &＃43; char ( 10 ) &＃43; char ( 13 ) &＃43;
11                   &＃39; from [#student2] &＃39; &＃43; char ( 10 ) &＃43; char ( 13 )
12  from #tmpCloumns
13 
14  select &＃64;strSql &＃61; substring(&＃64;strSql, 11 ,len(&＃64;strSql)) &＃43; &＃39; order by stdname,[科目] &＃39;
15  -- print &＃64;strSql
16  exec(&＃64;strsql) 
 这种题目&＃xff0c;在各种笔试中出现的概率还是非常大的&＃xff0c;大家不用死记。以前有的朋友看着复杂的报表查询&＃xff0c;几百行SQL,望而生畏&＃xff0c;然后说&＃xff1a;"这是哪个SQL超人写的啊&＃xff01;"其实&＃xff0c;谁一上来不可能写出那么长的SQL,也是慢慢重构--调试--重构-······

本文来自CSDN博客&＃xff0c;转载请标明出处&＃xff1a;http://blog.csdn.net/leftfist/archive/2009/12/29/5097307.aspx