SQL-消息架构-需要在给定一组用户的情况下查找现有的消息线程

SQL-消息架构-需要在给定一组用户的情况下查找现有的消息线程

编辑：

在尝试解释查询的过程中，我意识到它不一定总是可以正常工作。因此，我回头想出了如何进行测试。我仍然对模式设置感到困惑-
也就是说，这意味着无法将新用户添加到现有线程中，并且特定的一组用户只能在一个线程中进行对话-但纠正这一点很好查询。

WITH Selected_Users(id) as (VALUES (@id1), (@id2), --etc--),     Threads(id) as (SELECT DISTINCT threadFk          FROM ThreadMembers as a          JOIN Selected_Users as b          ON b.id = a.userFk)SELECT a.idFROM Threads as aWHERe NOT EXISTS (SELECt '1'       FROM ThreadMembers as b       LEFT JOIN Selected_Users as c       ON c.id = b.userFk       WHERe c.id IS NULL       AND b.threadFk = a.id)AND NOT EXISTS (SELECT '1'     FROM Selected_Users as b     LEFT JOIN ThreadMembers as c     ON c.userFk = b.id     AND c.threadFk = a.id     WHERe c.userFk IS NULL)

该语句可能必须是动态的，以构建选定用户的列表，除非SQL
Server有一种方法可以将列表作为主机变量提供（我知道DB2至少从iSeries可以做到）。我没有完美的数据集可以对此进行测试，但是针对数百万的行表（只有多对一关系），它几乎立即返回-
我正在对此进行仅索引访问（提示） 。

说明：

WITH Selected_Users(id) as (VALUES (@id1), (@id2), --etc--),

该CTE正在构建用户列表，以便可以将其作为表引用。这使得处理起来最容易，尽管可以在

IN

任何地方简单地用一条语句替换它（尽管需要多个引用）。

     Threads(id) as (SELECT DISTINCT threadFk          FROM ThreadMembers as a          JOIN Selected_Users as b          ON b.id = a.userFk)

此CTE获取用户所涉及的（不同的）线程的列表。通常，这只是将列表分成对的单个引用

threadFk

。

SELECT a.idFROM Threads as a

…获取选定的线程集…

WHERe NOT EXISTS (SELECt '1'       FROM ThreadMembers as b       LEFT JOIN Selected_Users as c       ON c.id = b.userFk       WHERe c.id IS NULL       AND b.threadFk = a.id)

在没有任何人从选定的用户列表中“丢失”的地方-
也就是说，它消除了具有较大列表的子集的用户列表的线程。它也消除了有一些从选择中列出的用户的线程，但也有一些是没有的，这意味着 计数
的用户将匹配，但实际用户不会（这是我的第一个版本失败）。

---

编辑：

我意识到，虽然现有语句解决了提供的用户列表是为给定线程列出的用户子集的情况，但我没有解决选择的用户列表包含以下子集的情况：给定线程的用户列表。

AND NOT EXISTS (SELECT '1'     FROM Selected_Users as b     LEFT JOIN ThreadMembers as c     ON c.userFk = b.id     AND c.threadFk = a.id     WHERe c.userFk IS NULL)

本条款解决了该问题。在排除特定线程的用户之后，请确保选择列表中没有剩余的用户。

声明现在让我有些烦恼-我可能会有一种更好的方式来执行此 *** 作…

---

编辑：

Muwahaha，有 是 一个

COUNT(*)

版本，这也应该是快：

WITH Selected_Users(id) as (VALUES (@id1), (@id2), --etc--),SELECT a.threadFkFROM ThreadMembers as aJOIN Selected_Users as bON b.id = a.userFkGROUP BY a.threadFkHAVINg COUNT(*) = (SELECT COUNT(*) FROM Selected_Users)AND COUNT(*) = (SELECT COUNT(*) from ThreadMembers as c     WHERe c.threadFk = a.threadFk)

说明：

SELECt a.threadFkFROM ThreadMembers as aJOIN Selected_Users as bON b.id = a.userFk

这是为了获得列出的成员所属的所有线程的加入。这是与

Threads

上面的CTE等效的内部内容。实际上，您也可以在上面的查询中删除该CTE。

GROUP BY a.threadFk

毕竟，我们只需要一个给定线程的实例。同样（至少在DB2中），该语句的其余部分除非存在，否则是无效的。

HAVINg COUNT(*) = (SELECT COUNT(*) FROM Selected_Users)

验证给定线程是否存在所有选定用户。或者，所有选定用户都必须存在于给定线程中。

AND COUNT(*) = (SELECT COUNT(*) from ThreadMembers as c     WHERe c.threadFk = a.threadFk)

验证给定线程是否没有未选择的用户。否则，不得有任何用户被“拒之门外”

您 应该 为此获得仅索引访问（我似乎是）。该

COUNT(*)

结果行（用于的

GROUPBY

）应该只被执行一次，并再利用。该

HAVINg

子句评估 后 的

GROUP BY

发生（如果我没有记错），所以从原始表的计数只应子选择
一次， 每次

threadFk

。