sql - 使用 group by 和多个列的行号

Question

为了在网格中显示数据，我必须构建 SQL 请求时遇到了一些问题。

我有如下临时表(我在示例中输入数字而不是电子邮件地址):

GroupID | Email1 | Email2 
null    |   1    | 2
null    |   1    | 2
null    |   1    | null
null    |   3    | 1
null    |   2    | 2
null    |   4    | 2
null    |   5    | 6
null    |   6    | null

我需要更新表格以设置 GroupID，如下所述:如果 email1 或 email2 与任何其他记录匹配，则此记录需要与其他记录具有相同的 groupId。例如(使用上表):

GroupID | Email1 | Email2
**1**   |   1    | 2
**1**   |   1    | 2
**1**   |   1    | null
**1**   |   3    | 1
**1**   |   2    | 2
**1**   |   4    | 2
**2**   |   5    | 6
**2**   |   6    | null

我试过类似的东西:

UPDATE a
SET a.GroupId = b.GroupId
FROM   #temp a
INNER JOIN (SELECT Email, 
                   ROW_NUMBER() OVER (ORDER BY ISNULL(Email,'zzzzzzzz')) GroupId
            FROM (SELECT Email1 Email
                  FROM #temp
                  GROUP BY Email1
                  UNION ALL 
                  SELECT Email2 Email   
                  FROM #temp                
                  GROUP BY Email2 
                 ) c
            GROUP BY Email
           ) b
ON a.Email1 = b.Email OR
   a.Email2 = b.Email OR 
   (b.Email IS NULL AND a.Email1 IS NULL AND a.Email2 IS NULL)

但这不起作用，我打算......例如，电子邮件 2 等于电子邮件 1 的情况不被识别为同一组......我怎样才能让这个请求按照我的意愿工作？有可能吗？？

[编辑] 2013/15/17 14:15:事实上，对于规则，我的意思是“如果电子邮件 1 或电子邮件 2 与任何其他记录的电子邮件 1 或电子邮件 2 匹配，则应该属于相同的组 ID”

Answer 1

这不能在单个 JOIN 中执行，因为可能有很长的电子邮件链要遍历，例如1, 2 -> 2, 3 -> 3, 4 -> ... -> 99, 100 >。 (您可以使用递归 CTE 在单个语句中完成它——以某种方式解决 GROUP BY 问题——但你知道我的意思。)

这是一种方法(SQL Server 2005 及更高版本):

WITH E AS (
   SELECT
      Num = Row_Number() OVER (ORDER BY (SELECT 1)),
      *
   FROM dbo.EmailGroups
)
UPDATE E
SET E.GroupID = E.Num
;

WHILE @@RowCount > 0 BEGIN
   UPDATE E
   SET E.GroupID = X.MinGroupID
   FROM
      dbo.EmailGroups E
      INNER JOIN (
         SELECT
            E1.GroupID,
            MinGroupID = Min(E2.GroupID)
         FROM
            dbo.EmailGroups E1
            INNER JOIN dbo.EmailGroups E2
               ON E1.Email1 IN (E2.Email1, E2.Email2)
               OR E1.Email2 IN (E2.Email1, E2.Email2)
         GROUP BY
            E1.GroupID
         HAVING
            E1.GroupID <> Min(E2.GroupID)
      ) X ON E.GroupID = X.GroupID
   ;
END;

See this working in a SQL Fiddle .

这将导致每个链接的行集都具有相同的 GroupID，与所有其他 GroupID 不同(但它们不是连续的，会有间隙) .如果您需要它们是连续的，请执行最终更新以将 GroupID 设置为 DENSE_RANK() OVER (ORDER BY GroupID) -- 这在 fiddle 中显示。