SQL经典实例大全3

最新推荐文章于 2023-05-03 10:11:51 发布

dieqi9270

最新推荐文章于 2023-05-03 10:11:51 发布

阅读量571

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/yuanscn/p/11220196.html

版权

本章主要介绍基本的SELECT语句。充分理解这些基础知识十分重要，因为本章中的许多内容不仅会出现在后面更复杂的实例里，同时也是日常SQL操作的一部分。

1.1检索所有行和列

问题

你有一张表，并且想查看表中的所有数据。

解决方案

用特殊符号对该表执行SELECT查询。

select * from enp

讨论

在SQL中，符号有着特殊含义。该符号使得查询语句返回指定表的所有列。由于没有指定WHERE子句，因此所有行都会被提取出来。你也可以使用另一种方法，列出表中的每一列。

select ernpno,enane,job,sal,mgr,hiredate,conn,deptno from enp

在交互式即席查询中，使用SELECT *会更加容易。然而，在编写程序代码时，最好具体指明每一列。虽然执行结果相同，但指明每一列让你能清楚地知道査询语句会返回哪些列。类似地，对于其他人而言，这样的查询语句也会更易于理解，因为他们可能不知道所要査询的表里包含哪些列。

1.2筛选行

问题

你有一张表，并且只想査看满足指定条件的行。

解决方案

使用WHERE子句指明保留哪些行。例如，下面的语句将査找部门编号为10的所有员工。

1 select *

2 from enp

3 where deptno = 10

讨论

可以使用WHERE子句来筛选出我们感兴趣的行。如果WHERE子句的表达式针对某一行的判定结果为真，那么就会返回该行的数据。

大多数数据库都支持常用的运算符，例如=、<、>、<=、>=、！和<>。除此之外，你可能需要指定多个条件来筛选数据，这时就需要使用AND、OR和圆括号。下一个实例将讨论这一点。

1.2 查找满足多个查询条件的行

问题

你想返回满足多个査询条件的行。

解决方案

使用带有0R和AND条件的WHERE子句。例如，如果你想找出部门编号为10的所有员工、有业务提成的所有员工以及部门编号是20且工资低于2000美元的所有员工。

1 select *

2 from enp

3 where deptno = 10

4 or comm is not null

5 or sal <= 2000 and deptno=20

讨论

你可以组合使用AND、OR和圆括号来筛选满足多个査询条件的行。在这个实例中，WHERE 子句找出了如下的数据。

DEPTNO 等于 10，或
C0MM 不是 Null,或
DEPTNO等于20且工资不高于2000美元的员工。

圆括号里的查询条件被一起评估。例如，试想一下如果采用下面的做法，检索结果会发生什么样的变化。

select * from enp

where ( deptno = 10

or conn is not null or sal <= 2000

and	deptno=20
EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM DEPTNO
7369	SMITH	CLERK	7902	17-DEC-1980	800	20
7876	ADAMS	CLERK	7788	12-JAN-1983	1100	20

1.4筛选列

问题

你有一张表，并且只想查看特定列的值。

解决方案

指定你感兴趣的列。例如，只査看员工的名字、部门编号和工资。

1 select enane,deptno,sal

2 from enp

讨论

在SELECT语句里指定具体的列名，可以确保查询语句不会返回无关的数据。当在整个网络范围内检索数据时，这样做尤为重要，因为它避免了把时间浪费在检索不需要的数据上。

1.4 创建有意义的列名

问题

你可能想要修改检索结果的列名，使其更具可读性且更易于理解。考虑下面这个查询，它返回的是每个员工的工资和业务提成。

1 select sal,conn

2 from enp

sal指的是什么？是sale的缩写吗？是人名吗？ com又是什么？是communication的缩写吗？显然，检索结果应该让人容易理解。

解决方案

使用AS关键字，并以o「iginal_nane AS new一name的形式来修改检索结果的列名。对于一些

数据库而言，AS不是必需的，但所有的数据库都支持这个关键字。

1 select sal as salary, conm as commission

2 from ernp

SALARY COMMISSION

800

1600 300

1250 500

2975

1250 1300

2850

2450

3000

5000

1500 0

1100 950 3000 1300

讨论

使用AS关键字重新命名查询所返回的列，即是创建别名。新的列名被称作别名。创建好的别名对于查询语句大有裨益，它能让查询结果更易于理解。

1.5 在WHERE子句中引用别名列

问题

你已经为检索结果集创建了有意义的列名，并且想利用WHERE子句过滤掉部分行数据。但是，如果你尝试在WHERE子句中引用别名列，查询无法顺利执行。

select sal as salary, conm as commission from emp where salary < 5000

解决方案

把查询包装为一个内嵌视图，这样就可以引用别名列了。

1 select *

2 from (

3 select sal as salary, conn as commission

4 from enp

5 ) x

6 where salary < 5000

讨论

在这个简单的实例中，你可以不使用内嵌视图。在WHERE子句里直接引用C0MM列和SAL 列，也可以达到同样的效果。当你想在WHERE子句中引用下列内容时，这个解决方案告诉你该如何做。

•聚合函数 •标量子査询 •窗口函数 •别名

将含有别名列的查询放入内嵌视图，就可以在外层查询中引用别名列。为什么要这么做呢？ WHERE子句会比SELECT子句先执行，就最初那个失败的査询例子而言，当WHERE子句被执行时，SALARY和COMMISSION尚不存在。直到WHERE子句执行完毕，那些别名列才会生效。然而，FROM子句会先于WHERE子句执行。如果把最初的那个查询放入一个FROM子句，

其查询结果会在最外层的WHERE子句开始之前产生，这样一来，最外层的WHERE子句就能 “看见”别名列了。当表里的某些列没有被恰当命名的时候，这个技巧尤其有用。

1.6 串联多列的值

1.问题

你想将多列的值合并为一列。例如，你想查询EMP表，并获得如下结果集。

CLARK WORKS AS A MANAGER KING WORKS AS A PRESIDENT MILLER WORKS AS A CLERK

然而，你需要的数据来自EMP表的ENAME列和]0B列。

select enane, job from enp where deptno = 10

ENAME

CLARK

KING

MILLER

MANAGER

PRESIDENT

CLERK

2.解决方案

使用数据库中的内置函数来串联多列的值。

DB2、Oracle 和 PostgreSQL

这些数据库把双竖线作为串联运算符。

1 select enane||' WORKS AS A '||job as nsg

2 from emp

3 where deptno=10

MySQL

该数据库使用CONCAT函数。

1 select concat(enane, ¹ WORKS AS A ', job) as nsg

2 from enp

3 where deptno=10

SQL Server

该数据库使用“+”作为串联运算符。

1 select enane + ' WORKS AS A ' + job as nsg

2 from enp

3 where deptno=10

讨论

使用CONCAT函数可以串联多列的值。在DB2、Oracle和PostgreSQL中，“||”是CONCAT函数的快捷方式，在SQL Server中则为“+”。

1.8在SELECT语句里使用条件逻辑

1.问题

你想在SELECT语句中针对查询结果值执行IF-ELSE操作。例如，你想生成类似这样的结果：如果员工的工资少于2000美元，就返回UNDERPAID;如果超过4000美元就返回 OVERPAID；若介于两者之间则返回0K。查询结果如下所示。

ENAME	SAL	STATUS
SMITH	800	UNDERPAID
ALLEN	1600	UNDERPAID
WARD	1250	UNDERPAID
JONES	2975	OK
MARTIN	1250	UNDERPAID
BLAKE	2850	OK
CLARK	2450	OK
SCOTT	3000	OK
KING	5000	OVERPAID
TURNER	1500	UNDERPAID
ADAMS	1100	UNDERPAID
JAMES	950	UNDERPAID
FORD	3000	OK
MILLER	1300	UNDERPAID

解决方案

在SELECT语句里直接使用CASE表达式来执行条件逻辑。

1 select enane,sal,

2 case when sal <= 2000 then 'UNDERPAID'

3 when sal >= 4000 then 'OVERPAID'

4 else 'OK'

5 end as status

6 from enp

讨论

CASE表达式能对査询结果执行条件逻辑判断。你可以为CASE表达式的执行结果取一个别名，使结果集更有可读性。就本例而言，STATUS就是CASE表达式执行结果的别名。ELSE 子句是可选的，若没有它，对于不满足测试条件的行，CASE表达式会返回Null。

1.9限定返回行数

问题

你想限定查询结果的行数。你不关心排序，任意《行都可以。

解决方案

使用数据库的内置功能来控制返回的行数。

DB2

使用FETCH FIRST子句。

1 select *

2 from enp fetch first 5 rows only

MySQL 和 PostgreSQL

使用LIMIT子句。

1 select *

2 from enp limit 5 Oracle

对于Oracle而言，通过在WHERE子句中限制ROWNUM的值来获得指定行数的结果集。

1 select *

2 from emp

3 where rownum <= 5

SQL Server

使用TOP关键字限定返回行数。

1 select top 5 *

2 from emp

讨论

许多数据库提供了类似FETCH FIRST和LIMIT这样的子句来指定查询结果的行数。Oracle与此不同，你必须使用ROWNUM的函数，该函数会为结果集里的每一行指定一个行号（从1开始，逐渐增大)。

当你使用R0WNUM<=5限定只返回最初的5行数据时，会发生如下的事情。

⑴Oracle执行查询。

(2) Oracle取得第一行数据，并把它的行号定为1。

(3) 已经超过第5行了吗？如果没有，Oracle会返回当前行，因为当前的行号满足小于或等于5这一条件。如果已经超过，那么Oracle就不返回当前行。

