跳转到内容

结构化查询语言/SELECT：集合运算

Add links
来自维基教科书，开放的书籍，开放的世界
SELECT：预定义函数 结构化查询语言
SELECT：集合运算 		SELECT：Case 表达式



表、视图和 SELECT 命令的结果在某种程度上类似于集合论中的集合。在这种比较中，集合的元素对应于表、视图和 SELECT 结果的行。集合论和 SQL 结构之间的区别在于

  • 集合论中的集合不允许重复，而 SQL 允许重复。（即使同一个表的不同行也可能相同，因为没有义务使用主键的概念。）在下面，当我们谈论 SQL 中允许重复的集合时，我们使用术语多重集。
  • 集合论中的集合和多重集是无序的。但是对于 SELECT 命令的结果，我们可以通过可选的 ORDER BY 子句强制排序。

集合论和 SQL 之间的比较更进一步。在 SQL 中，我们有操作在多重集上运行，就集合论意义而言：SQL 操作 UNION、INTERSECT 和 EXCEPT（有些人称之为 MINUS）处理由不同 SELECT 命令生成的中间多重集。这些操作要求多重集类型相同。这主要意味着它们必须具有相同的列数。此外，它们的数据类型也应该相关，但这不是强制性的。如果它们不同，DBMS 将尝试将它们强制转换为公共数据类型 - 如果可能。

UNION

[编辑 | 编辑源代码]

UNION 操作将多个 SELECT 命令的结果合并在一起。UNION 的结果包含在第一个或第二个中间结果中的值。

-- Please consider that this is only one command (only ONE semicolon at the very end)
SELECT firstname  -- first SELECT command
FROM   person
  UNION           -- push both intermediate results together to one result
SELECT lastname   -- second SELECT command
FROM   person;
两个中间结果的UNION

这是一个单个 SQL 命令。它由两个 SELECT 和一个 UNION 操作组成。SELECT 首先被评估。之后，它们的结果被合并成一个单一结果。在我们的示例中，结果包含一个单独列中的所有姓氏和名字（我们的示例在实践中可能帮助不大，它只是 UNION 的演示）。

DISTINCT / ALL
如果我们仔细检查结果，我们会注意到它只包含 17 个值。person 表包含十行，因此我们可能期望结果中包含二十个值。如果我们执行 'SELECT firstname ...' 和 'SELECT lastname ...' 作为单独的命令，不使用 UNION，我们将为两个命令都收到十个值。三个缺失值的解释是 UNION 命令。默认情况下，UNION 会删除重复项。因此，一些中间值被跳过。如果我们想获得重复值，我们必须修改 UNION 操作。它的行为可以通过两个关键字 DISTINCT 或 ALL 之一进行更改。DISTINCT 是默认值，它会删除重复值，就像我们之前看到的那样。ALL 将保留所有值，包括重复值。

