1. 用一条 SQL 语句 查询出每门课都大于 80 分的学生姓名
name kecheng
fenshu
张三
语文
81
张三
数学
75
李四
语文
76
李四
数学
90
王五
语文
81
王五
数学
100
王五
英语
90
思路：这里不能直接用 分数 >80 这样的比较条件来查询的到结果，因为要求没门成绩都大于 80 。我们可以反过来思考，如果有一门成绩小于 80 ，那么就不符合要求。先找出成绩表中成绩 <80 的多有学生姓名，不能重复，然后再用 not in 找出不再这个集合中的学生姓名。
create
table
#成绩(姓名varchar(
20
),课程名称varchar(
20
),分数int)
insert
into
#成绩values
(
'
张三
'
,
'
语文
'
,
81
),
(
'
张三
'
,
'
数学
'
,
75
),
(
'
李四
'
,
'
语文
'
,
76
),
(
'
李四
'
,
'
数学
'
,
90
),
(
'
王五
'
,
'
语文
'
,
81
),
(
'
王五
'
,
'
数学
'
,
100
),
(
'
王五
'
,
'
英语
'
,
90
)
select
distinct
(姓名)
from
#成绩 where 姓名 not
in
(
select
distinct
(姓名)
from
#成绩 where 分数
<=
80
)
经 luofer 提示还有一种思路，是用group by + hvaing，这绝对是一种好方法。我估计出这个题的人就是要考察这个知识，代码如下：
select
姓名
from
#成绩
group
by
姓名
having
min
(分数)
>
80
还有一种方法类似于第一种
select
distinct
a.姓名
from
#成绩 a
where
not
exists
(
select
1
from
#成绩
where
分数
<
80
and
姓名
=
a.姓名)
2. 学生表 如下 :
自动编号
学号
姓名
课程编号
课程名称
分数
1 2005001
张三
0001
数学
69
2 2005002
李四
0001
数学
89
3 2005001
张三
0001
数学
69
删除除了自动编号不同
,
其他都相同的学生冗余信息
思路：这个和上面的一样，也不能直接删除，而是要先找出自动编号不相同，其他都相同的行，这个要使用 group by 语句，并且将其他的字段都放在 group by 后面，这样找出来的行都是没有冗余的行，然后随便保留其中一个自动编号，删除其他的行。
create
table
#成绩(自动编号
int
, 学号
int
,姓名
varchar
(
20
),课程编号
int
,课程名称
varchar
(
20
),分数
int
)
insert
into
#成绩
values
(
1
,
2005001
,
'
张三
'
,
1
,
'
语文
'
,
81
),
(
2
,
2005001
,
'
李四
'
,
1
,
'
语文
'
,
81
),
(
3
,
2005001
,
'
张三
'
,
1
,
'
语文
'
,
81
),
(
4
,
2005001
,
'
张三
'
,
1
,
'
语文
'
,
81
)
select
*
from
#成绩
drop
table
#成绩
delete
from
#成绩
where
自动编号
not
in
(
select
MIN
(自动编号)
from
#成绩
group
by
学号,姓名,课程编号,课程名称,分数)
经 广岛之恋 的提醒发现另外一种思路,代码如下：
delete
from
#成绩
where
自动编号
not
in
(
select
distinct
(a.自动编号)
from
#成绩 a
join
#成绩 b
on
a.自动编号
>
b.自动编号
where
a.学号
=
b.学号
and
a.姓名
=
b.姓名
and
a.课程编号
=
b.课程编号
and
a.分数
=
b.分数)
如果不考虑自动编号，还可以这样
--
注意identity用法，只能用在有into的select语句中
select
identity
(
int
,
1
,
1
)
as
id, 学号,姓名,课程编号,课程名称,分数
into
#
temp
from
#成绩
group
by
学号,姓名,课程编号,课程名称,分数
truncate
table
#成绩
insert
into
#成绩
select
*
from
#
temp
3. 一个叫 department 的表，里面只有一个字段 name, 一共有 4 条纪录，分别是 a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条 sql 语句显示所有可能的比赛组合。
思路：这是一个组合问题，就是说四个不同的元素有多少种不同的两两组合。现在要把这个问题用 sql 语句实现。既然这四个元素是不相同的，我们可以将这个表当成两个集合，求他们的笛卡尔积，然后再从笛卡尔积中找到那些元素不相同的，并且不重复的组合。
create
table
#department(taname
char
(
1
))
insert
into
#department
values
(
'
a
'
),(
'
b
'
),(
'
c
'
),(
'
d
'
)
--下面两条语句都可以，多谢
wanglinglong提醒
select
a.taname,b.taname
from
#department a,#department b
where
a.taname
<
b.taname
select
a.taname,b.taname
from
#department a,#department b
where
a.taname
>
b.taname
4.
怎么把这样一个表
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year
m1 m2 m3 m4
1991
1.1
1.2
1.3
1.4
1992
2.1
2.2
2.3
2.4
思路：这个很明显是一个行列转换，首先会想到 pivot 。结果中有 m1 ， m2 ， m3 ， m4 四个新的列，他们需要从原来的行中转换。
create
table
#sales(years
int
,months
int
,amount
float
)
insert
into
#sales
values
(
1991
,
1
,
1.1
),
(
1991
,
2
,
1.2
),
(
1991
,
3
,
1.3
),
(
1991
,
4
,
1.4
),
(
1992
,
1
,
2.1
),
(
1992
,
2
,
2.2
),
(
1992
,
3
,
2.3
),
(
1992
,
4
,
2.4
)
select
pt.years,
[
1
]
as
m1,
[
2
]
as
m2,
[
3
]
as
m3,
[
4
]
as
m4
from
(
select
sod.amount,sod.months,sod.years
as
years
from
#sales sod) so
pivot
(
min
(so.amount)
for
so.months
in
(
[
1
]
,
[
2
]
,
[
3
]
,
[
4
]
))
as
pt
注意[1],[2],[3],[4]中括号不可缺少，否则会出错。还有一种写法是使用子查询，这个要新建 4 个子查询进而得到新的列：
select
a.years,
(
select
m.amount
from
#sales m
where
months
=
1
and
m.years
=
a.years)
as
m1,
(
select
m.amount
from
#sales m
where
months
=
2
and
m.years
=
a.years)
as
m2,
(
select
m.amount
from
#sales m
where
months
=
3
and
m.years
=
a.years)
as
m3,
(
select
m.amount
from
#sales m
where
months
=
4
and
m.years
=
a.years)
as
m4
from
#sales a
group
by
a.years
还可以这样写，大同小异：
select
a.years,
sum
(
case
months
when
1
then
amount
else
0
end
)
as
m1,
sum
(
case
months
when
2
then
amount
else
0
end
)
as
m2,
sum
(
case
months
when
3
then
amount
else
0
end
)
as
m3,
sum
(
case
months
when
4
then
amount
else
0
end
)
as
m4
from
#sales a
group
by
a.years
5. 有两个表 A 和 B ，均有 key 和 value 两个字段，如果 B 的 key 在 A 中也有，就把 B 的 value 换为 A 中对应的 value 。这道题的 SQL 语句怎么写？
思路：这个问题看似简单，只要一个 update 语句，然后找到相同的 key ，更新 value 字段就可以了。可能你首先会写成这样： update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys) 。但是要注意的是如果仅仅找相同的 key 会有很多匹配，更新的时候会出现错误，所以要在外层限制。
create
table
#a(keys
int
, value
varchar
(
10
))
insert
into
#a
values
(
1
,
'
aa
'
),
(
2
,
'
ab
'
),
(
3
,
'
ac
'
)
create
table
#b(keys
int
, value
varchar
(
10
))
insert
into
#b
values
(
1
,
'
aa
'
),
(
2
,
'
a
'
),
(
3
,
'
a
'
)
update
#b
set
#b.value
=
(
select
#a.value
from
#a
where
#a.keys
=
#b.keys)
where
#b.keys
in
(
select
#b.keys
from
#b,#a
where
#a.keys
=
#b.keys
and
#a.value
<>
#b.value)
在 luofer 的提醒之，有了第二个思路
update
#b
set
#b.value
=
s.value
from
(
select
*
from
#a
except
select
*
from
#b) s
where
s.keys
=
#b.keys
luofer 是牛人啊！
再举一个例子，已知有一个课程表PressErp..Course，里面已经有一些数据，现在要向教师表[Press].[dbo].[Teacher]中添加新数据，但是有的老师课程可能不在这个已知的课程表中，这时候就可以用到这个except了，它的作用就是向这个课程表中添加以前不存在的课程名称。注意关键字distinct和except。
insert
into
Course
select
distinct
(课程)
from
[
Press
]
.
[
dbo
]
.
[
Teacher
]
a
except
select
b.CourseName
from
Course b
6. 两张关联表，删除主表中已经在副表中没有的信息。
思路：这个就是存在关系，可以使用 in ，也可以使用 exists。
create
table
#zhubiao(id
int
,name
varchar
(
5
))
insert
into
#zhubiao
values
(
1
,
'
aa
'
),
(
2
,
'
ab
'
),
(
3
,
'
ac
'
)
create
table
#fubiao(id
int
, grade
varchar
(
5
))
insert
into
#fubiao
values
(
1
,
'
aa
'
),
(
2
,
'
ab
'
)
delete
from
#zhubiao
where
id
not
in
(
select
b.id
from
#fubiao b)
delete
from
#zhubiao
where
not
exists
(
select
1
from
#fubiao
where
#zhubiao.id
=
#fubiao.id)
7. 原表 :
courseid coursename score
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
为了便于阅读 , 查询此表后的结果显式如下 ( 及格分数为 60):
courseid coursename score mark
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
思路：这个就很直接了，使用 case 语句判断一下。
create
table
#scores(course
int
,coursename
varchar
(
10
),score
int
)
insert
into
#scores
values
(
1
,
'
java
'
,
70
),
(
2
,
'
oracle
'
,
90
),
(
3
,
'
xmls
'
,
40
),
(
4
,
'
jsp
'
,
30
),
(
5
,
'
servlet
'
,
80
)
select
course,coursename,
case
when
score
>
60
then
'
pass
'
else
'
fail
'
end
as
mark
from
#scores
8.
原表
:
id proid proname
1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A
查询后的表
:
id pro1 pro2
1 M F
2 N G
3 B A
思路：依旧是行列转换，这个在面试中的几率很高。这个语句还是有两种写法，如下：
create
table
#table1(id
int
,proid
int
,proname
char
)
insert
into
#table1
values
(
1
,
1
,
'
M
'
),
(
1
,
2
,
'
F
'
),
(
2
,
1
,
'
N
'
),
(
2
,
2
,
'
G
'
),
(
3
,
1
,
'
B
'
),
(
3
,
2
,
'
A
'
)
select
id,
(
select
proname
from
#table1
where
proid
=
1
and
id
=
b.id)
as
pro1,
(
select
proname
from
#table1
where
proid
=
2
and
id
=
b.id)
as
pro2
from
#table1 b
group
by
id
select
d.id,
[
1
]
as
pro1,
[
2
]
as
pro2
from
(
select
b.id,b.proid,b.proname
from
#table1 b)
as
c
pivot
(
min
(c.proname)
for
c.proid
in
(
[
1
]
,
[
2
]
))
as
d
9. 如下
表
a
列
a1 a2
记录
1 a
1 b
2 x
2 y
2 z
用
select
能选成以下结果吗？
1 ab
2 xyz
思路：这个开始想使用行列转换来写，没有成功，后来没有办法只好用游标，代码如下：
create
table
#table2(id
int
, value
varchar
(
10
))
insert
into
#table2
values
(
1
,
'
a
'
),
(
1
,
'
b
'
),
(
2
,
'
x
'
),
(
2
,
'
y
'
),
(
2
,
'
z
'
)
create
table
#table3(id
int
,value
varchar
(
100
) );
insert
into
#table3(id,value)
select
distinct
(id),
''
from
#table2
declare
@id
int
,
@name
varchar
(
10
)
declare
mycursor
cursor
for
select
*
from
#table2
open
mycursor
fetch
next
from
mycursor
into
@id
,
@name
while
(
@@Fetch_Status
=
0
)
begin
update
#table3
set
value
=
value
+
@name
where
id
=
@id
fetch
next
from
mycursor
into
@id
,
@name
end
close
mycursor
deallocate
mycursor
select
*
from
#table3
有两个要注意的地方，
a.#table3里面的value字段初始值如果不设置的话默认是null，后面更新的时候null+'a'任然是null，最后得到的value永远是null。所以默认是''
b.第二个 fetch 语句一定要放在 begin 和 end 之间，要不然会死循环的，不常用的语句写起来很不爽快
经 scottshen 提醒，使用for xml更加的简单，看下面的语句：
SELECT
id,
(
SELECT
value
+
''
FROM
#table2
WHERE
id
=
a.id
FOR
XML PATH(
''
))
AS
[
values
]
FROM
#table2
AS
a
GROUP
BY
a.id
--
或者这样写
select
distinct
a.id,
(
select
b.value
+
''
from
#table2 b
where
b.id
=
a.id
for
XML path(
''
))
as
value
from
#table2 a
下面这一句帮助我们理解for xml的工作原理
select
''
+
a.value
from
#table2 a
where
id
=
2
for
xml path(
''
)
10.一个业务有多个访谈信息，要求每次添加访谈信息的时候都要更新业务中的当前添加的访谈信息的ID，这样可以快速查找当前业务的最新状态。
update
MaintainMessage
set
CurrentCommunicateID
=
(
select
MAX
(a.ID)
from
Communicate a
where
a.MaintainId
=
MaintainMessage.ID)
注意MaintainMessage.ID中的MaintainMessage不能省略。
