如何為精確的組對編寫查詢

我得到了架構：

員工（Fname，Ssn，Dno）

部門（Dname，Dnumber，Mgr_ssn，Mgr_start_date）

項目（Pname，Pnumber，位置，底部）

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 查詢？

一個簡單的連接就足夠了。

現在我收集使用者在 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;

---

在 DB Fiddle 上查看

**這是一個沒有餘數的關係除法問題。**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;

引用自：https://dba.stackexchange.com/questions/289602