-- remove (that's the default) or keep duplicates
SELECT ...
  UNION [DISTINCT | ALL]
SELECT ...
[ -- it is possible to 'UNION' more than 2 intermediate results
  UNION [DISTINCT | ALL]
SELECT ...
];

提示：Oracle 用户：Oracle 不接受默认的 DISTINCT 关键字。请省略它。

通用提示
在大多数情况下，UNION 将不同表上的 SELECT 命令或同一个表的不同列上的 SELECT 命令组合在一起。同一个表同一列上的 SELECT 命令通常会将 WHERE 子句与布尔逻辑结合使用。

-- A very unusual example. People apply such queries on the same table only in combination with very complex WHERE conditions.
-- This example would normally be expressed with a phrasing similar to: WHERE lastname IN ('de Winter', 'Goldstein');
SELECT *
FROM   person
WHERE  lastname = 'de Winter'
  UNION ALL
SELECT *
FROM   person
WHERE  lastname = 'Goldstein';

INTERSECT

[编辑 | 编辑源代码]
INTERSECT

INTERSECT 操作将评估为同时出现在第一个和第二个中间结果中的值。





-- As in our example database, there is no example for the INTERSECT we insert a new person.
-- This person has the same last name 'Victor' as the first name of another person.
INSERT INTO person VALUES (21, 'Paul', 'Victor', DATE'1966-04-02', 'Washington', '078-05-1121', 66);
COMMIT;
-- All firstnames which are used as lastname.
SELECT firstname  -- first SELECT command
FROM   person
  INTERSECT       -- looking for common values
SELECT lastname   -- second SELECT command
FROM   person;

提示：MySQL 用户：MySQL（5.5）不支持 INTERSECT 操作。但由于它不是一个基本操作，所以有一些解决方法。

EXCEPT

[编辑 | 编辑源代码]
EXCEPT

EXCEPT 操作将评估为出现在第一个中间结果中但没有出现在第二个中间结果中的值。





-- All firstname except for 'Victor', because there is a lastname with this value.
SELECT firstname  -- first SELECT command
FROM   person
  EXCEPT          -- are there values in the result of the first SELECT but not of second?
SELECT lastname   -- second SELECT command
FROM   person;

提示：MySQL 用户：MySQL（5.5）不支持 EXCEPT 操作。但由于它不是一个基本操作，所以有一些解决方法。
提示：Oracle 用户：Oracle 使用 MINUS 关键字代替 EXCEPT。

-- Clean up the example database
DELETE FROM person WHERE id > 10;
COMMIT;

Order By

[编辑 | 编辑源代码]

我们可以将集合运算与 SELECT 命令的所有其他元素结合使用，特别是与 ORDER BY 和 GROUP BY 结合使用。但这可能会导致一些不确定性。因此，我们想在下面解释一些细节。

SELECT firstname  -- first SELECT command
FROM   person
  UNION           -- push both intermediate results together to one result
SELECT lastname   -- second SELECT command
FROM   person
ORDER BY firstname;

ORDER BY 属于命令的哪一部分？第一个 SELECT、第二个 SELECT 还是 UNION 的结果？SQL 规则规定集合运算在 ORDER BY 子句之前执行（像往常一样，圆括号可以改变执行顺序）。因此 ORDER BY 对最终结果进行排序，而不是对任何中间结果进行排序。

我们重新排列示例，希望事情变得清楚。

-- Equivalent semantic
SELECT * FROM
  (SELECT firstname  -- first SELECT command
   FROM   person
     UNION           -- push both intermediate (unnamed) results together to the next intermediate result 't'
   SELECT lastname   -- second SELECT command
   FROM   person
  ) t                -- 't' is the name for the intermediate result generated by UNION
ORDER BY t.firstname;

首先，两个 SELECT 被评估，然后是 UNION。这个中间结果被称为 't'。't' 被排序。


通常，人们希望第一个 SELECT 中的行独立于第二个 SELECT 中的行进行排序。我们可以通过在每个 SELECT 语句的结果中添加一个虚拟列，并在 ORDER BY 中使用虚拟列来实现这一点。

SELECT '1' as dummy, firstname
FROM   person
  UNION
SELECT '2', lastname
FROM   person
ORDER BY dummy, firstname;

Group By

[编辑 | 编辑源代码]

使用 GROUP BY 子句，事情比 ORDER BY 稍微复杂一些。GROUP BY 指的是最后一个 SELECT，或者换句话说，指的是它直接级别上的 SELECT。

-- Will not work because the GROUP BY belongs to the second SELECT and not to the UNION!
SELECT firstname
FROM   person
  UNION
SELECT lastname
FROM   person
GROUP BY firstname;
-- 
-- Works, but possibly not what you want to do.
-- The alias name for the (only) column of the UNION is 'firstname'.
SELECT firstname
FROM   person
  UNION
-- We group over the (only) column of the second SELECT, which is 'lastname' and results in 7 values
SELECT lastname
FROM   person
GROUP BY lastname;
--
-- Make things clear: rearrange the query to group over the final result 
SELECT * FROM
  (SELECT firstname  -- columnnames of the first SELECT determins the columnnames of the UNION
   FROM   person
     UNION
   SELECT lastname
   FROM   person
  ) t
GROUP BY t.firstname; -- now we can group over the complete result

练习

[编辑 | 编辑源代码]

显示最低、最高和平均体重，a) 1 行的 3 个值，b) 3 行的 1 个值。

点击查看解决方案
-- 1 row
SELECT min(weight), max(weight), avg(weight)
FROM   person;

-- 3 rows
SELECT min(weight)
FROM   person
  UNION
SELECT max(weight)
FROM   person
  UNION
SELECT avg(weight)
FROM   person;

扩展之前的 3 行解决方案，以满足另外两个条件：a) 只考虑在旧金山出生的人员，以及
b) 添加一个虚拟列，根据相关数值显示 'Min'、'Max' 和 'Avg'。

点击查看解决方案
SELECT 'Min', min(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 'Max', max(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 'Avg', avg(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco';

扩展之前的解决方案，对结果进行排序：最小值在前，平均值在中间，最大值在最后。

点击查看解决方案
-- 'ugly' solution
SELECT '1 Min' AS note, min(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT '3 Max' AS note, max(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT '2 Avg' AS note, avg(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
ORDER BY note;

-- 'clean' solution
SELECT 1 AS note, 'Min', min(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 3 AS note, 'Max', max(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
  UNION
SELECT 2 AS note, 'Avg', avg(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco'
ORDER BY note;

创建体重超过 70 公斤的人员的姓氏列表，以及
所有电子邮件值（每行一个值）。姓氏和电子邮件之间没有对应关系。
（这个示例在实践中帮助不大，但很有启发性。）

点击查看解决方案
SELECT lastname
FROM   person
WHERE  weight > 70
  UNION
SELECT contact_value
FROM   contact
WHERE  contact_type = 'email';

在前面的示例中，'de Winter' 姓氏只显示了一次。但有多个体重超过 70 公斤的同姓人。
为什么？
扩展之前的解决方案，以便显示与标准相符的命中次数一样多的结果行。

点击查看解决方案
-- Extend 'UNION' to 'UNION ALL'. The default is 'UNION DISTINCT'
SELECT lastname
FROM   person
WHERE  weight > 70
  UNION ALL
SELECT contact_value
FROM   contact
WHERE  contact_type = 'email';


SELECT：预定义函数 结构化查询语言
SELECT：集合运算 		SELECT：Case 表达式
检索自 "https://wikibooks.cn/w/index.php?title=Structured_Query_Language/SELECT:_Set_Operations&oldid=3698893"
华夏公益教科书