sql面试

1、 在表A中存在一部分记录，主键是重复的，将重复的主键删除，只保留改主键的第一条记录

假设表A的数据如下：



ID NAME NAME2 NAME3

1
A
2 A
3 B
4 B
5 C



处理后表A的数据如下：


ID
NAME NAME2 NAME3
1
A

2 B
3 C



答案：

delete test t1
where t1.rowid !=
(
select max(t2.rowid) from test t2
where t2.id = t1.id
)



2、请写出数据库关联更新的SQL，用B表的数据去更新A表，要求主键对应。

假设表A的数据如下：




ID

NAME
NAME2 NAME3

1

A






2

B






3

C








表B的数据如下：




ID

NAME2

NAME3


1

南京

1


2

北京

2


4

上海

3


更新表A后的结果如下：




ID

NAME1

NAME2

NAME3


1

A

南京

1


2

B

北京

2


3

C






要求sql要考虑通用情况，使用表中的任何数据，而不能删除A表中任何一行数据。



update test t1 set(t1.f2,t1.f3) = (select t2.f2,t2.f3 from test2 t2 where t1.id = t2.id)




3、有如下A、B两个表，如何将A表的数据查询出来，结果如B所示？

A表：


姓名

科目

分数


张三

语文

87


李四

数学

98


张三

英语

74


张三

数学

95


李四

语文

80


李四

英语

98




B表：


姓名

语文

数学

英语

总分


张三










李四












思路：



1：先需要想到行转列函数 decode 或 case



select a.name 姓名,
(case
when course = '语文' then
score
end) 语文,
(case
when course = '英语' then
score
end) 英语,
(case
when course = '数学' then
score
end) 数学,
score
from test a
order by a. name desc;



2：想到求列的总和（想到sum语句）

select a.name 姓名,
sum(decode(course, '语文', score)) 语文,
sum(decode(course, '数学', score)) 数学,
sum(decode(course, '英语', score)) 英语,
sum(score) 总分
from test a
group by a.name
order by a. name desc;



另外一种方法(不推荐)

select a.name 姓名,
sum(case
when course = '语文' then
score
end) 语文,
sum(case
when course = '英语' then
score
end) 英语,
sum(case
when course = '数学' then
score
end) 数学,
b.total 总分
from test a, (select name, sum(score) total from test group by name) b
where a.name = b.name
group by a.name, b.total
order by a. name desc;



另外一个相似题目：
有两张关联的表A、B
A： id acount pay_id B: pay_id B_acount
1 100 1 1 0
2 200 2 2 0
3 200 1
要求，更新表B，使得B_acount的值等于表A中对应的pay_id的所有acount之和。打印如下：
B： pay_id B_acount
1 300
2 200
------------------------------------------------------------------
update B
inner join A on B.pay_id = A.pay_id
set b_acount=
(select sum(acount) from A where A.pay_id=B.pay_id)



4：首先是需要对表做一个规划，即必须有自己的ID和ID对应这条记录的PID（父ID）。
简单的数据结构如下
id pid
1 0
2 0
3 1
4 1
5 2
6 2
7 3
8 4
9 5
比如你现在需要取出ID是1的以及其下所有的记录，如：
id pid
1 0
3 1
4 1
7 3
8 4

查询语句如下：
select *
from TEST
start with id = 1
connect by prior id = pid
order by id asc

如果有条件语句，比如where or when，其语句放在 start with前面



5：MERGE的用法



create table TEST
(
ID NUMBER,
NAME VARCHAR2(100),
PID NUMBER default 0
)



MERGE INTO test D
USING (SELECT 1 id FROM dual) S
ON (S.id = D.id)
WHEN MATCHED THEN
UPDATE SET D.name = 'aaA' where D.Id = 1
WHEN NOT MATCHED THEN
INSERT (d.id, D.name, d.pid) VALUES (1, 'A', 0)



参考：

第三题的题目参考自 http://haohaoxuexi.iteye.com/blog/1820541