(4) Oracle取得下一行数据，并且将行号加1 (得到2，然后得到3，再然后得到4，以此类推）。

(5) 返回第3步。

如上述处理过程所示，Oracle会在取得某一行数据之后再为其编号，这是关键之处。很多 Oracle开发人员试图只获取一行数据，比如指定R0WNUM=5，希望只返回第5行。但是，同时使用ROWNUM和等式条件是不对的。以下是使用R0WNUM=5后实际发生的事情。

(1) Oracle执行査询。

(2) Oracle取得第一行数据，并把它的行号定为1。

(3) 已经到第5行了吗？如果没有，那么Oracle会舍弃这一行，因为它不符合条件。如果是，那么Oracle会返回当前行。但是，行号永远不可能到5 !

(4) Oracle取得下一行数据，并把它的行号定为1。这是因为查询结果的第1行的行号必须是1。

(5) 返回第3步。

深人理解这一过程，你会明白为什么通过指定等式条件R0WNUM=5来获取第5行会失败。如果你不先获取第1行到第4行，第5行从何而来？

你可能会注意到，R0WNUM=1确实能得到第1行，这似乎与上述解释相矛盾。R0WNUM=1运行正常的原因在于，Oracle必须至少尝试一次读取，才能确定表里是否有记录。仔细阅读以上处理过程，用1替换5,你就会理解为什么指定R0WNUM=1作为条件（为了返回一行）会成功。

1.10随机返回若干行记录

问题

你希望从表中获取特定数量的随机记录。修改下面的语句，以便连续执行查询并使结果集含有5行不同的数据。

select enane, job from emp

解决方案

使用数据库的内置函数来随机生成査询结果。在ORDER BY子句里使用该内置函数可以实现查询结果的随机排序。最后要结合1.9节中的技巧从随机排序结果里获取限定数目的行。

DB2

把内置函数RAND和ORDER BY、FETCH结合使用。

1 select enarne,job

2 from enp

3 order by rand() fetch first 5 rows only MySQL

把内置函数RAND和LIMIT、ORDER BY结合使用。

1 select enane,job

2 from enp

3 order by rand() Unit 5 PostgreSQL

把内置函数RANDOM和LIMIT、ORDER BY结合使用。

1 select enarne,job

2 from enp

3 order by random() limit 5

Oracle

在内置包DBMS_RANDOM里可以找到VALUE函数，把该内置函数和ORDER BY、内置函数 ROWNUM结合使用。

1 select *

2 from (

3 select enane, job

4 from enp

6 order by dbrns一random.value()

7 )

8 where rownum <= 5 SQL Server

同时使用内置函数NEWID和TOP、ORDER BY来返回一个随机结果集。

1 select top 5 enane,job

2 from enp

3 order by newid()

3.讨论

ORDER BY子句能够接受一个函数的返回值，并利用该值改变当前结果集的顺序。在本例中，所有查询都是在ORDER BY子句执行结束后才限定返回值的行数。看过Oracle的解决方案后，非Oracle用户可能会受到启发，因为Oracle的解决方案展示了（在理论上）其他数据库内部是如何实现该查询的。

不要误认为ORDER BY子句中的函数是数值常量，这一点很重要。如果ORDER BY子句使用数值常量，315么就需要按照SELECT列表里的顺序来排序。如果ORDER BY子句使用了函数，那么就需要按照该函数的返回值来排序，而函数返回的值是根据结果集里的每一行计算而来的。

1.11 查找NuU值

问题

你想查找特定列的值为Null的所有行。

解决方案

要判断一个值是否为Null,必须使用IS Null。

1 select *

2 from enp

3 where comm is null

讨论

NuU值不会等于或者不等于任何值，甚至不能与其自身作比较。因此，不能使用=或!= 来测试某一列的值是否为NuU。判断一行是否含有Null，必须使用IS Null。你也可以使用IS NOT Null来找到给定列的值不是Null的所有行。

1.12把NuU值转换为实际值

问题

有一些行包含Null值，但是你想在返回结果里将其替换为非Null值。

解决方案

使用COALESCE函数将Null值替代为实际值。

1 select coalesce(conm,0)

2 from enp

讨论

需要为COALESCE函数指定一个或多个参数。该函数会返回参数列表里的第一个非Null值。在本例中，若C0MM不为Null,会返回COMM值，否则返回0。

处理Null值时，最好利用数据库的内置功能。在许多情况下，你会发现有不止一个函数能解决本实例中的问题。COALESCE函数只是恰好适用于所有的数据库。除此之外，CASE也适用于所有数据库。

select case

when conn is not null then comm else 0 end from emp

尽管CASE也能把Null值转换成实际值，但COALESCE函数更方便、更简洁。

1.13查找匹配项

1.问题

你想返回匹配某个特定字符串或模式的行。考虑下面的查询及其结果集。

select enarne, job from enp where deptno in (10,20)

ENAME JOB

SMITH CLERK

JONES MANAGER

CLARK MANAGER

SCOTT ANALYST

KING PRESIDENT

ADAMS CLERK

FORD ANALYST

MILLER CLERK

你想从编号为10和20的两个部门中找到名字中含有字母I或职位以ER结尾的人。

ENAME JOB

SMITH CLERK

JONES MANAGER

CLARK MANAGER

KING PRESIDENT

MILLER CLERK

解决方案

结合使用LIKE运算符和SQL通配符％。

1 select enane, job

2 from emp

3 where deptno in (10,20)

4 and (enane like '%I%' or job like '%ER')

讨论

被用于LIKE模式匹配操作时，运算符％可以匹配任意长度的连续字符。大多数SQL实现也提供了下划线（_)运算符，用于匹配单个字符。通过在字母I前后都加上56,任何（在任意位置）出现I的字符串都会被检索出来。如果没有使用％把检索模式围起来，那么％的位置会影响查询结果。例如，为了找到以ER结尾的职位，就需要在ER的前面加上如果是要找以ER开头的职位，那就应该在ER的后面加上％。

第2章

査询结果排序

本章主要介绍如何使查询结果个性化。如果知道如何控制和修改结果集，你就能提供更具可读性、更有意义的数据。

2.1以指定顺序返回查询结果

1.问题

你想显示部门编号为10的员工的名字、职位和工资，并根据工资从低到高排序。你希望返回如下结果集。

ENAME	JOB	SAL
MILLER	CLERK	1300
CLARK	MANAGER	2450
KING	PRESIDENT	5000

解决方案

使用ORDER BY子句。

1 select enane,job,sal

2 from enp

3 where deptno = 10

4 order by sal asc

讨论

ORDER BY子句可以对结果集排序。本实例针对SAL按照升序排列。默认情况下，ORDER BY 会做升序排列，因此ASC子句是可选项。相应地，也可以通过指定DESC执行降序排列。

select enane,job,sal from enp where deptno = 10 order by sal desc

ENAME JOB SAL

KING PRESIDENT 5000

CLARK MANAGER 2450

MILLER CLERK 1300

你也可以不指定用于排序的列名，而指定一个数值来指代该列。数值从1开始，从左向右匹配SELECT列表里的列，如下所示。

select enane,job,sal from enp where deptno = 10 order by 3 desc

ENAME JOB SAL

KING PRESIDENT 5000

CLARK MANAGER 2450

MILLER CLERK 1300

上述ORDER BY子句里的数字3对应着SELECT列表的第3列，即SAL。

2.2多字段排序

1.问题

针对EMP表的数据，你想先按照DEPTNO升序排列，然后再按照SAL降序排列。你希望返回如下所示的结果集。

EMPN0	DEPTNO	SAL ENAME	JOB
7839	10	5000 KING	PRESIDENT
7782	10	2450 CLARK	MANAGER
7934	10	1300 MILLER	CLERK
7788	20	3000 SCOTT	ANALYST
7902	20	3000 FORD	ANALYST
7566	20	2975 JONES	MANAGER
7876	20	1100 ADAMS	CLERK
7369	20	800 SMITH	CLERK
7698	30	2850 BLAKE	MANAGER
7499	30	1600 ALLEN	SALESMAN
7844	30	1500 TURNER	SALESMAN
7521	30	1250 WARD	SALESMAN
7654	30	1250 MARTIN	SALESMAN
7900	30	950 JAMES	CLERK

2.解决方案

在ORDER BY子句中列出不同的排序列，以逗号分隔。

1 select enpno,deptno,sal,ename,job

2 from enp

3 order by deptno, sal desc 3.讨论

ORDER BY的执行顺序是从左到右的。如果使用SELECT列表项对应的位置序号来指定排序项，那么这个数字序号不能大于SELECT列表里的项目个数。一般而言，你也可以根据一个没有被包含在SELECT列表里的列来排序，但必须明确地指定列名。不过，如果你的查询语句里有GROUP BY或DISTINCT,那么就不能按照SELECT列表之外的列进行排序。

2.3依据子串排序

1.问题

你想按照一个字符串的特定部分排列查询结果。例如，你希望从EMP表检索员工的名字和职位，并且按照职位字段的最后两个字符对检索结果进行排序。结果集应该像下面这样。

ENAME JOB

KING	PRESIDENT
SMITH	CLERK
ADAMS	CLERK
JAMES	CLERK
MILLER	CLERK
JONES	MANAGER
CLARK	MANAGER
BLAKE	MANAGER
ALLEN	SALESMAN
MARTIN	SALESMAN
WARD	SALESMAN
TURNER	SALESMAN
SCOTT	ANALYST
FORD	ANALYST

2.解决方案 DB2、MySQL、

在ORDER BY子句里使用SUBSTR函数。

select enane,job from enp

order by substr(job,length(job)-2)

SQL Server

在ORDER BY子句里使用SUBSTRING函数。

select enane,job from emp

order by substring(job,len(job)-2,2)

3.讨论

利用数据库中的子串函数，你可以很方便地按照一个字符串的任意部分来排序。要想按照一个字符串的最后两个字符排序，需要先找到该字符串的结尾处（即字符串的长度），然
后减去2。这样，起始位置就是该字符串的倒数第2个字符。然后，你就可以截取从指定起始位置开始直到字符串结束的所有字符。SQL Server的SUBSTRING函数略有不同，它要求提供第3个参数来指定需要截取几个字符。对于本实例而言，第3个参数既可以是2，也可以是任何大于2的数字。

2.4 对含有字母和数字的列排序

1.问题

你有混合了字母和数字的数据，希望按照字母部分或者数字部分来排序。考虑如下所示的视图。

create view V as

select enane|I' '||deptno as data from enp

select * from V

DATA

SMITH 20 ALLEN 30 WARD 30 JONES 20 MARTIN 30 BLAKE 30 CLARK 10 SCOTT 20 KING 10 TURNER 30 ADAMS 20 JAMES 30 FORD 20 MILLER 10

你希望以DEPTNO或ENAME作为排序项。若按照DEPTNO排序，会产生如下所示的结果集。 DATA

CLARK 10 KING 10 MILLER 10 SMITH 20 ADAMS 20 FORD 20 SCOTT 20 JONES 20 ALLEN 30 BLAKE 30 MARTIN 30 JAMES 30 TURNER 30 WARD 30

DATA

ADAMS 20 ALLEN 30 BLAKE 30 CLARK 10 FORD 20 JAMES 30 JONES 20 KING 10 MARTIN 30 MILLER 10 SCOTT 20 SMITH 20 TURNER 30 WARD 30

