SQL高级查询

转载 2005年03月04日 11:13:00


找出某一列里最大或最小的前几个,或是大于或小于某一个值(最大值或平均值)的数据
1:找出公司里收入最高的前三名员工:

SQL> select rownum, last_name, salary
 2  from (select last_name, salary
 3        from s_emp
 4        order by salary desc)
 5  where rownum<=3;

   ROWNUM LAST_NAME                     SALARY
---------- ------------------------- ----------
        1 Velasquez                       4750
        2 Ropeburn                        2945
        3 Nguyen                        2897.5


注意:请大家分析一下一下语句为什么不对:

SQL> select rownum, last_name, salary
 2  from s_emp
 3  where rownum<=3
 4  order by salary desc;

   ROWNUM LAST_NAME                     SALARY
---------- ------------------------- ----------
        1 Velasquez                       4750
        3 Nagayama                        2660
        2 Ngao                            2000


2: 找出表中的某一行或某几行的数据:

(1):找出表中第三行数据:
用以下方法是不行的,因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。

SQL> select * from s_emp
 2  where rownum=3;

no rows selected

SQL> select * from s_emp
 2  where rownum between 3 and 5;

no rows selected

正确的方法如下:

SQL> l
 1  select last_name, salary
 2  from (select rownum a, b.*
 3        from s_emp b)
 4* where a=3
SQL> /

LAST_NAME                     SALARY
------------------------- ----------
Nagayama                        2660

(2):找出第三行到第五行之间的数据:
SQL> l
 1  select last_name, salary
 2  from (select rownum a, b.*
 3        from s_emp b)
 4* where a between 3 and 5
SQL> /

LAST_NAME                     SALARY
------------------------- ----------
Nagayama                        2660
Quick-To-See                    2755
Ropeburn                        2945

3:找出那些工资高于他们所在部门的平均工资的员工。

(1):第一种方法:
SQL> select last_name, dept_id, salary
 2  from s_emp a
 3  where salary>(select avg(salary)
 4                from s_emp
 5                where dept_id=a.dept_id);

LAST_NAME                    DEPT_ID     SALARY
------------------------- ---------- ----------
Velasquez                         50       4750
Urguhart                          41       2280
Menchu                            42       2375
Biri                              43       2090
Catchpole                         44       2470
Havel                             45     2483.3
Nguyen                            34     2897.5
Maduro                            41       2660
Nozaki                            42       2280
Schwartz                          45       2090

10 rows selected.

(2):第二种方法:
SQL> l
 1  select a.last_name, a.salary, a.dept_id, b.avgsal
 2  from s_emp a, (select dept_id, avg(salary) avgsal
 3               from s_emp
 4               group by dept_id) b
 5  where a.dept_id=b.dept_id
 6* and a.salary>b.avgsal
SQL> /

LAST_NAME                     SALARY    DEPT_ID     AVGSAL
------------------------- ---------- ---------- ----------
Velasquez                       4750         50     3847.5
Urguhart                        2280         41     2181.5
Menchu                          2375         42 2055.16667
Biri                            2090         43       1710
Catchpole                       2470         44       1995
Havel                         2483.3         45     2069.1
Nguyen                        2897.5         34       2204
Maduro                          2660         41     2181.5
Nozaki                          2280         42 2055.16667
Schwartz                        2090         45     2069.1

10 rows selected.

4:找出那些工资高于他们所在部门的manager的工资的员工。

SQL> l
 1  select id, last_name, salary, manager_id
 2  from s_emp a
 3  where salary>(select salary
 4                from s_emp
 5*               where id=a.manager_id)
SQL> /

       ID LAST_NAME                     SALARY MANAGER_ID
---------- ------------------------- ---------- ----------
        6 Urguhart                        2280          2
        7 Menchu                          2375          2
        8 Biri                            2090          2
        9 Catchpole                       2470          2
       10 Havel                         2483.3          2
       12 Giljum                          2831          3
       13 Sedeghi                       2878.5          3
       14 Nguyen                        2897.5          3
       15 Dumas                           2755          3
       16 Maduro                          2660          6

10 rows selected.

