c# - SQL 按用户连接和过滤 - 多个连接

Question

我遇到了 SQL 方面的问题，而且找不到解决方案。看起来很简单。

我有四个表如下:Task、User、Building 和 BuildingUser。任务 - 每个用户都可以打开与建筑物相关的任务User - 存储用户建筑 - 存储建筑物BuildingUser - 表示用户和建筑物之间的关系。它显示哪个用户可以“看到”建筑物。 N->N

我的情况:

表任务

CREATE TABLE [dbo].[TASK] 
  ( 
     [ID]          [INT] IDENTITY(1, 1) NOT NULL, 
     [ID_USER]     [INT] NOT NULL, 
     [ID_BUILDING] [INT] NOT NULL 
  ) 

表建筑

CREATE TABLE [dbo].[BUILDING] 
  ( 
     [ID]   [INT] IDENTITY(1, 1) NOT NULL, 
     [NAME] [VARCHAR](50) NOT NULL 
  ) 

表用户

CREATE TABLE [dbo].[USER] 
  ( 
     [ID]   [INT] IDENTITY(1, 1) NOT NULL, 
     [NAME] [VARCHAR](50) NOT NULL 
  ) 

表BuildingUser

CREATE TABLE [dbo].[BUILDINGUSER] 
  ( 
     [ID]          [INT] IDENTITY(1, 1) NOT NULL, 
     [ID_BUILDING] [INT] NOT NULL, 
     [ID_USER]     [INT] NOT NULL 
  ) 

示例数据

任务

ID|ID_USER|ID_BUILDING
1 |1      |1
2 |1      |2

用户

ID|Name
1 |Carlos
2 |Joao
3 |Maria

建筑

ID|Name
1 |Rochavera
2 |San George

构建用户

ID_USER|ID_BUILDING
2      |1
3      |2

所以，我想同时将两个任务转移给尽可能多的用户。但是，这些用户必须查看我选择的任务的所有建筑物。例如，如果我选择了 Task.ID 1 和 2 查询必须返回“无”，因为我没有看到这两个建筑物的用户。但是如果我只选择了一个任务，比如 ID 1，我就可以转移到用户 ID 2。

我有这个查询，但它是错误的。这只是一次尝试。

SELECT DISTINCT [USER].ID, 
                [USER].NAME 
FROM   TASK 
       INNER JOIN BUILDINGUSER 
               ON BUILDINGUSER.ID_BUILDING = TASK.ID_BUILDING 
       INNER JOIN [USER] 
               ON [USER].ID = BUILDINGUSER.ID_USER 
WHERE  TASK.ID IN ( 1, 2 ) 

编辑我按照下面的说明进行操作并使其正常工作。

SELECT u.ID, 
       u.NAME 
FROM   TASK t 
       JOIN BUILDINGUSER bu 
         ON bu.ID_BUILDING = t.ID_BUILDING 
       JOIN [USER] u 
         ON u.ID = bu.ID_USER 
WHERE  t.ID IN ( 1, 2 ) 
       AND u.ID != t.ID_USER 
GROUP  BY u.ID, 
          u.NAME 
HAVING Count(bu.ID_BUILDING) = (SELECT Count(*) 
                                FROM   TASK t2 
                                WHERE  t2.ID IN ( 1, 2 )) 

Answer 1

这是一个“集合中的集合”查询。您正在寻找一组中的所有成员(任务)。

我主张通过使用聚合并将逻辑放在 having 子句中来解决这个问题:

select u.id, u.name
from Task t join
     BuildingUser bu
     ON bu.ID_BUILDING = t.ID_BUILDING join
     [User] u
     ON u.ID = bu.ID_USER 
group by u.id, u.name
having sum(case when t.id = 1 then 1 else 0 end) > 0 and  -- Any rows with taskid = 1 ?
       sum(case when t.id = 2 then 1 else 0 end) > 0;     -- Any rows with taskid = 2 ?

这种方法的优点是易于推广。假设您想要任务 1 和 2，但不想 3。having 子句更改为:

having sum(case when t.id = 1 then 1 else 0 end) > 0 and
       sum(case when t.id = 2 then 1 else 0 end) > 0 and
       sum(case when t.id = 3 then 1 else 0 end) = 0;