2.解决方案 Oracle 和 PostgreSQL

使用函数REPLACE和TRANSLATE修改用于排序的字符串。

/*按照DEPTNO排序*1

1 select data

2 from V

3 order by replace(data,

4 replace(

5 translate(data,'0123456789•，¹##########',)

/*按照ENAME排序*/

1 select data

2 from ernp

3 order by replace(

4 translate(data,¹0123456789',' ##########

DB2

DB2的隐式类型转换比Oracle和PostgreSQL更严格，因此在创建视图V的时候，要先将 DEPTNO的类型转换为CHAR。这种方法没有创建一个新视图，而是直接使用内嵌视图。DB2 中的REPLACE函数和TRANSLATE函数的使用方式与Oracle和PostgreSQL中的相同，只是 TRANSLATE函数的参数顺序稍有不同。

/*按照DEPTNO排序*/

1 select *

2 from (

3 selectename|丨’ ¹||cast(deptno as char(2)) as data

4 from emp

5 )v

6 order by replace(data,

7 replace(

/*按照ENAME排序*/

1 select *

2 from (

3 selectename|| ■ ¹1|cast(deptno as char(2)) as data

4 fron ernp

5 )v

6 order by replace(

7 translate(data,'##########¹,'0123456789 MySQL 和 SQL Server

这些数据库不支持TRANSLATE函数，因此不能提供针对本问题的解决方案。

3.讨论

使用TRANSLATE函数和REPLACE函数删除每一行的数字或者字符后，就能方便地按照剩余的部分排序。上述示例代码里被传递给ORDER BY的值如下述的结果集所示。（以Oracle 解决方案为例的原因是，这3种数据库使用了同样的技巧，唯一特别之处在于DB2的 TRANSLATE函数的参数顺序略有不同。）

select data,

replace(data,

replace(

translate(data,'0123456789','##########'),#¹,'¹), ") nuns, replace(

translate(data，'0123456789','##########丨），丨#丨，••）chars from V

DATA	NUMS	CHARS
SMITH 20	20	SMITH
ALLEN 30	30	ALLEN
WARD 30	30	WARD
JONES 20	20	JONES
MARTIN 30	30	MARTIN
BLAKE 30	30	BLAKE
CLARK 10	10	CLARK
SCOTT 20	20	SCOTT
KING 10	10	KING
TURNER 30	30	TURNER
ADAMS 20	20	ADAMS
JAMES 30	30	JAMES
FORD 20	20	FORD
MILLER 10	10	MILLER

2.5排序时对NuU值的处理

1.问题

你想按照EMP表的COMM列对査询结果进行排序，但该字段可能为Null。因此，你需要想个办法来指定是否应该将NuU值排到后面。

TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400
SMITH	800
JONES	2975
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000

ENAME

SAL COMM

或者你希望把NuU值放在前面。

ENAME	SAL	COMM
SMITH	800
JONES	2975
CLARK	2450
BLAKE	2850
SCOTT	3000
KING	5000
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
MARTIN	1250	1400
WARD	1250	500
ALLEN	1600	300
TURNER	1500	0

2.解决方案

根据你希望的排序方式（以及你所使用的数据库管理系统如何处理NuU值排序问题），你能够对可能为NuU的列进行升序排列或者降序排列。

1 select enane,sal,conn

2 from enp

3 order by 3

1 select ename,sal,conn

2 from enp

3 order by 3 desc

这个解决方案表明，如果一个可能为Null的列含有非Null值，它们也会相应地被升序排列或降序排列；这与你的直觉可能相反。但是，如果你希望采用与非Null值列不同的方式来排列Null值，例如，你可能想把非Null值以升序排列或降序排列，而把全部NuU值都放到最后面，那么你就要使用CASE表达式来动态调整排序项。

DB2、MySQL、PostgreSQL 和 SQL Server

使用CASE表达式标记NuU值。该标记有两种可能的取值：一种代表NuU值, 表非Null值。一旦你做好了标记，只要简单地把它放进ORDER BY子句就行了。你就能在不影响非Null值的情况下，方便地调整Null值的位置了。 /*非Null值C0MM升序排列，全部NuU值放到最后面*/

1 select enane,sal,comm

2 from (

3 select enane,sal,conn,

4 case when conm is null then 0 else 1 end as is__null

5 from emp

6	)x
7 order by is_		_nulldesc,cornn
ENAME	SAL	COMM
TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400
SMITH	800
JONES	2975
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000

/*非NuU值COMM降序排列，全部NuU值放到最后面*/

1 select enane,sal,conn

2 fron (

3 select enane,sal,conn,

4 case when conm is null then 0 else 1 end as is_null

5 from enp

6 )x

7 order by is_nulldesc,conmdesc

ENAME	SAL	COMM
MARTIN	1250	1400
WARD	1250	500
ALLEN	1600	300
TURNER	1500	0
SMITH	800
JONES	2975
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100

BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000

/*非NuU值COMM升序排列，全部Null值放到最前面*/

1 selectename, sal,conm

2 from (

3 selectename, sal, conn,

4 case when conn is null then 0 else 1 end as

5 from enp

6 )x

7 order by is_null,conn

ENAME	SAL	COMM
SMITH	800
JONES	2975
CLARK	2450
BLAKE	2850
SCOTT	3000
KING	5000
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400

/*非Null值COMM降序排列，全部NuU值放到最前面*/

1 selectename, sal, conn

2 from (

3 selectename, sal, conn,

4 case when conn is null then 0 else 1 end as

5 from enp

6 )x

7 order by is_null,conn desc

ENAME	SAL
SMITH	800
JONES	2975
CLARK	2450
BLAKE	2850
SCOTT	3000
KING	5000
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
MARTIN	1250

WARD	1250	500
ALLEN	1600	300
TURNER	1500	0

Oracle

如果你使用的是Oracle 8/或者更早的版本，可以使用上述针对其他平台的解决方案。如果使用的是Oracle 9/及后续版本，则能使用针对ORDER BY子句的扩展语法NULLS FIRST和 NULLS LAST来决定NuU值应该排到前面还是后面，而无须考虑非Null值的排序方式。

/*非NuU值C0MM升序排列，全部Null值放到最后面*/

1 select 2 from 3 order ENAME	:enane,sal,conm i emp by conm nulls ' SAL COMM
TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400
SMITH	800
JONES	2975
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000

/*非Null值com降序排列，全部Null值放到最后面*/

1 select ename,sal,conn

2 from enp

3 order by conndesc nulls last

ENAME	SAL	COMM
MARTIN	1250	1400
WARD	1250	500
ALLEN	1600	300
TURNER	1500	0
SMITH	800
JONES	2975
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000

1 select enane,sal,comm

2 from emp

3 order by conn nulls first

ENAME	SAL	COMM
SMITH	800
JONES	2975
CLARK	2450
BLAKE	2850
SCOTT	3000
KING	5000
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400

/*非Null值COMM降序排列，全部NuU值放到最前面*/

1 select ename,sal,conn

2 from enp

3 order by conndesc nulls first

ENAME	SAL	COMM
SMITH	800
JONES	2975
CLARK	2450
BLAKE	2850
SCOTT	3000
KING	5000
JAMES	950
MILLER	1300
FORD	3000
ADAMS	1100
MARTIN	1250	1400
WARD	1250	500
ALLEN	1600	300
TURNER	1500	0

3.讨论

除非数据库管理系统提供了一种方式，它能够让你在无须修改非Null值数据的情况下方便地把Null值排到最前面或者最后面（像Oracle那样），否则你就得添加一个辅助列。

一"I在写作本书之时，DB2用户能够在窗口函数OVER子句的ORDER BY里使用 H*, J NULLS FIRST和NULLS LAST,不过该语法不适用于针对整个结果集的ORDER BY

子句。

辅助列（只存在于查询语句里，而不存在于表中）的目的是，让你能够识别出Null值，并控制其排在最前面还是最后面。对于非Oracle解决方案的查询语句，其内嵌视图X会返回如下结果集。

select enane,sal,conn,

case when conn is null then 0 else 1 end as is_null from enp

ENAME	SAL	COMM	IS一NULL
SMITH	800		0
ALLEN	1600	300	1
WARD	1250	500	1
JONES	2975		0
MARTIN	1250	1400	1
BLAKE	2850		0
CLARK	2450
SCOTT	3000
KING	5000
TURNER	1500	0
ADAMS	1100
JAMES	950
FORD	3000
MILLER	1300		0

通过使用IS_NULL返回的值，你就能在不影响COMM排序的情况下，轻而易举地把全部Null 值放到最削面或者最后面。

2.6依据条件逻辑动态调整排序项

1.问题

你希望按照某个条件逻辑来排序。例如，如果JOB等于SALESMAN,就要按照COMM来排序; 否则，按照SAL排序。你希望返回如下所示的结果集。

ENAME SAL JOB COMM

TURNER	1500 SALESMAN	0
ALLEN	1600 SALESMAN	300
WARD	1250 SALESMAN	500
SMITH	800 CLERK
JAMES	950 CLERK
ADAMS	1100 CLERK
MARTIN	1250 SALESMAN	1300
MILLER	1300 CLERK
CLARK	2450 MANAGER
BLAKE	2850 MANAGER
JONES	2975 MANAGER
SCOTT	3000 ANALYST
FORD	3000 ANALYST
KING	5000 PRESIDENT

解决方案在ORDER BY子句里使用CASE表达式。

1 select enarne,sal,job,comm

2 from enp

3 order by case when job = 'SALESMAN' then conn else sal end

讨论可以利用CASE表达式来动态调整结果的排序方式。上述示例代码中传递给ORDER BY的值如下所示。

select enane,sal,job,conm,

case when job = 'SALESMAN' then conn else sal end as ordered from ernp order by 5

ENAME	SAL JOB	COMM	ORDERED
TURNER	1500 SALESMAN	0	0
ALLEN	1600 SALESMAN	300	300
WARD	1250 SALESMAN	500	500
SMITH	800 CLERK		800
JAMES	950 CLERK		950
ADAMS	1100 CLERK		1100
MARTIN	1250 SALESMAN	1300	1300
MILLER	1300 CLERK		1300
CLARK	2450 MANAGER		2450
BLAKE	2850 MANAGER		2850
JONES	2975 MANAGER		2975
SCOTT	3000 ANALYST		3000
FORD	3000 ANALYST		3000
KING	5000 PRESIDENT		5000

第3章

多表査询

本章介绍如何利用连接查询和集合运算来合并多个表中的数据。连接査询是SQL的基础，集合运算也非常重要。为了掌握后续各章介绍的更复杂的查询，你必须首先学习本章中的连接查询和集合运算。

3.1 叠加两个行集

1.问题

你想返回保存在多个表中的数据，理论上需要将一个结果集叠加在另一个之上。这些表可以没有相同的键，但它们的列的数据类型必须相同。例如，你想显示EMP表里部门编号为 10的员工的名字和部门编号，以及DEPT表中各个部门的名称和编号。你希望得到如下所示的结果集。

ENAME_AND_DNAME	DEPTNO
CLARK	10
KING	10
MILLER	10
ACCOUNTING	10
RESEARCH	20
SALES	30
OPERATIONS	40

2.解决方案

使用集合运算UNION ALL合并多个表中的行。

1 select enane as ename一and一dname, deptno

2 from enp

3 where deptno = 10

4 union all

5 select '................... ', null

6 from tl

7 union all

8 select dname, deptno

9 from dept

3.讨论

UNION ALL将多个表中的行并入一个结果集。对于所有的集合运算来说，SELECT列表里的所有项目必须保持数目相同，且数据类型匹配。例如，下面的两个检索都将失败。

select deptno from dept union all select enane from ernp

select deptno, dname from dept union select deptno from enp

尤其需要注意的是，如果有重复项，UNION ALL也将一并纳入。如果你希望过滤掉重复项，可以使用UNION运算符。例如，如果针对EMP.DEPTNO和DEPT.DEPTNO执行UNION操作，就只会返回如下所示的4行数据。

select deptno from enp union select deptno from dept

DEPTNO

使用UNION而不是UNION ALL,则很可能会进行一次排序操作，以便删除重复项。当处理大型结果集的时候要想到这一点。大体而言，使用UNION等同于针对UNION ALL的输出结果再执行一次DISTINCT操作，如下所示。

select distinct deptno from ( select deptno from enp union all select deptno fron dept )

DEPTNO

除非有必要，否则不要在查询中使用DISTINCT操作，同样的规则也适用于UNION。除非有必要，否则不要用UNION代替UNION ALL。

3.2 合并相关行

1.问题

你想根据一个共同的列或者具有相同值的列做连接查询，并返回多个表中的行。例如，你想显示部门编号为10的全部员工的名字及其部门所在地，但这些数据分别存储在两个表里。你希望得到如下所示的结果集。

ENAME L0C

CLARK NEW YORK KING NEW YORK

MILLER NEW YORK

解决方案

通过DEPTNO字段把EMP表和DEPT表连接起来。

1 select e.ename, d.loc

2 from enp e, dept d

3 where e.deptno = d.deptno

4 and e.deptno = 10

讨论

这个解决方案是一个关于连接査询的例子。更准确地说，它是内连接中的相等连接。连接査询是一种把来自两个表的行合并起来的操作。对于相等连接而言，其连接条件依赖于某个相等条件（例如，一个表的部门编号和另一个表的部门编号相等）。内连接是最早的一种连接，它返回的每一行都包含了来自参与连接查询的各个表的数据。

理论上，连接操作首先会依据FROM子句里列出的表生成笛卡儿积（列出所有可能的行组合），如下所示。

select e.enane, d.loc,

e.deptno as enp_deptno, d.deptno as dept_deptno fron enp e, dept d where e.deptno = 10

ENAME	LOC	EMP_DEPTN0 DEPT_	_DEPTN0
CLARK	NEW YORK	10	10
KING	NEW YORK	10	10
MILLER	NEW YORK	10	10
CLARK	DALLAS	10	20
KING	DALLAS	10	20
MILLER	DALLAS	10	20
CLARK	CHICAGO	10	30
KING	CHICAGO	10	30
MILLER	CHICAGO	10	30

CLARK BOSTON 10 40

KING BOSTON 10 40

MILLER BOSTON 10 40

EMP表里部门编号为10的全部员工与DEPT表的所有部门组合都被列出来了。然后，通过 WHERE子句里的e.deptno和d.deptno做连接操作，限定了只有EMP.DEPTNO和DEPT.DEPTNO 相等的行才会被返回。

select e.enane, d.loc,

e.deptno as enp_deptno, d.deptno as dept_deptno from emp e, dept d where e.deptno = d.deptno and e.deptno = 10

ENAME LOC EMP DEPTNO DEPT_DEPTN0

CLARK NEW YORK 10 10

KING NEW YORK 10 10

MILLER NEW YORK 10 10

另一种写法是利用显式的]0IN子句（INNER关键字是可选项）。

select e.ename, d.loc

fron emp e inner join dept d on (e.deptno = d.deptno) where e.deptno = 10

如果你更喜欢在FROM子句里（而不是在WHERE子句里）写明连接逻辑，则可以使用]0IN 子句。这两种风格都符合ANSI标准，本书涉及的关系数据库管理系统的最新版本也都支持它们。

3.3 查找两个表中相同的行

1.问题你想找出两个表中相同的行，但需要连接多列。例如，考虑如下所示的视图V。

create view V as

select enane,job,sal from emp where job = 'CLERK'

select * fron V

ENAME JOB SAL

SMITH CLERK 800

ADAMS CLERK 1100

JAMES CLERK 950

MILLER CLERK 1300

视图v只包含职位是CLERK的员工，但并没有显示EMP表中所有可能的列。你想从EMP表获取与视图V相匹配的全部员工的EMPNO、ENAME、^〕0B、SAL和DEPTNO，并且希望得到如下

所示的结果集。

EMPNO ENAME	JOB	SAL	DEPTNO
7369 SMITH	CLERK	800	20
7876 ADAMS	CLERK	1100	20
7900 JAMES	CLERK	950	30
7934 MILLER	CLERK	1300	10

解决方案

把多个表中所有必要的列都连接起来，以获得正确的结果。也可以使用集合运算 INTERSECT来替代连接查询，并返回两个表的交集（相同的行)。

MySQL 和 SQL Server

使用多个条件把EMP表和视图V连接起来。

1 select e.empno,e.ename,e.job,e.sal,e.deptno

2 from enp e, V

3 where e.ename = v.enane

4 and e.job = v.job

5 and e.sal = v.sal

除此之外，也可以使用JOIN子句执行同样的连接查询。

1 select e.enpno,e.ename,e.job,e.sal,e.deptno

2 from emp e join V

3 on ( e.enane = v.enarne

4 and e.job = v.job

5 and e.sal = v.sal )

DB2、Oracle 和 PostgreSQL

针对MySQL和SQL Server的解决方案也适用于DB2、Oracle和PostgreSQL。如果你希望从视图V查询数据，就需要使用该方案。

如果你不需要检索视图V的某些列，可以使用集合运算INTERSECT和谓词IN。

1 select enpno,ename,job,sal,deptno

2 from enp

3 where (ename,job,sal) in (

4 select ename,job,sal from emp

5 intersect

6 select ename,job,sal fron V

7 )

讨论

当执行连接查询时，为了得到正确的结果，必须慎重考虑要把哪些列作为连接项。当参与连接的行集里的某些列可能有共同值，而其他列有不同值的时候，这一点尤为重要。

集合运算INTERSECT会返回两个行集的相同部分。在使用INTERSECT时，必须保证两个表里参与比较的项目数目是相同的，并且数据类型也是相同的。注意，当执行集合运算时，默认不会返回重复项。

3.4 查找只存在于一个表中的数据

1 .问题

你希望从一个表（可以称之为源表）里找出那些在某个目标表里不存在的值。例如，你想找出在DEPT表中存在而在EMP表里却不存在的部门编号（如果有的话）。在示例数据中， DEPT表里DEPTNO为⁴0的数据并不存在于EMP表里，因此结果集应该如下所示。

DEPTNO

解决方案

计算差集的函数对解决本问题尤其有用。DB2、PostgreSQL和Oracle支持差集运算。如果你所使用的数据库管理系统没有提供差集函数，那么就要采用MySQL和SQL Server解决方案介绍的子查询技巧。

DB2 和 PostgreSQL

使用集合运算EXCEPT。

1 select deptno from dept

2 except

3 select deptno from ernp Oracle

使用集合运算MINUS。

1 select deptno from dept

2 minus

3 select deptno from enp MySQL 和 SQL Server

使用子查询得到EMP表中所有的DEPTNO,并将该结果传入外层査询，然后外层查询会检索 DEPT表，找出没有出现在子査询结果里的DEPTNO值。

1 select deptno

2 from dept

3 where deptno not in (select deptno from enp)

讨论

DB2 和 PostgreSQL

DB2和PostgreSQL提供的内置函数使得该操作非常简单。EXCEPT运算符获取第一个结果集的数据，然后从中删除第二个结果集的数据。这种运算非常像减法。

包括EXCEPT在内的集合运算符在使用上都有一些限制条件。参与运算的两个SELECT列表要有相同的数据类型和值个数。而且，EXCEPT不返回重复项；并且NuU值不会产生问题，这与NOT IN子查询不同（参考对MySQL和SQL Server的讨论）。EXCEPT运算符会返回只存在于第一个査询（EXCEPT前面的查询）结果里而不存在于第二个査询（EXCEPT后面的查询）结果里的行。

Oracle

Oracle解决方案除了集合运算符叫作MINUS而不是EXCEPT，其他方面与DB2和PostgreSQL 的解决方案相同。另外，上述解释也适用于Oracle。

MySQL 和 SQL Server

这个子查询会获取EMP表中所有的DEPTNO。外层查询会返回DEPT表中“不存在于”或“未被包含在”子査询结果集里的所有的DEPTNO值。

当你使用MySQL和SQL Server的解决方案时，需要考虑排除重复项。其他数据库基于 EXCEPT或者MINUS的解决方案已经从结果集中排除了重复的行，确保每个DEPTNO只出现一次。当然，之所以能这样做，是因为示例数据中的DEPTNO是表的主键。如果DEPTNO 不是主键，你可以使用DISTINCT来确保每个在EMP表里缺少的DEPTNO值只出现一次，

如下所示。

select distinct deptno from dept

where deptno not in (select deptno from ernp)

在使用NOT IN时，要注意NuU值。考虑如下的表NEW_DEPT。

create table new_dept(deptno integer) insert into new一dept values (10) insert into new一dept values (50) insert into new一dept values (null)

如果你试着使用NOT IN子査询检索存在于DEPT表却不存在于NEW_DEPT表的DEPTNO，会发

现査不到任何值。

select * from dept

where deptno not in (select deptno from new一dept)

DEPTNO为20、30和40的数据虽然不在NEW_DEPT表中，却没被上述查询检索到。原因就在于NEW_DEPT表里有Null值。子查询会返回3行DEPTNO,分别为10、50和Null值。IN和 NOT IN本质上是OR运算，由于Null值参与0R逻辑运算的方式不同，IN和NOT IN将会产生不同的结果。考虑以下分别使用IN和0R的例子。

select deptno fron dept where deptno in ( 10,50,null )

DEPTNO

select deptno from dept

where (deptno=10 or deptno=50 or deptno=null)

DEPTNO

再来看看使用NOT IN和NOT OR的例子。

select deptno from dept where deptno not in ( 10,50,null )

(no rows )

select deptno from dept

where not (deptno=10 or deptno=50 or deptno=null)

(no rows )

如你所见，条件DEPTNO NOT IN (10, 50, NULL)等价于： not (deptno=10 or deptno=50 or deptno=null)

对于DEPTNO是S0的情况，下面是这个表达式的展开过程。

not (deptno=10 or deptno=50 or deptno=nu"ll)

(false or false or null)

(false or null) null

在SQL中，TRUE or NULL的运算结果是TRUE,但FALSE or NULL的运算结果却是Null ! — 旦混入了 NuU,结果就会一直保持为NuU (除非你使用实例1.11介绍的技巧特意测试是否含有Null)。必须谨记，当使用IN谓词以及当执行0R逻辑运算的时候，你要想到是否会涉及Null值。

为了避免NOT IN和Null值带来的问题，需要结合使用NOT EXISTS和关联子査询。关联子查询指的是外层查询执行后获得的结果集会被内层子査询引用。下面的例子给出了一个免受NuU值影响的替代方案（回到“问题”部分给出的那个原始査询语句）。

select d.deptno fron dept d where not exists ( select null from enp e where d.deptno = e.deptno )

DEPTNO

上述查询语句遍历并评估dept表的每一行。针对每一行，会有如下操作。

(1) 执行子査询并检查当前的部门编号是否存在于EMP表。要注意关联条件D.DEPTNO = E.DEPTNO,它通过部门编号把两个表连接起来。

(2) 如果子査询有结果返回给外层査询，那么EXISTS (■•_)的评估结果是TRUE,这样NOT EXISTS (...)就是FALSE,如此一来，外层查询就会舍弃当前行。

(3) 如果子查询没有返回任何结果，那么NOT EXISTS (...)的评估结果是TRUE,由此外层査询就会返回当前行（因为它是一个不存在于EMP表中的部门编号)。

把EXISTS/NOT EXISTS和关联子查询一起使用时，SELECT列表里的项目并不重要，因此我在这个例子中用了 SELECT NULL,这是为了让你把注意力放到子查询的连接操作上，而非 SELECT列表的项目上。

3.5从一个表检索与另一个表不相关的行

1.问题

两个表有相同的键，你想在一个表里查找与另一个表不相匹配的行。例如，你想找出哪些部门没有员工。结果集如下所示。

DEPTNO DNAME LOC

40 OPERATIONS BOSTON

如果想要找到每一个员工就职的部门，需要基于EMP表和DEPT表的DEPTNO列进行相等连接查询。DEPTNO是两个表都有的列。不幸的是，相等连接无法找到哪些部门没有员工。这是因为，针对EMP表和DEPT表做相等连接操作，将返回满足连接条件的所有行。相反，你只想从DEPT表里找出那些不满足连接条件的行。

本问题乍看起来和前一个实例相同，但其实它们之间有微妙的差别。不同之处在于，前一个实例仅仅返回了没有出现在EMP表中的部门编号。然而，本实例可以很方便地从DEPT表中获取其他列。

解决方案

基于共同列把两个表连接起来，返回一个表的所有行，不论这些行在另一个表里是否存在匹配行。然后，只保留那些不匹配的行即可。

DB2、MySQL、PostgreSQL 和 SQL Server

使用外连接并过滤掉Null值（关键字OUTER是可选的）。

1 select d.*

2 from dept d left outer join enp e

3 on (d.deptno = e.deptno)

4 where e.deptno is null

Oracle

对于Oracle 9/及其后续版本，上述解决方案仍然适用。当然，你也可以使用Oracle专有的外连接语法。

1 select d.*

2 from dept d, emp e

3 where d.deptno = e.deptno (+)

4 and e.deptno is null

Oracle 8/数据库及更早的版本只能使用上述专有语法（注意，圆括号里是+)来完成外连接操作。

这个解决方案使用了外连接，并且只保留不匹配的行。这种操作有时候被称为反连接（anti- join)。为了更好地理解反连接，我们先来看一下没有过滤掉Null值的结果集。

select e.enane, e.deptno as enp_deptno, d.* fron dept d left join enp e on (d.deptno = e.deptno)

ENAME	EMP—DEPTNO	DEPTNO DNAME	LOC
SMITH	20	20 RESEARCH	DALLAS
ALLEN	30	30 SALE	CHICAGO
WARD	30	30 SALES	CHICAGO
JONES	20	20 RESEARCH	DALLAS
MARTIN	30	30 SALES	CHICAGO
BLAKE	30	30 SALES	CHICAGO
CLARK	10	10 ACCOUNTING	NEW YORK
SCOTT	20	20 RESEARCH	DALLAS
KING	10	10 ACCOUNTING	NEW YORK
TURNER	30	30 SALES	CHICAGO
ADAMS	20	20 RESEARCH	DALLAS
3AMES	30	30 SALES	CHICAGO
FORD	20	20 RESEARCH	DALLAS
MILLER	10	10 ACCOUNTING	NEW YORK
		40 OPERATIONS	BOSTON

注意，最后一行的EMP.ENAME和EMP_DEPTNO都是Null值。这是因为没有员工在编号为40 的部门工作。该解决方案使用WHERE子句，只保留了 EMP_DEPTN0是Null值的行（这样只留下DEPT表中无法与EMP表相匹配的行)。

3.6新增连接查询而不影响其他连接查询

1.问题

你已经有了一个查询语句，它可以返回你想要的数据。你需要一些额外信息，但当你试图获取这些信息的时候，却丢失了原有的查询结果集中的数据。例如，你想査找所有员工的信息，包括他们所在部门的位置，以及他们收到奖金的日期。针对这个问题，EMP_B0NUS 表包含了如下数据。

select * from emp_bonus

EMPNO RECEIVED TYPE

7369 14-MAR-2005 1

7900 14-MAR-2005 2

7788 14-MAR-2005 3

最初，你使用如下所示的查询语句。

select e.enane, d.loc from enp e, dept d where e.deptno=d.deptno

ENAME	LOC
SMITH	DALLAS
ALLEN	CHICAGO
WARD	CHICAGO
JONES	DALLAS
MARTIN	CHICAGO
BLAKE	CHICAGO
CLARK	NEW YORK
SCOTT	DALLAS
KING	NEW YORK
TURNER	CHICAGO
ADAMS	DALLAS
JAMES	CHICAGO
FORD	DALLAS
MILLER	NEW YORK

对于有奖金的员工，你希望把他们收到奖金的日期也添加到结果集里，但连接了 EMP_ BONUS表后得到的行数却比预期的要少，因为并非所有的员工都有奖金。

select e.enane, d.loc,eb.received from emp e, dept d, enp_bonus eb where e.deptno=d.deptno and e.enpno=eb.enpno

ENAME	LOC	RECEIVED
SCOTT	DALLAS	14-MAR-2005
SMITH	DALLAS	14-MAR-2005
JAMES	CHICAGO	14-MAR-2005
:希望得到如下所示的结果集。
ENAME	LOC	RECEIVED
ALLEN	CHICAGO
WARD	CHICAGO
MARTIN	CHICAGO
JAMES	CHICAGO	14-MAR-2005
TURNER	CHICAGO
BLAKE	CHICAGO
SMITH	DALLAS	14-MAR-2005
FORD	DALLAS
ADAMS	DALLAS
JONES	DALLAS
SCOTT	DALLAS	14-MAR-2005
CLARK	NEW YORK
KING	NEW YORK
MILLER	NEW YORK

2.解决方案使用外连接既能够获得额外信息，又不会丢失原有的信息。首先连接EMP表和DEPT表，得到全部员工和他们所在部门的位置。然后外连接EMP_B0NUS表，如果某个员工有奖金，则

检索其收到奖金的日期。下面是DB2、MySQL、PostgreSQL以及SQL Server的查询语法。

1 select e.enane, d.loc, eb.received

2 from enp e join dept d

3 on (e.deptno=d.deptno)

4 left join emp_bonus eb

5 on (e.enpno=eb.enpno)

6 order by 2

对于Oracle 9/数据库及其后续版本，上述解决方案仍然适用。除此之外，对于Oracle 8/数据库及更早的版本，可以使用Oracle专有的外连接语法。

1 select e.enane, d.loc, eb.received

2 from emp e, dept d, emp_bonus eb

3 where e.deptno=d.deptno

4 and e.ernpno=eb.enpno (+)

5 order by 2

也可以使用标量子查询（即把子查询放置在SELECT列表里）来模仿外连接操作。

1 select e.enane, d.loc,

2 (select eb.received from emp一bonus eb

3 where eb.ernpno=e.enpno) as received

4 from ernp e, dept d

5 where e.deptno=d.deptno

6 order by 2

标量子査询解决方案适用于所有数据库。

讨论

外连接查询会返回一个表中的所有行，以及另一个表中与之匹配的行。上一个实例中也出现了这种连接操作。外连接之所以能够解决本问题，是因为它不会过滤掉任何应该被返回的行。上述外连接查询返回的行数和没有外连接时一样多。而且，如果有收到奖金的日期，它也会返回那个日期。

使用标量子查询是解决本问题的一种巧妙做法，因为不需要修改主查询中正确的连接操作。在不破坏当前结果集的情况下，标量子查询是为现有查询语句添加额外数据的好办法。当使用标量子查询时，必须确保它们返回的是标量值（单值）。如果SELECT列表里的子査询返回多行，那么查询将会出错。

参考资料

关于如何解决SELECT列表里的子查询不能返回多行数据的问题，参见14.10节。

3.7 确定两个表是否有相同的数据

1.问题

你想知道两个表或两个视图里是否有相同的数据（行数和值)。考虑如下所示的视图。

create view V as

select * from emp where deptno != 10

union all

select * from enp where ename = 'WARD' select * from V

EMPNO ENAME		JOB	MGR HIREDATE		SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-1980	800		20
7499	ALLEN	SALESMAN	7698	20-FEB-1981	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-1981	1250	500	30
7566	JONES	MANAGER	7839	02-APR-1981	2975		20
7654	MARTIN	SALESMAN	7698	28-SEP-1981	1250	1300	30
7698	BLAKE	MANAGER	7839	01-MAV-1981	2850		30
7788	SCOTT	ANALYST	7566	09-DEC-1982	3000		20
7844	TURNER	SALESMAN	7698	08-SEP-1981	1500	0	30
7876	ADAMS	CLERK	7788	12-3AN-1983	1100		20
7900	JAMES	CLERK	7698	03-DEC-1981	950		30
7902	FORD	ANALYST	7566	03-DEC-1981	3000		20
7521	WARD	SALESMAN	7698	22-FEB-1981	1250	500	30

你希望确定该视图是否和EMP表有完全相同的数据。与员工WARD相关的数据有两行，这表明相应的解决方案不仅要找出来不同的数据，还要找到重复的数据。根据EMP表的数据，二者的不同之处包括3行部门编号为10的数据以及两行员工WARD的数据。你希望返回如下所示的结果集。

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	CNT
7521	WARD	SALESMAN	7698	22-FEB-1981	1250	500	30	1
7521	WARD	SALESMAN	7698	22-FEB-1981	1250	500	30	2
7782	CLARK	MANAGER	7839	09-JUN-1981	2450		10	1
7839	KING	PRESIDENT		17-N0V-1981	5000		10	1
7934	MILLER	CLERK	7782	23-JAN-1982	1300		10	1

2.解决方案

使用求差集的函数（MINUS或EXCEPT,这取决于你使用的数据库管理系统）可以很容易地比较表中的数据。如果你所使用的数据库管理系统没有提供类似功能，则可以使用关联子査询。

DB2 和 PostgreSQL

使用集合运算EXCEPT和UNION ALL找出视图V和EMP表的不同之处。

2	select	enpno,ename,job,mgr,hiredate,sal,conn,deptno,
3		count(*) as cnt
4	f厂on	V
5	group	by enpno,ename,job,mgr,hiredate,sal,conn,deptno
6	except
7	select	enpno,ename, job,mgr,hiredate, sal, conn,deptno,
8		count(*) as cnt
9	f厂on	enp
10	group	by enpno,enane,job,mgr,hiredate,sal,comm,deptno
11	)
12	union	all

empno,ename,job,mgr,hiredate,sal,comm,deptno,

count(*) as cnt

enp

by ernpno,enarne,job,mgr,hiredate,sal,comm,deptno

enpno,enane,job,mgr,hiredate,sal,comm,deptno, count(*) as cnt

by empno,ename, job，mgr,hiredate,sal,conn,deptno

enpno,enane,job,mgr,hiredate,sal,comm,deptno, count(*) as cnt

by empno,enane,job,mgr,hiredate,sal,comm,deptno

empno,enane,job,mgr,hiredate,sal,conn,deptno,

count(*) as cnt

enp

by empno,enane,job,mgr,hiredate,sal,comm,deptno

empno,ename,job,mgr,hiredate,sal,comm,deptno,

count(*) as cnt

enp

by empno,enane,job,mgr,hiredate,sal,comm,deptno

enpno,enane,job,mgr,hiredate,sal,comm,deptno, count(*) as cnt v

by enpno,enane,job,mgr,hiredate,sal,conn,deptno

MySQL 和 SQL Server

使用关联子查询和UNION ALL找出那些存在于视图V而不存在于EMP表的数据，以及存在于EMP表而不存在于视图V的数据，并将它们合并起来。

1	select
2	f厂om	(
3	select	e.enpno,e.enane,e•job，e•mgr,e•hiredate,
4		e.sal,e.conn,e.deptno, count(*) as cnt
5	from	enp e
6	group	by enpno,enane,job,mgr,hiredate,
7		sal,conn,deptno
8		)e
9	whe「e	not exists (
10	select	null
11	f厂om	(

12	select	v.ernpno,v.enarne,v. job, v. mgr, v. hiredate,
13		v.sal,v.comm,v.deptno, count(*) as cnt
14	f厂on	V
15	group	by enpno,ename,job,mgr,hiredate,
16		sal,conn,deptno
17		)v
18	where	v.enpno = e.enpno
19	and	v.ename = e.ename
20	and	v.job = e.job
21	and	v.mgr = e.mgr
22	and	v.hiredate = e.hiredate
23	and	v.sal = e.sal
24	and	v.deptno = e.deptno
25	and	v.cnt = e.cnt
26	and	coalesce(v.comn,0) = coalesce(e.comm,0)
27	)
28	union	all
29	select	•k
30	from	(
31	select	v.enpno,v.enane,v.job,v.ngr,v.hiredate,
32		v.sal,v.conn,v.deptno, count(*) as cnt
33	from	V
34	group	by enpno,ename,job,mgr,hiredate,
35		sal,conn,deptno
36		)v
37	where	not exists (
38	select	null
39	from	(
40	select	e.enpno,e.enane,e.job,e.mgr,e.hiredate,
41		e.sal,e.conm,e.deptno, count(*) as cnt
42	f厂om	enp e
43	group	by enpno,enane, job,mgr,hiredate,
44		sal,conn,deptno
45		)e
46	where	v.empno = e.enpno
47	and	v. enane = e. enarne
48	and	v.job = e.job
49	and	v.mgr = e.mgr
50	and	v.hiredate = e.hiredate
51	and	v.sal = e.sal
52	and	v.deptno = e.deptno
53	and	v.cnt = e.cnt
54	and	coalesce(v.conn,0) = coalesce(e.comm,0)
55 3.讨论	)

尽管使用了不同的方法，但上述解决方案的原理并无差别。 (1)首先，找出存在于EMP表而不存在于视图V的行；

(2) 然后与存在于视图v而不存在于EMP表的行合并（UNION ALL)。

