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