第一题:有两个表分别如下:
表A(varchar(32) NAME,int GRADE)
数据:
ZHANGSHAN 80
LISI      60
WANGWU    84

表B(varchar(32) NAME,int AGE)
数据:
ZHANGSHAN 26
LISI      24
WANGWU    26
WUTIAN    26

(1)写SQL语句得到如下查询结果:
NAME      GRADE   AGE    
ZHANGSHAN 80      26
LISI      60      24
WANGWU    84      26
WUTIAN    NULL    26
疑问:这里的没有成绩的那个人的记录怎么得到呢?

(2)写SQl语句根据名字(NAME)相同按年龄(AGE)分组得到不同年龄的人的平均成绩,并写出结果。
疑问:按照名字相同,WUTIAN这个人没有成绩该不该把他统计在内呢?

(3)有一个数据库表dept中有如下数据:
id_no id_name
1000  S1
1001  S2
1002  S3
1003  S4
1000  S5
1000  S6
1001  S7
表中有id_no重复,如id_no为1000的有3条记录,如id_no为1001的有2条记录,
现在要按照id_no给表建索引,需要删除id_no重复了的那些记录,但不能删掉所有拥有该id_no的记录,必需保留一条拥有该id_no的记录(如id_no为1000的只剩下一条记录)

(1)请写出SQl语句(或SQL语句组),查询所有id_no重复的记录。

(2)请写出SQl语句实现题目要求的结果。 

1:
SQL> create table a
 2  (name varchar2(32),
 3  grade int);
Table created.
SQL> insert into a_t
 2  values('&a',&b);
Enter value for a: zhangshan
Enter value for b: 80
old   2: values('&a',&b)
new   2: values('zhangshan',80)

1 row created.

SQL> /
Enter value for a: lisi
Enter value for b: 60
old   2: values('&a',&b)
new   2: values('lisi',60)

1 row created.

SQL> /
Enter value for a: wangwu
Enter value for b: 84
old   2: values('&a',&b)
new   2: values('wangwu',84)

1 row created.

SQL> commit;

Commit complete.

SQL> create table b_t
 2  (name varchar2(32),
 3  age int);

Table created.

SQL> insert into b_t
 2  values('&a',&b);
Enter value for a: zhangshan
Enter value for b: 26
old   2: values('&a',&b)
new   2: values('zhangshan',26)

1 row created.

SQL> /
Enter value for a: lisi
Enter value for b: 24
old   2: values('&a',&b)
new   2: values('lisi',24)

1 row created.

SQL> /
Enter value for a: wangwu
Enter value for b: 26
old   2: values('&a',&b)
new   2: values('wangwu',26)

1 row created.

SQL> /
Enter value for a: wutian
Enter value for b: 26
old   2: values('&a',&b)
new   2: values('wutian',26)

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> col grade null 'null'
SQL> l
 1  select b.name, a.grade, b.age
 2  from a_t a,b_t b
 3* where a.name(+)=b.name
SQL> /

NAME                                  GRADE        AGE
-------------------------------- ---------- ----------
lisi                                     60         24
wangwu                                   84         26
wutian                           null               26
zhangshan                                80         26

SQL>

2:
SQL> l
 1  select name, avg(grade), age
 2  from (select b.name name, a.grade grade, b.age age
 3  from a_t a,b_t b
 4  where a.name(+)=b.name)
 5* group by age, name
SQL> /

NAME                             AVG(GRADE)        AGE
-------------------------------- ---------- ----------
lisi                                     60         24
wangwu                                   84         26
wutian                                              26
zhangshan                                80         26

SWUTIAN这个人没有成绩不把他统计在内
3:
1、当我们想要为一个表创建唯一索引时,如果该表有重复的记录,则无法创建成功。
方法原理:
1、Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,
  rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。

2、在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中
  那些具有最大rowid的就可以了,其余全部删除。

3、以下语句用到了3项技巧:rowid、子查询、别名。

实现方法:
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /

表已建立.

SQL> insert into a values('1111','1111');
SQL> insert into a values('1112','1111');
SQL> insert into a values('1113','1111');
SQL> insert into a values('1114','1111');