如果两个表完全相同，则不会返回任何数据。如果两个表有不同之处，那么将返回那些不同的行。在比较两个表的时候，比较容易的做法是，在比较数据之前先单独比较行数。下面是一个行数比较的简单示例，适用于所有数据库管理系统。

select	count(*)
from	enp
union
select	count(*)
from	dept
C0UNT(*)

因为UNION子句会过滤掉重复项，所以如果两个表的行数相同，则只会返回一行数据。本例中返回了两行数据，这说明两个表中没有完全相同的数据。

DB2、Oracle 和 PostgreSQL

MINUS和EXCEPT的作用相同，所以这里只讨论EXCEPT。UNION ALL前后的两个查询语句非常相似。因此，为了说明这个解决方案的原理，我们将直接执行位于UNION ALL前面的那个查询。执行第1行至第11行后产生的结果集如下所示。

(

select enpno,enane,job,ngr,hiredate,sal,conn,deptno, count(*) as cnt from V

group by enpno,enane,job,ngr,hiredate,sal,conn,deptno except

select enpno,enane,job,ngr,hiredate,sal,conn,deptno, count(*) as cnt fron enp

group by enpno,enane,job,ngr,hiredate,sal,conn,deptno

)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CNT

7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30 2

上述结果集显示从视图v中查询到了一行数据，该行数据要么不存在于EMP表，要么它在视图V中出现的次数与EMP表中的不一致。对于本例而言，查询找到了员工WARD的重复行。如果你仍然不理解该结果集是如何产生的，可以分别执行位于EXCEPT前后的两个查询。你会发现，两个结果集的不同之处仅仅在于视图V中员工WARD相关行的CNT值。

