Azure SQL中无需循环实现用户与报告权限匹配查询

在Microsoft Azure环境下，现有Reports表存储报告与关联组织的对应关系，Users表存储用户与关联组织的对应关系。需求为：仅当用户拥有某报告关联的全部OrganizationId时，该用户才可访问此报告。已知可通过循环遍历报告实现权限匹配，以下提供纯SQL语句的实现方案。

示例数据表结构及数据 #

Reports表 #

CREATE TABLE Reports (
reportId INT,
OrganizationID INT
);

INSERT INTO Reports (reportId, OrganizationID) VALUES
(1, 1),
(1, 2),
(2, 1),
(3, 1),
(3, 2),
(3, 3);

Users表 #

CREATE TABLE Users (
User CHAR(1),
OrganizationId INT
);

INSERT INTO Users (User, OrganizationId) VALUES
('a', 1),
('a', 2),
('b', 2),
('c', 1),
('c', 2),
('c', 4);

预期输出 #

纯SQL实现方案 #

可以通过分组统计与关联匹配完成需求，核心逻辑是对比用户拥有的报告关联组织数量，与报告本身要求的组织总数是否一致：

SELECT u.User, r.reportId
FROM Reports r
JOIN Users u ON r.OrganizationID = u.OrganizationId
GROUP BY u.User, r.reportId
HAVING COUNT(DISTINCT r.OrganizationID) = (
    SELECT COUNT(DISTINCT OrganizationID)
    FROM Reports
    WHERE reportId = r.reportId
);

逻辑说明 #

1. 通过JOIN关联用户表与报告表，得到用户和报告的组织关联记录
2. 按用户、报告分组，统计该用户已拥有的当前报告关联组织数量
3. 子查询单独统计每个报告本身关联的组织总数
4. HAVING子句判断两者数量是否相等，相等则说明用户拥有该报告的全部访问权限

内容的提问来源于stack exchange，提问作者SF1