sql语句总结

最新推荐文章于 2024-11-07 10:34:02 发布

iteye_19698

最新推荐文章于 2024-11-07 10:34:02 发布

阅读量108

点赞数

分类专栏： sql 文章标签： sql oracle

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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