位于UNION ALL后面的查询语句执行了和UNION ALL前面的查询相反的操作。该查询找出了那些存在于EMP表而不存在于视图V的行。

(

select enpno,enane,job,ngr,hiredate,sal,conn,deptno, count(*) as cnt fron enp

group by enpno,enarne,job,mgr,hiredate,sal,conn,deptno minus

select enpno,enane,job,ngr,hiredate,sal,conn,deptno, count(*) as cnt from v

group by enpno,enane,job,ngr,hiredate,sal,conn,deptno

EMPN0	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	CNT
7521	WARD	SALESMAN	7698	22-FEB-1981	1250	500	30	1
7782	CLARK	MANAGER	7839	09-DUN-1981	2450		10	1
7839	KING	PRESIDENT		17-N0V-1981	5000		10	1
7934	MILLER	CLERK	7782	23-JAN-1982	1300		10	1

上述两个结果集通过UNION ALL合并后即可得到最终的结果集。

MySQL 和 SQL Server

位于UNION ALL前后的两个查询语句非常相似。为了理解基于子查询的解决方案，我们直接执行UNION ALL前面的查询。下面的查询是第1行至第27行的内容。

select * fron (

select e•enpno,e.enane,e.job,e.ngr,e.hiredate, e.sal,e.conn,e.deptno, count(*) as cnt from enp e group by enpno,enane,job,mgr,hiredate, sal,comm,deptno

where not exists ( select null from (

select v•enpno，v•ename,v.job,v.mgr,v.hiredate, v.sal,v.conn,v.deptno, count(*) as cnt from v

group by enpno,ename,job,mgr,hiredate, sal,conn,deptno

where v.enpno = e.enpno

and v.enane = e.enane

and v.job = e.job

and v.ngr = e.ngr

and v.hiredate = e.hiredate

and v.sal = e.sal

and v.deptno = e.deptno

and v.cnt = e.cnt

and coalesce(v.conn,0) = coalesce(e.conn,0)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	CNT
7521	WARD	SALESMAN	7698	22-FEB-1981	1250	500	30	1
7782	CLARK	MANAGER	7839	09-JUN-1981	2450		10	1
7839	KING	PRESIDENT		17-NOV-1981	5000		10	1
7934	MILLER	CLERK	7782	23-JAN-1982	1300		10	1

注意，这里比较的不是EMP表和视图V，而是内嵌视图E和内嵌视图V。计算出每一行数据出现的次数，并作为查询结果的一列返回。我们要比较每一行的数据及其出现的次数。如果你还是不理解比较操作是如何执行的，不妨单独执行两个子査询。下一步是找出存在于内嵌视图E而不存在于内嵌视图V的所有行（包括CNT)。该操作使用了关联子查询和NOT

EXISTS。连接查询将确定哪些行是相同的，NOT EXISTS则筛选出内嵌视图E中与连接査询结果不匹配的行。UNION ALL后面的查询语句做了相反的操作，它找出了所有存在于内嵌视图V而不存在于内嵌视图E的行。

select * from (

select v.enpno,v.ename,v•job,v•mgr,v•hiredate, v.sal,v.comm,v.deptno, count(*) as cnt from v

group by enpno,enane,job,ngr,hiredate, sal,comm,deptno

where not exists ( select null from (

select e•enpno,e•enane,e•job,e. mgr,e•hiredate, e.sal,e.comn,e.deptno, count(*) as cnt from enp e

group by enpno,enane,job,ngr,hiredate, sal,comm,deptno

where	V.	enpno	=e.	enpno
and	V.	enane	=e.	enane
and	V.	job	=e.job
and	V.	ngr	=e.	ngr
and	V.	hiredate	=e.	hiredate
and	v.sal		=e.	sal
and	V.	deptno	=e.	deptno
and	v.cnt		=e.	cnt
and	coalesce(v.		.conn,0) = co;

EMPNO ENAME

JOB

MGR HIREDATE

7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30 2

最后，使用UNION ALL合并两个结果集，即可得到最终的结果集。

Ales Spetic 和 Jonathan Gennick 在 Transact-SQL Cookbook 一书中给出了另一种解决方案。请参考这本书第2章“Comparing Two Sets for Equality” 一节。

3.8识别并消除笛卡儿积

1.问题

你想找出部门编号为10的所有员工的名字及其部门所在的城市。下面的查询返回的数据是错误的。

select e.enane, d.loc from enp e, dept d where e.deptno = 10

CLARK	NEW YORK
CLARK	DALLAS
CLARK	CHICAGO
CLARK	BOSTON
KING	NEW YORK
KING	DALLAS
KING	CHICAGO
KING	BOSTON
MILLER	NEW YORK
MILLER	DALLAS
MILLER	CHICAGO
MILLER	BOSTON

正确的结果集如下所示。

ENAME LOC

CLARK NEW YORK

KING NEW YORK

MILLER NEW YORK

解决方案

在FROM子句里对两个表执行连接查询，以得到正确的结果集。

1 select e.enane, d.loc

2 from enp e, dept d

3 where e.deptno = 10

4 and d.deptno = e.deptno

讨论

先看一下DEPT表的数据。 select * fron dept

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

我们看到，编号为10的部门位于纽约，因此如果查询结果不是纽约，那就出错了。上述那个错误的查询语句返回的结果行数是FROM子句里两个表的行数的乘积。对于该查询而言，依据EMP表的部门编号等于10这一过滤条件，将产生3行结果。但是，由于没有对 DEPT表做条件过滤，因此DEPT表中的全部4行数据都将被返回。3乘以4等于12,因此上述错误的查询语句会返回12行数据。为了消除笛卡儿积，我们通常会用到《_1法则，其中n代表FROM子句里表的个数，则代表消除笛卡儿积所必需的连接查询的最少次数。依据表里有什么样的键以及基于哪些列来实现表之间的连接操作，有时候必要的连接查询次数可能会超过次，但是当我们编写查询语句的时候，《-1法则仍然是一个很好的指导原则。

若使用得当，笛卡儿积会很有用。这一方法被广泛运用于多种查询中。笛卡儿积常用于变换或展开（以及合并）结果集，生成一系列的值，以及模拟 loop循环。

3.9组合使用连接查询与聚合函数

1.问题

你想执行一个聚合操作，但查询语句涉及多个表。你希望确保表之间的连接查询不会干扰聚合操作。例如，你希望计算部门编号为10的员工的工资总额以及奖金总和。因为有部分员工多次获得奖金，所以在EMP表和EMP_BONUS表连接之后再执行聚合函数SUM,就会得出错误的计算结果。在这个问题中，EMP_B0NUS表里有如下数据。

select * from enp_bonus

EMPNO RECEIVED TYPE

7934 17-MAR-2005 1

7934 15-FEB-2005 2

7839 15-FEB-2005 3

7782 15-FEB-2005 1

现在，考虑下面的查询语句，它返回了部门编号为10的所有员工的工资和奖金。BONUS表中的TYPE列决定了奖金的数额。若TYPE值等于1，则奖金为工资的10%;若TYPE值等于

2,则奖金为工资的20%;若TYPE值等于3,则奖金为工资的30%。

select e.enpno, e.enane, e.sal, e.deptno,

e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from enp e, emp_bonus eb where e.enpno = eb.enpno and e.deptno = 10

EMPNO ENAME	SAL	DEPTNO	BONUS
7934 MILLER	1300	10	130
7934 MILLER	1300	10	260
7839 KING	5000	10	1500
7782 CLARK	2450	10	245

到目前为止，一切都很顺利。然而，如果你试图连接EMP—BONUS表并计算奖金总和，就会出错。

select deptno，

sun(sal) as total_sal, sun(bonus) as total_bonus

DEPTNO TOTAL SAL T0TAL_B0NUS

10050

尽管奖金总额（T0TAL_B0NUS)是正确的，但工资总额（TOTALJAL)却是错误的。部门编号为10的所有员工的工资总额应该是8750，如下所示。 select sun(sal) from enp where deptno=10 SUM(SAL)

8750

为什么工资总额不对呢？这是因为连接查询导致某些行的SAL列出现了两次。考虑下面连接EMP表和EMP_B0NUS表的查询语句。

select e.enane, e.sal

fron enp e, enp_bonus eb where e.enpno = eb.empno and e.deptno = 10

ENAME

CLARK

KING

MILLER

2450

5000

1300

现在就能很容易地看出来为什么工资总额是错误的了，因为MILLER的工资被统计了两次。你真正想要的结果集应该如下所示。

DEPTNO TOTAL SAL TOTAL BONUS

8750

2.解决方案在连接查询里进行聚合运算时，必须十分小心才行。如果连接查询产生了重复行，通常有两种办法来使用聚合函数，而且可以避免得出错误的计算结果。一种方法是，调用聚合函

数时直接使用关键字distinct,这样每个值都会先去掉重复项再参与计算；另一种方法是，在进行连接查询之前先执行聚合运算（以内嵌视图的方式），这样可以避免错误的结果，因为聚合运算发生在连接查询之前。下面的解决方案使用了 DISTINCT。之后，我们将讨论在连接查询之前使用内嵌视图执行聚合运算的做法。

MySQL 和 PostgreSQL

使用DISTINCT计算工资总额。

DB2、Oracle 和 SQL Server

上述解决方案也适用于这些数据库。

1 select distinct deptno,total一sal,total—bonus

2 fron (

3 select e.ernpno,

4 e.enane,

5 sun(distinct e.sal) over

6 (partition by e.deptno) as total一sal,

7 e.deptno,

8 sun(e.sal*case when eb.type = 1 then .1

9 when eb.type = 2 then .2

0 else .3 end) over

1 (partition by deptno) as total_bonus

2 fron enp e, enp_bonus eb

3 where e.ernpno = eb.empno

4 and e.deptno = 10

5 ) x

3.讨论

MySQL 和 PostgreSQL

本实例“问题”部分的第二个査询语句把EMP表和EMP_B0NUS表连接起来，并返回了员工 MILLER的两行数据，这是导致EMP表的工资总额出错的原因（MILLER的工资被加了两次)。对应的解决办法是只计算不同的EMP.SAL值。下面的查询语句是另一种解决方案。首先计算部门编号为10的全部员工的工资总额，然后连接EMP表和EMP_B0NUS表。下面的査

询语句适用于所有的关系数据库管理系统。

deptno, sum(sal) as total一sal enp

deptno = 10 by deptno

e.deptno = d.deptno e.empno = eb.enpno by d.deptno,d.total_

sal

DEPTNO T0TAL_SAL T0TAL_B0NUS

2135

DB2, Oracle 和 SQL Server

上面的另一种解决方案利用了窗口函数SUM OVER。下面的查询语句来自该解决方案的第3 行至第14行，返回的结果集如下。

select e.enpno, e.enane,

sun(distinct e.sal) over

(partition by e.deptno) as total_sal,

e.deptno,

sum(e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end) over (partition by deptno) as total_bonus from enp e, enp_bonus eb where e.empno = eb.empno and e.deptno = 10

EMPN0	ENAME	T0TAL—SAL	DEPTNO TOTAL,	_B0NUS
7934	MILLER	8750	1G	2135
7934	MILLER	8750	10	2135
7782	CLARK	8750	10	2135
7839	KING	8750	10	2135

窗口函数SUM OVER被调用了两次，第一次调用针对指定的分区或者分组计算工资总额。在本例中，分区指的是编号为10的部门，该部门员工的工资总额是8750。第二次调用SUM OVER针对同一个分区计算奖金总额。最终的结果集则是在去除了 T0TAL_SAL、DEPTNO以及 T0TAL_B0NUS组合的重复项之后产生的。

3.10组合使用外连接查询与聚合函数

1.问题

本节的问题和3.9节的大致相同，只是略微修改了 EMP_B0NUS表的数据，使得部门编号为 10的员工中只有部分人获得了奖金。考虑如下所示的EMP_B0NUS表和査询语句，该查询 (表面上）计算出了部门编号为10的员工的工资总额和奖金总额。 select * from emp_bonus

EMPNO RECEIVED TYPE

7934 17-MAR-2005 1

7934 15-FEB-2O05 2

select deptno,

sun(sal) as total一sal, sun(bonus) as total_bonus from ( select e.enpno, e.enane, e.sal, e.deptno,

e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from emp e, enp_bonus eb where e.enpno = eb.empno and e.deptno = 10 )

group by deptno

DEPTNO T0TAL_SAL T0TAL_B0NUS

10 2600 390

奖金总额的结果是正确的，但工资总额却不是部门编号为10的员工的工资总额。下面的查询语句解释了为什么工资总额不正确。

select e.enpno, e.enane, e.sal, e.deptno,

e.sal*case when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from emp e, enp_bonus eb where e.enpno = eb.enpno and e.deptno = 10

EMPNO ENAME	SAL	DEPTNO	BONUS
7934 MILLER	1300	10	130
7934 MILLER	1300	10	260

上述查询没有计算部门编号为10的全部员工的工资总额，实际上只有MILLER的工资被计入总和，而且被错误地计算了两次。其实，你最终想得到如下所示的结果集。

DEPTNO T0TAL_SAL T0TAL_B0NUS

10 8750 390

2.解决方案

下面的解决方案也和3.9节的类似，不同之处在于要外连接EMP_B0NUS表，确保把部门编号为10的全部员工都包括进来。

DB2、MySQL, PostgreSQL 和 SQL Server

外连接EMP_B0NUS表，然后去掉部门编号为10的员工的重复项，再计算工资总和。

1	select	deptno,
2		sun(distinct sal) as total一sal,
3		sun(bonus)	as total—bonus
4	f厂on	(
5	select	e.ernpno,
6		e.enarne,
7		e.sal,
8		e.deptno,
9		e.sal*case	when eb.type is null then 0
10			when eb.type = 1 then .1
11			when eb.type = 2 then .2
12			else .3 end as bonus
13	from	emp e left	outer join enp_bonus eb
14	on	(e.ernpno =	eb.empno)
15	whe 厂 e	e.deptno =	10
16		)
17	group	by deptno
以使用窗口函数SUM OVER。
1	select	distinct deptno,total_sal,total_bonus
2	from	(
3	select	e.ernpno,
4		e.ename,
5		sun(distinct e.sal) over
6		(partition by e.deptno) as total一sal,
7		e.deptno,
8		sun(e.sal*case when eb.type is null then
9			when eb.type = 1 then .1
10			when eb.type = 2 then .2
11			else .3
12		end) over
13		(partition	by deptno) as total—bonus
14	f厂on	emp e left	outer join enp_bonus eb
15	on	(e.ernpno =	eb.empno)
16	where	e.deptno =	10
17		)x

Oracle

对于Oracle 9/数据库及其后续版本，上述解决方案仍然适用。除此之外，我们也可以使

用Oracle专有的外连接语法。对于Oracle 8/数据库及更早的版本，只能使用该语法实现外连接。

deptno,

sun(distinct sal) as total一sal,

group by deptno

与DB2及其他数据库类似，Oracle 8/数据库也支持SUM OVER语法，但必须把上面的查询语句里出现的外连接改为Oracle专有的语法。

3.讨论

本实例“问题”部分中的第二个查询语句连接了 EMP表和EMP_B0NUS表，却只返回了员工 MILLER的两行数据，这是导致EMP表的工资总额计算出错的原因（部门编号为10的其他员工没有奖金，他们的工资没有被计入总和）。解决办法则是把EMP表外连接到EMP_B0NUS 表，这样一来，那些没有奖金的员工也会被计算进来。如果一个员工没有奖金，那么EMP一 BONUS表中的TYPE就是Null值。注意到这一点非常重要，因为CASE语句部分已经在3.9 节的基础上稍微有了变动。如果EMP_B0NUS表中的TYPE为NuU值，则CASE表达式会返回 0，这样就不会对总和产生影响。

下面的查询语句是另一种解决方案。首先计算部门编号为10的员工的工资总额，然后再连接EMP表和EMP—B0NUS表（这样就避免了使用外连接）。下面的查询语句适用于所有的关系数据库管理系统。

select d.deptno，

d.total_sal,

when	eb.type	=1	then	• 1
when	eb.type	=2	then	.2
else	•3 end)	as	total	_bonus

enp e,

enp_bonus eb,

(

deptno， sun(sal) as total_sal enp

deptno = 10 by deptno

where e.deptno

and e.enpno = eb.enpno group by d.deptno,d.total_sal

DEPTNO T0TAL_SAL T0TAL_B0NUS

10 8750 39G

3.11从多个表中返回缺失值

1.问题

你想从多个表中返回缺失值。找到存在于DEPT表而不存在于EMP表的数据（即没有员工的部门）需要使用外连接。考虑下面的查询语句，该査询返回了 DEPT表中所有的DEPTNO和 DNAME,以及每个部门里全部员工的名字（如果这个部门有员工的话)。

select d.deptno,d.dnane,e.enane from dept d left outer join enp e on (d.deptno=e.deptno)

DEPTNO DNAME ENAME

20	RESEARCH	SMITH
30	SALES	ALLEN
30	SALES	WARD
20	RESEARCH	JONES
30	SALES	MARTIN
30	SALES	BLAKE
10	ACCOUNTING	CLARK
20	RESEARCH	SCOTT
10	ACCOUNTING	KING
30	SALES	TURNER
20	RESEARCH	ADAMS
30	SALES	JAMES
20	RESEARCH	FORD
10	ACCOUNTING	MILLER
40	OPERATIONS

最后一行是OPERATIONS部门，这个部门虽然没有员工，却也出现在了査询结果中，这是因为DEPT表外连接了 EMP表。现在假设有一个员工不属于任何部门，你将如何返回以上结果集，并且包含那个不属于任何部门的员工呢？换句话说，你希望在同一个查询语句里既外连接到EMP表又外连接到DEPT表。在创建了新的员工数据之后，第一次尝试可能如下所示。

insert into enp (enpno,enane,job,Rgr,hiredate,sal,conn,deptno) select 1111,¹YODA'，']EDI', null,hiredate,sal,conn,null from enp where enane = 'KING'

select d.deptno,d.dnane,e.enane from dept d right outer join enp e on (d.deptno=e.deptno)

DEPTNO DNAME

ENAME

10	ACCOUNTING	MILLER
10	ACCOUNTING	KING
10	ACCOUNTING	CLARK
20	RESEARCH	FORD
20	RESEARCH	ADAMS
20	RESEARCH	SCOTT
20	RESEARCH	JONES
20	RESEARCH	SMITH
30	SALES	JAMES
30	SALES	TURNER
30	SALES	BLAKE
30	SALES	MARTIN
30	SALES	WARD
30	SALES	ALLEN YODA

以上外连接查询包含了那个新的员工，却丢失了先前结果集里的OPERATIONS部门。最终的结果集应该既包括Y0DA，也包括OPERATIONS，如下所示。

DEPTNO	DNAME	ENAME
10	ACCOUNTING	CLARK
10	ACCOUNTING	KING
10	ACCOUNTING	MILLER
20	RESEARCH	ADAMS
20	RESEARCH	FORD
20	RESEARCH	JONES
20	RESEARCH	SCOTT
20	RESEARCH	SMITH
30	SALES	ALLEN
30	SALES	BLAKE
30	SALES	JAMES
30	SALES	MARTIN
30	SALES	TURNER
30	SALES	WARD
40	OPERATIONS	YODA

2.解决方案

使用全外连接（full outer join)，基于一个共同值从两个表中返回缺失值。 DB2、MySQL、PostgreSQL 和 SQL Server 使用显式的全外连接命令从两个表中返回缺失的行以及相匹配的行。

1 select d.deptno,d.dname,e.ename

2 from dept d full outer join enp e

3 on (d.deptno=e.deptno)

或者，也可以合并两个外连接的查询结果。

1 select d•deptno,d.dname,e.enane

2 from dept d right outer join enp e

3 on (d.deptno=e.deptno)

4 union

5 select d.deptno,d.dname,e.enane

6 from dept d left outer join enp e

7 on (d.deptno=e.deptno)

Oracle

对于Oracle 9/数据库及其后续版本，上述解决方案仍然适用。除此之外，我们也可以使用 Oracle专有的外连接语法。对于Oracle 8/数据库及更早的版本，只能使用专有语法实现外连接。

d.deptno,d.dname,e.enane dept d, enp e d.deptno = e.deptno(+)

d.deptno,d.dname,e.enane dept d, enp e d.deptno(+) = e.deptno

3.讨论

全外连接査询其实就是合并两个表的外连接查询的结果集。为了理解全外连接背后的运行原理，直接执行每一个外连接查询，然后合并其查询结果集即可。下面的查询找出了 DEPT 表里与EMP表相匹配的所有行（如果存在的话）。

select	d.deptno,d.dname，e•enane
from	dept d left outer join enp e
on	(d.deptno =	e.deptno)
DEPTNO	DNAME	ENAME
20	RESEARCH	SMITH
30	SALES	ALLEN
30	SALES	WARD
20	RESEARCH	JONES
30	SALES	MARTIN
30	SALES	BLAKE
10	ACCOUNTING	CLARK
20	RESEARCH	SCOTT
10	ACCOUNTING	KING
30	SALES	TURNER
20	RESEARCH	ADAMS
30	SALES	JAMES
20	RESEARCH	FORD
10	ACCOUNTING	MILLER
40	OPERATIONS

接下来的这个查询找出了 EMP表里与DEPT表相匹配的所有行（如果存在的话)。

