几道sql的面试题目

第一题：把表一转换为表二

表一（student）：

表二：

SQL语句：

SELECT stdname,
IsNull(Max(CASE stdsubject WHEN '化学' THEN result end),0) [化学],
IsNull(Max(CASE stdsubject WHEN '数学' THEN result end),0) [数学],
IsNull(Max(CASE stdsubject WHEN '物理' THEN result end),0) [物理],
IsNull(Max(CASE stdsubject WHEN '语文' THEN result end),0) [语文]
FROM student GROUP BY stdname

聚合函数要包括整个Case When…End语句

第二题：把表一转换为表二

表一（student）：

表二：

SQL语句：

SELECT'李四'as stdname,stdsubject='化学', 化学 as result from student where stdname='李四'
union all
SELECT'李四'as stdname,stdsubject='化学', 化学 as result from student where stdname='李四'
union all
SELECT'李四'as stdname,stdsubject='数学', 数学 as result from student where stdname='李四'
union all
SELECT'李四'as stdname,stdsubject='物理', 物理 as result from student where stdname='李四'
union all
SELECT'李四'as stdname,stdsubject='语文', 语文 as result from student where stdname='李四'
union all
SELECT'张三'as stdname,stdsubject='化学', 化学 as result from student where stdname='张三'
union all
SELECT'张三'as stdname,stdsubject='数学', 数学 as result from student where stdname='张三'
union all
SELECT'张三'as stdname,stdsubject='物理', 物理 as result from student where stdname='张三'
union all
SELECT'张三'as stdname,stdsubject='语文', 语文 as result from student where stdname='张三'

注意此处的SELECT的不常见用法：SELECT ‘张三’ AS stdname、Select stdsubject=’化学’以及Select 化学 as result

Select A As B或Select A B常用于修改显示的列名，由此推广到，它们更普遍的定义可以理解为，以B作为列标题，其内容为A，因此，“’张三’ AS stdname”就是说列名为stdname，值为“张三”，而“化学 as result”意思是列名为result，值为表中“化学”列的数据，从这一看出，“A”的值可以是一个具体的字符串，也可以是某一列，甚至还可以是Select子表达式，如果是字符串，则列的值就等于该字符串，如果是列，则等于该列的值。

Select stdsubject=’化学’于上面As的情况大同小异，是把’化学’这个字符串作为列的值，应用到所有名为stdsubject的列中，属于给列赋值的语句，而且被赋值的列，如此处的stdsubject，不一定要存在于表中，等效于Select A As B中的B，而‘化学’则相当于A，即等号“=”左边的是列标题，右边是列的值。

此外，UNION和UNION ALL的区别是，UNION将保留重复项中的一个，而UNION ALL则会保留全部的重复项

第三题：已经知道原表Tab

year salary

—————— ———————

2000 1000

2001 2000

2002 3000

2003 4000

现要显示如下查询结果

year salary

—————— ———————

2000 1000

2001 3000

2002 6000

2003 10000

即salary为以前年的工资的和

SQL语句：

SELECT s1.year "year",(SELECT SUM(s2.salary) FROM Tab s2 WHERE s2.year<=s1.year) "salary" 

From Tab s1 ORDER BY s1.year

或者

SELECT b.year,SUM(a.salary) FROM Tab a,Tab b WHERE a.year<=b.year GROUP BY b.year ORDER BY b.year;

个人任务前者更容易理解

第四题：已知下表

id strvalue type

1 how 1

2 are 1

3 you 1

4 fine 2

5 thank 2

6 you 2

要求用SQL将它们搜索出来，显示成：#how are you#fine thank you#

SQL语句：

SELECT
(SELECT '#'+REPLACE(
  REPLACE((SELECT strvalue FROM tb_test t WHERE type = 1 FOR XML AUTO),'<t strvalue="',' ')
  ,'"/>', ' ')
   +'#'
)
+
(SELECT REPLACE(REPLACE((SELECT strvalue FROM tb_test t WHERE type = 2 FOR XML AUTO),

'<t strvalue="',' '),'"/>', ' ')+'#')

此处需要注意的是FOR XML AUTO，用于将查询结果集转成XML文档，AUTO关键字指出查询结果集将以层次结构的形式组织

起来，详细说明请查阅：《详解SQL中FOR XML子句的各种用法》

第五题

已知：

Student(S#,Sname,Sage,Ssex) 学生表 

Course(C#,Cname,T#) 课程表 

SC(S#,C#,score) 成绩表 

Teacher(T#,Tname) 教师表

查询“001”课程比“002”课程成绩高的所有学生的学号

SQL语句

SELECT a.S# FROM (SELECT s#,score FROM SC WHERE C#='001') a,(SELECT s#,score
FROM SC WHERE C#='002') b
WHERE a.score>b.score AND a.s#=b.s#;

SELECT…FROM后面还可以用SELECT语句的结果作为数据集

第六题

已知表T(Id,Name,StrValue),Id为主键，且表中同一个Name有多条记录，用SQL语句显示全表，但重复的记录只保留第一条。

SQL语句：

Select * From T Where T.Id In (Select MIN(T.Id) From T Group By T.Name)

多条重复记录中，其第一条记录的Id必然最小，而非重复记录，则有且只有一个Id，显然也是最小的，故用Min函数即可排除

重复数据中不是第一条的记录。

第七题

表Tab1有一Int类型列Id，Id中所有数字不重复，如下图所示：

问：如何用一条SQL语句显示出Id值是否连续，例如：1，2，3，4，5是连续的，而1，2，3，5是不连续的。

如果Id连续则输出True，否则输出False（上图应该输出False）。

SQL语句：

SELECT CASE MAX(Id)-MIN(Id)+1-COUNT(Id) WHEN 0 THEN 'True' ELSE 'False' END FROM Tab1

数学分析：

考察一个集合E{k₁,k₂,k₃,…,k_n}，元素个数Count(E)=n，在元素依次递增的情况下，如果E中元素是连续的，K₁为集合中最小值，K_n为集合中最大值，对于任意数字n，都有：K_n-K_n-1=1，K_n-k₁=n-1，即：Max(E)-Min(E)+1=Count(E)。如果E中元素是不连续的，由于元素不重复，因此至少存在一个元素k_n使得：K_n-K_n-1>1，那么也必有：Max(E)-Min(E)>n-1，即：Max(E)-Min(E)+1>Count(E)。所以，集合元素的连续排列的条件就是：Max(E)-Min(E)+1=Count(E)。同理可证明在元素依次递减的情况下，该等式依然成立。

SQL语句分析：

根据上面数学分析，首先求的Tab1的Max-Min+1值，再计算Count值，如果两者之差等于0，说明该列中的数字是连续的，否则就是不连续的。