SQL> insert into a select * from a;

插入4个记录.

SQL> commit;
SQL> select rowid,bm,mc from a;

ROWID BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111

查询到8记录.


查出重复记录
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

ROWID BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111

删除重复记录
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

删除4个记录.

SQL> select rowid,bm,mc from a;

ROWID BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111

其实方法有很多,用IN 或者用GROUP BY同样可以实现:
1: 使用IN:

A) 找出重复数据:
SQL> l
 1  select rowid, e.* from a e
 2* where e.rowid>(select min(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc);
ROWID              BM   MC
------------------ ---- --------------------
AAABdcAAGAAAAYyAAE 1111 1111
AAABdcAAGAAAAYyAAF 1112 1111
AAABdcAAGAAAAYyAAG 1113 1111
AAABdcAAGAAAAYyAAH 1114 1111

B) 删除重复数据:
SQL> l
 1  delete from a
 2  where rowid in (select rowid from a e
 3* where e.rowid>(select min(x.rowid) from a x where x.bm=e.bm and x.mc=e.mc))
SQL> /

4 rows deleted.

SQL> select * from a;

BM   MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111

SQL>

2: 使用GROUP BY:

A): 找出重复数据:
SQL> l
 1  select bm,mc
 2  from a
 3  group by bm,mc
 4* having count(*)>1
SQL> /

BM   MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111

SQL>

B) 删除重复数据:
SQL> delete from a
 2  where (bm,mc) in (select bm,mc
 3                    from a
 4                    group by bm,mc
 5                    having count(*)>1)
 6  and rowid not in (select min(rowid)
 7                    from a
 8                    group by bm,mc
 9                    having count(*)>1);

4 rows deleted.

SQL> select * from a;

BM   MC
---- --------------------
1111 1111
1112 1111
1113 1111
1114 1111

SQL>

Sql Server数据库设计和高级查询

1,数据库设计步骤: (1)收集信息 (2)标识对象(确定数据库中有哪些表) (3)标识对象的属性(确定每个表中的例) (4)标识对象的关系(两个表的关系) 4种关系: 1对1 ...

有用的SQL高级函数与查询

一.序号函数 # rank函数函数 根据排序子句给出递增的序号,但是存在并列并且跳空 顺序递增 select id, name, rank() over(order by cid) a...

Oracle与Mysql的高级查询与难点sql

一、连接查询  1.内连接      内连接用于返回满足连接条件的所有记录。默认情况下,在执行连接查询时如果没有指定任何连接操作符,那么这些连接查询都属于内连接。  Sql代码   1. ...

Oracle数据库之SQL(高级查询)详解

SQL(高级查询)1、SQL(高级查询) 子查询 : 嵌入在其它SQL语句中的SELECT语句,大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT...

Sql server高级查询

高级查询在数据库中用得是最频繁的,也是应用最广泛的 基本常用查询 --select select * from student; --all 查询所有 select all sex from...

day08—SQL高级查询

目标 1、子查询 2、分页查询 3、DECODE函数 4、排序函数 子查询:嵌入在其它SQL语句中的SELECT语句,大部分时候出现在WHERE子句中。 一、子查询在WHERE子句中: ...

PL/SQL 高级语言查询

一.PL/SQL l语言:1.数据定义语言 DDL 2.数据操作语言 DML 3.数据控制语言 DCL PL/SQL 语言实现了将过程结构和Oracle SQL的无缝集成二....

SQL学习之高级查询

高级查询详解 select [select选项] 字段列表 [字段别名] / * from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [...

sql几个:几个高级查询运算词

11、说明:几个高级查询运算词 A: UNION 运算符 UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 U...
  • revivec
  • revivec
  • 2013年05月15日 14:11
  • 667

SQL之高级联结查询

之前有篇文章介绍了简单的联结查询,现在介绍稍微复杂一点的 联结,如果你没有看过之前的文章,请点击下面的链接。 联结查询和子查询 本次示例使用的数据库关系图 SQL联结方式内联结 之前一篇文...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL高级查询
举报原因:
原因补充:

(最多只允许输入30个字)