SQL從表中選擇，其中第二個表中的連接值是第三個表中值的子集

我在 MS SQL Server 中有以下表格：Tasks、Users、Tags、TaskTags（將任務映射到標簽）和 UserTags（將用戶映射到標簽）。

給定用戶 U，我想找到所有任務 T，其中 T 的每個標簽也是 U 的標簽（例如，如果任務的標簽是用戶標簽的子集，則應回傳任務）。

這是一個包含一些示例資料的表腳本（它可以在http://sqlfiddle.com/使用 MS SQL Server 17 運行）：

CREATE TABLE [dbo].[Tasks](
    [TaskId] [int] NOT NULL PRIMARY KEY,
    [TaskName] [nvarchar](MAX) NOT NULL
)

CREATE TABLE [dbo].[Users](
    [UserId] [int] NOT NULL PRIMARY KEY,
    [UserName] [nvarchar](MAX) NOT NULL
)

CREATE TABLE [dbo].[Tags](
    [TagId] [int] NOT NULL PRIMARY KEY,
    [TagName] [nvarchar](MAX) NOT NULL
)

CREATE TABLE [dbo].[TaskTags](
    [TaskId] [int] NOT NULL,
    [TagId] [int] NOT NULL
)

CREATE TABLE [dbo].[UserTags](
    [UserId] [int] NOT NULL,
    [TagId] [int] NOT NULL
)
 
INSERT INTO Tasks VALUES (1,'Task for all SWEs');
INSERT INTO Tasks VALUES (2,'Task for USA SWEs');
INSERT INTO Tasks VALUES (3,'Task for all PMs');
INSERT INTO Tasks VALUES (4,'Task for Europe PMs');

INSERT INTO Users VALUES (1,'Europe SWE');
INSERT INTO Users VALUES (2,'USA SWE');
INSERT INTO Users VALUES (3,'Europe PM');
INSERT INTO Users VALUES (4,'USA PM');

INSERT INTO Tags VALUES (1,'swe');
INSERT INTO Tags VALUES (2,'pm');
INSERT INTO Tags VALUES (3,'usa');
INSERT INTO Tags VALUES (4,'europe');

INSERT INTO TaskTags VALUES (1,1);
INSERT INTO TaskTags VALUES (2,1);
INSERT INTO TaskTags VALUES (2,3);
INSERT INTO TaskTags VALUES (3,2);
INSERT INTO TaskTags VALUES (4,2);
INSERT INTO TaskTags VALUES (4,4);

INSERT INTO UserTags VALUES (1,1);
INSERT INTO UserTags VALUES (1,4);
INSERT INTO UserTags VALUES (2,1);
INSERT INTO UserTags VALUES (2,3);
INSERT INTO UserTags VALUES (3,2);
INSERT INTO UserTags VALUES (3,4);
INSERT INTO UserTags VALUES (4,2);
INSERT INTO UserTags VALUES (4,3);

當給出任務 T 時，我能夠找出這個問題的反面。例如，給定任務 T，回傳所有用戶 U，其中 T 的標簽是 U 的子集。這是該查詢：

WITH thisTaskTags AS (
    SELECT DISTINCT TaskTags.TagId
    FROM TaskTags
    WHERE TaskTags.TaskId = @taskId
)
SELECT UserTags.UserId
FROM UserTags JOIN thisTaskTags 
    ON UserTags.TagId = thisTaskTags.TagId CROSS JOIN
    (SELECT COUNT(*) AS keycnt FROM thisTaskTags) k
GROUP BY UserTags.UserId
HAVING COUNT(thisTaskTags.TagId) = MAX(k.keycnt)

當@taskId = 1 時，回傳UserId 1 和2，而當@taskId = 2 時，僅回傳UserId 2（正確行為）。

但是，當我嘗試將其轉換為回傳給定用戶應該擁有的所有任務時，我遇到了麻煩。我試過這個查詢：

WITH thisUserTags AS (
    SELECT DISTINCT UserTags.TagId
    FROM UserTags
    WHERE UserTags.UserId = @userId
)
SELECT TaskTags.TaskId
FROM TaskTags JOIN thisUserTags
    ON thisUserTags.TagId = TaskTags.TagId CROSS JOIN
    (SELECT COUNT(*) AS keycnt FROM thisUserTags) k
GROUP BY TaskTags.TaskId
HAVING COUNT(thisUserTags.TagId) = MAX(k.keycnt);

然而，這只會回傳所有任務標簽與所有用戶任務匹配的任務，例如，如果 U 有標簽：[a,b,c]，它只會得到帶有標簽的任務：[a,b,c] 而不是 [a]， [b]、[b,c] 等。

舉個具體的例子，如果您設定@userId = 1，則不回傳任務ID，當正確的輸出將獲得1行時，任務ID = 1。當@userId = 2時，僅回傳任務ID 2，當兩個任務ID都為1時和 2 應該回傳（即如果一個任務只有“swe”標簽，所有“swe”用戶都應該得到它，但是如果一個任務有“swe”和“usa”，只有同時擁有這兩個標簽的用戶應該得到它）。

我也試過這個查詢：

SELECT DISTINCT Tasks.TaskId FROM Tasks
INNER JOIN TaskTags ON TaskTags.TaskId = Tasks.TaskId
WHERE TaskTags.TagId IN (SELECT TagId from UserTags where UserId = @userId)
GROUP BY Tasks.TaskId

But the issue with this is it returns any task that has any tag in common, so U with tags: [a,b,c] would get T with tags: [b,d] even though U doesn't have tag d.

Again with concrete examples, if @userId = 1, taskIDs 1,2, and 4 are returned, when only taskIds 1 and 2 should be returned (task ID 4 should only be assigned to users with both tags "europe" and "pm", here it is erroneously being assigned to a user with tags "europe" and "swe" due to the common "europe" tag).

Could someone shed some light here?

uj5u.com熱心網友回復：

您可能正在尋找類似以下的內容...

declare @userId int = ...;
select Tasks.TaskId
from Tasks
where 0 = (
  select count(1)
  from (
    select TagId from TaskTags where TaskTags.TaskId=Tasks.TaskId
    except
    select TagId from UserTags where UserTags.UserId=@userId
  ) TaskSpecificTags
);

目前尚不清楚您是否還想回傳帶有 0 個標簽的任務，因此您可能還需要測驗該條件。

uj5u.com熱心網友回復：

這是一個經典的帶余數的關系除法問題。

您只需要正確構建它：

• 你想要所有Tasks...
• ...其TaskTags劃分所有UserTags給定的集合User
• 可以有余數，UserTags但不能有余數，TaskTags所以前者是被除數，后者是除數。

一個典型的解決方案（有很多）是將被除數左連接到除數，將其分組，然后確保匹配的被除數的數量與除數的數量相同。換句話說，所有除數都有一個匹配項。

由于您似乎只想要Tasks而不是他們的，因此您可以在子查詢TaskTags中完成所有這些操作：EXISTS

DECLARE @userId int = 1;

SELECT *
FROM Tasks t
WHERE EXISTS (SELECT 1
    FROM TaskTags tt
    LEFT JOIN UserTags ut ON ut.TagId = tt.TagId
        AND ut.UserId = @userId
    WHERE tt.TaskId = t.TaskId
    HAVING COUNT(*) = COUNT(ut.UserId)
);

db<>小提琴

標籤：

上一篇：執行xp_cmdshell以生成CSV-不喜歡查詢中的“WHERE”子句

下一篇：SQL-盡管一列有下劃線，但仍連接2個表