select d.deptno,d.dnane,e.enane from dept d right outer join enp e on (d.deptno = e.deptno)

DEPTNO DNAME

10	ACCOUNTING	MILLER
10	ACCOUNTING	KING
10	ACCOUNTING	CLARK
20	RESEARCH	FORD
20	RESEARCH	ADAMS
20	RESEARCH	SCOTT
20	RESEARCH	JONES
20	RESEARCH	SMITH
30	SALES	JAMES
30	SALES	TURNER
30	SALES	BLAKE
30	SALES	MARTIN
30	SALES	WARD
30	SALES	ALLEN

YODA

合并上面的两个查询结果，就可以得到最终的结果集。

3.12在运算和比较中使用Null

问题

Null不会等于或不等于任何值，甚至不能与其自身进行比较，但是你希望对从Null列返回的数据进行评估，就像评估具体的值一样。例如，你想找出EMP表里业务提成（COMM 列）比员工WARD低的所有员工。检索结果应该包含业务提成为Null的员工。

解决方案

使用如COALESCE这样的函数把Null转换为一个具体的、可以用于标准评估的值。

1 select enane,comm

2 from emp

3 where coalesce(comn,0)

讨论

COALESCE函数会返回参数列表里的第一个非Null值。就本实例而言，COMM列中的Null会被替换为0，这样才能与WARD的业务提成相比较。把COALESCE函数添加到SELECT列表，就能查看其执行结果。

select enane,conn,coalesce(conm,0) from enp

where coalesce(conn,0) < ( select conn

from enp where enane = ¹ WARD¹ )

ENAME COMM COALESCE(COMM,0)

SMITH 0

ALLEN 300 300

JONES

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

转载于:https://www.cnblogs.com/yuanscn/p/11220196.html

dieqi9270

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL经典实例大全3

本章主要介绍基本的SELECT语句。充分理解这些基础知识十分重要，因为本章中的许多内容不仅会出现在后面更复杂的实例里，同时也是日常SQL操作的一部分。1.1检索所有行和列问题你有一张表，并且想查看表中的所有数据。解决方案用特殊符号对该表执行SELECT查询。select * from enp讨论在SQL中，符号有着特殊含义...
复制链接

扫一扫