集合成员和关系

项目
10/29/2007

使用捷径来确定您需要的数据

Itzik Ben-Gan

T-SQL 程序员的一部分工作是将应用程序用户对信息的请求转换为查询。请求通常涉及到确定满足某些条件的行或若干组行 - 比如，与另一组项共同具有某种关系的项。例如，有时您需要确定部分内容与另一个定单的部分内容相同的所有定单。在本文中，我将讨论《SQL Server Magazine》的读者 John Lombardo 最近提出的一个问题的解决方法。

集合之间的关系

为了介绍我在本文中讨论的问题，首先我们来回顾一下集合论中表示集合之间关系的几个概念。我使用这些概念来为本文所讨论的问题定义条件。我用大写字母指定集合名称，用编号的小写字母指定集合成员，用里面包含集合成员的花括号指定集合本身。

集合论描述了集合之间可以存在的某些关系：

集合 U 等于集合 V，条件是：U 的所有成员都存在于 V 中，并且 V 的所有成员都存在于 U 中－例如，U = {u1, u2, u3}，V = {u1, u2, u3}。
如果 U 的所有成员都存在于 V 中，则 U 是 V 的子集。当 U 等于 V 时，U 是 V 的子集，V 也是 U 的子集。
当 U 是 V 的子集，但 V 不是 U 的子集时，U 是 V 的真子集 －例如，U = {u1, u2, u3}，V = {u1, u2, u3, u4}。

我在本文中讨论的任务涉及到确定与另一个项组有某种关系的项组，也就是集合。我们先来看一个提出问题的示例。

Orders 和 OrderDetails 方案

我使用的方案涉及到作为示例的 Orders 表和 OrderDetails 表，您可以运行清单 1显示的脚本在 tempdb 中创建并填充这两个表。这些表只包括了与本文讨论内容相关的列－即，Orders 中的 orderid 列和 OrderDetails 中的 orderid 和 productid 列。Orders 表中的每个订单在 OrderDetails 表中可能不存在相关行；或存在一个或多个相关行，每一行都包含不同的产品。就其本质来说，每个定单都是实体的一个实例，但在本文中，我所指的定单是属于定单的一组详细信息。

应用程序用户输入一组代表新定单的产品，您的代码将它们存储在 #ProdList 临时表中：

CREATE TABLE #ProdList(productid int NOT NULL PRIMARY KEY)
INSERT INTO #ProdList VALUES(2)
INSERT INTO #ProdList VALUES(3)
INSERT INTO #ProdList VALUES(4)

您从市场营销部门接受了几个任务，要求您确定新定单和现有定单之间的不同关系。这些关系对于市场营销部门可能很重要，营销部门会据此确定采购模式，考虑某些产品组的折扣，等等。

任务 1 ： P 是 O *的子集。*您的第一个任务是确定包含 #ProdList 中的所有产品的定单。用集合术语来说，如果 O 代表构成定单的定单详细信息的集合，P 代表 #ProdList 中的产品的集合，您就要查找满足“P 是 O 的子集”这一条件的定单。您的查询应该返回定单 A 和 B。

下面的查询为您提供了该任务的解决方案：

