主页 / dba / 问题 / 193404
Accepted
Thomas Franz
Asked: 2017-12-19 06:37:27 +0800 CST

分配给另一个表中所有其他 ID 的 ID 列表

  • 772

我需要帮助完成以下任务（几个小时后放弃）

CREATE TABLE #cities (cityId INT, city VARCHAR(100));
INSERT INTO #cities (cityId, city) 
VALUES (1, 'Metropolis A'),
       (2, 'Town B'),
       (3, 'Village C');

CREATE TABLE #assignements (assignmentID INT, cityId INT, desciption VARCHAR(100));
INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (1, 1, 'Test Alpha Metropol'),
       (1, 2, 'Test Alpha Town'),
       (1, 3, 'Test Alpha Village'),
       (2, 1, 'Test Beta Metropol');

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (2, 3, 'Test Beta Village');

我需要一个查询，返回所有加入#cities 的 assignmentID（简单 :-)），但只列出那些在 ALL assignmentID 中使用的城市。

在上面的例子中，我应该INSERT只在第一个之后返回

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'

因为 Town 和 village 只使用一次（但存在两个不同的 assignmentID）

第二次后INSERT它应该返回

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'
1             'Village C'
2             'Village C'

因为 C 村现在也分配给了两个 assignmentID。

当我会

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (3, 2, 'Test Gamma Town');

查询不应返回任何行，因为没有城市被分配给所有三个 assignmentID（Alpha、Beta 和 Gamma）。

当然这个例子被简化了，实际上可能有任意数量的城市被分配给（当前）最多 11 个 assignmentID

sql-server t-sql

1 个回答

  1. Best Answer

    你可以尝试这样的事情：

    select a.assignmentID, c.city
from #cities c
join #assignements a on a.cityId = c.cityId
where c.cityId in ( select c.cityId
                    from #cities c
                    left join #assignements a on a.cityId = c.cityId
                    group by c.cityId
                    having count(*) = (select count(distinct assignmentID) 
                                       from #assignements)
                  )
    • 2

相关问题