SQL笔试题

最新推荐文章于 2023-12-08 21:05:10 发布

weixin_34327223

最新推荐文章于 2023-12-08 21:05:10 发布

阅读量94

点赞数

文章标签：数据库

纵览各大社区、论坛，各大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新手们重构到答案。用李建忠老师最爱说的话就是------我不建议一上来就套用模式，而应该从重构到模式。

首先大家会想到分两组

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)

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

表一：

表二：

数据库代码如下：

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

weixin_34327223

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL笔试题

纵览各大社区、论坛，各大ORM框架火得不行了，如NHibernate、LINQ to SQL、ADO.NET Entity framework等，还有最近市场上出版的一本叫《领域驱动设计与模式实战》，里面也凸显了不少NHibernate在领域驱动设计中的作用与地位，也算是第一本与NHibernate相关的书籍吧！不过就NHibernate而言还是没有官方文档介绍得详细呵呵，园子里Kiler已经...
复制链接

扫一扫