SELECT orderid
FROM OrderDetails
WHERE productid IN(SELECT productid FROM #ProdList)
GROUP BY orderid
HAVING COUNT(*) = (SELECT COUNT(*) FROM #ProdList)

此代码查询 OrderDetails 表，筛选出只包含存在于 #ProdList 中的产品的行。该查询按 orderid 对结果进行分组，然后筛选产品数与 #ProdList 表中的产品数相同的组。该查询只返回包含 #ProdList 中的所有产品的定单。

任务 2 ： P 等于 O *。*您的第二个任务是确定包含 #ProdList 中的所有产品但不包含其他产品的定单。用集合术语来说，您要查找满足“P 等于 O”这一条件的订单。在前面的查询中，WHERE 子句去除了那些包含 #ProdList 中不存在的产品的行，所以该查询未考虑那些行。但这一次，您需要考虑所有行。要解决此问题，您可以先编写一个返回 OrderDetails 中的所有行的查询，向结果追加到一个名为 inlist 的列中；对于 #ProdList 中存在的产品，该列的取值为 1，对于不包含的产品，该列的取值为 -1：

SELECT *,
  CASE
    WHEN productid IN(SELECT
      productid FROM #ProdList)
      THEN 1
    ELSE -1
  END AS inlist
FROM OrderDetails

图 1显示了此查询的结果。

清单 2中的代码由前面的查询形成一个派生表，按 orderid 对该表的行进行分组，只返回 inlist 值的总和等于 #ProdList 中的产品数的那些组。要使 HAVING 子句中的条件为真，定单必须包含 #ProdList 中的所有产品并且不包含其他产品，而这正是请求所指定的。结果中应该只包含定单 B。

任务 3 ： P 是 O *的真子集。*任务 3 要求您确定包含 #ProdList 中的所有产品和至少一种其他产品的定单。用集合术语来说，您需要满足“P 是 O 的真子集”这一条件的所有定单。您可以使用类似于前面查询中的方法，即，使用一个包含所有定单详细信息和名为 inlist 的附加列的派生表。这一次，对于 #ProdList 中存在的产品，使得 inlist 列的取值为 1，对于不存在的产品，使得该列的取值为 NULL；如清单 3所示。

在 HAVING 子句中，通过将非 Null 的 inlist 值的数目－ COUNT(inlist) －与 #ProdList 中的产品数目进行比较，确保查询只返回包含 #ProdList 中的所有产品的组。通过检查该组中的总行数是否大于非 Null 的 inlist 值的数目，您可以确保返回的定单至少包含一种不存在于 #ProdList 中的产品。结果中应该只包含定单 A。

任务 4 ： O 是 P *的子集。*任务 4 调换了 P 和 O 的角色。您需要返回其所有产品都在 #ProdList 中的订单。用集合术语来说，您需要返回满足“O 是 P 的子集”这一条件的所有定单。为了获得此结果，可以执行 OrderDetails 和 #ProdList 之间的左外部联接。这种联接的结果会返回所有定单详细信息，无论 #ProdList 中是否存在匹配（#ProdList 中的列的 NULL 表示不匹配）都是如此。您要查找的定单就是那些包含的所有定单详细信息在 #ProdList 中存在匹配的定单－也就是说，在 #ProdList 的列中没有 NULL 的定单。确定这些定单的一种简单方法是，按 orderid 对联接的结果进行分组，使用 HAVING 子句来检查该组中所有行的计数是否等于 #ProdList 中非 Null 的 productid 值的计数。清单 4 显示的此查询应该返回定单 B、C 和 D。

任务 5 ： O 是 P *的真子集。*最后一个任务是确定所有产品均存在于 #ProdList 中的定单，同时 #ProdList 至少包含一种另外的产品。用集合术语来说，您要查找满足“O 是 P 的真子集”这一条件的定单。您需要在前面的查询中添加一点内容，修改后的查询将返回作为 #ProdList 的子集（不一定是真子集）的定单。如清单 5的最后一行所示，您向 HAVING 子句添加了一个表达式，以确保定单中的产品数目小于 #ProdList 中的产品数目。结果中应该包含定单 C 和 D。

下期内容

在关系代数中，我在本文中讨论的问题称为关系划分。在本月专栏文章中，我说明了基于聚合的解决方案。下个月，我将讨论如何通过使用一种不同的、基于相关子查询的方法来解决这些问题。我还会探讨您需要确定满足集体成员条件的集合的问题，例如，确定包含产品 1 和 3 或产品 2 和 4 的定单。同时，看看您是否能通过使用另一种方法（不同于我所使用的方法）得出本月示例问题的解决方案。有关 ANSI SQL 中的关系划分的其他知识，请参见 Joe Celko 所著的?SQL for Smarties??第 2 版（Morgan Kaufman，1999）。

错误、评价、建议| 法律 | 法律隐私 | 广告

转到原英文页面

集合成员和关系

其他资源