主页 / dba / 问题 / 289602
Accepted
pk00
Asked: 2021-04-11 09:04:00 +0800 CST

如何为精确的组对编写查询

  • 772

我得到了架构：

员工（Fname，Ssn，Dno）

部门（Dname，Dnumber，Mgr_ssn，Mgr_start_date）

项目（Pname，Pnumber，位置，Dno）

WORKS_ON（Essn，Pno）

小提琴：https ://www.db-fiddle.com/f/9LnJN32WLVu1HsdmG8NUAc/5

主键以粗体显示。

问题：是否可以定义一个视图来报告每个员工的以下详细信息：Ssn 和从事完全相同项目集的员工数量？解释为什么不这样做或给出一个 SQL 查询。

我的想法是找到唯一的员工对并检查第一个员工工作的项目是否等于第二个员工的项目（这是通过使用 set 操作完成的，除非 s1 除了 s2 联合 s2 除了 s1 是空的，而不是看起来像 s1 和 s2相等）

我的尝试是：

SELECT e1.ssn,count(e2.ssn)
FROM EMPLOYEE e1, EMPLOYEE e2
WHERE e1.ssn < e2.ssn
AND NOT EXISTS
(
 (
 (SELECT Pno
 FROM WORKS_ON w1
 WHERE w1.Essn = e1.Ssn)
 EXCEPT ALL
 (SELECT Pno
 FROM WORKS_ON w2
 WHERE w2.Essn = e2.Ssn)
 )

 UNION ALL
 
 (
 (SELECT Pno
 FROM WORKS_ON w2
 WHERE w2.Essn = e2.Ssn)
 EXCEPT ALL
 (SELECT Pno
 FROM WORKS_ON w1
 WHERE w1.Essn = e1.Ssn)
 )
)
GROUP BY(e1.ssn)

这个查询看起来正确吗？还有一种更简洁的方法吗？

作为最后的附注，究竟是什么决定了我们是否不可能为问题构建 SQL 查询？

postgresql relational-division

2 个回答

  1. 这是一个没有余数的关系除法问题。Joe Celko 对此有一篇非常好的文章。

    有很多方法可以切这个蛋糕。这里有一对：

    1.获取每个员工项目的窗口计数（您也可以将其作为标量子查询来执行）。
    将员工彼此左连接，在 上匹配Pno。
    按每对员工分组，排除项目过多或过少的员工。
    然后对每个员工进行最终分组，计算匹配的员工。

    WITH WORKS_ONwithCount AS (
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY Essn) AS CountPno
    FROM WORKS_ON
),
EmployeesCrossJoin AS (
    SELECT this.Essn,
        other.Essn
    FROM cte this
    LEFT JOIN cte other ON other.Essn <> this.Essn AND other.Pno = this.Pno
    GROUP BY this.Essn, other.Essn, other.CountPno
    HAVING COUNT(*) = COUNT(other.Pno) AND COUNT(*) = other.CountPno
)
SELECT emp.Essn, COUNT(*) AS CountMatching
FROM EmployeesCrossJoin emp
GROUP BY emp.Essn;

    2.将每个员工分组，将他们的项目编号放在一个数组中。
    然后计算其他员工的数量：在取消嵌套和完全连接两个数组之后，两边都没有NULL值。

    WITH cte AS (
    SELECT
        Essn,
        array_agg(Pno ORDER BY Pno) AS AerPno
    FROM WORKS_ON
    GROUP BY Essn
)
SELECT Essn,
    (SELECT COUNT(*)
    FROM cte other
    WHERE this.Essn <> other.Essn
        AND NOT EXISTS (SELECT 1
        FROM unnest(this.AerPno) AS nThis(value)
        FULL JOIN unnest(other.AerPno) AS nOther(value) ON nOther.value = nThis.value
        WHERE nOther.value IS NULL OR nThis.value IS NULL
    ) AS 
FROM cte this;
    • 1
  2. Best Answer

    一个简单的连接就足够了。

    现在我收集用户在 aeeay 中工作的每个 project_id，并进行比较

    查询 #1

    WITH CTE AS (SELECT Essn,array_agg(Pno ORDER BY Pno) AS AerPno FROM WORKS_ON
GROUP BY Essn)
SELECT c1.Essn, COUNT(*) FROM CTE c1 JOIN CTE c2 on c1.AerPno = c2.AerPno AND c1.Essn <> c2.Essn
GROUP BY c1.Essn;
    本质 数数
    5 1
    1 1

    在 DB Fiddle 上查看

    • 0

相关问题