集合成员和关系

发布日期 : 4/1/2004 | 更新日期 : 4/1/2004

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

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)。

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

版权所有? 2002 Penton Media, Inc. 保留所有权利。

转到原英文页面

显示: