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