SQL查詢2個表使用沒有相交的字元串

我是 SQL 新手並且一直在使用 SQLite 我想知道是否可以進行 Intersect 或通過某種形式的連接來代替？

我正在嘗試搜尋 2 個表格以查找“哪些員工參與了 John Smith 所做的任何項目”，但未在結果中顯示 John Smith？

表一：（T1）

表 2：（T2）

我將如何重新編寫此程式碼，以便按姓氏而不是 EmpNum 進行搜尋？將 EmpNum 編輯為 T1.Lname 不會產生任何結果。

   SELECT T1.EMPNUM, T1.FNAME, T1.LNAME FROM T1
   INNER JOIN T2
   ON T1.EMPNUM = T2.EMPNUM
   WHERE PROJNUM IN( SELECT PROJNUM
                     FROM T2 WHERE T2.EMPNUM = '101'
                     GROUP BY PROJNUM)
  AND T2.EMPNUM NOT LIKE '101'



      

這可以直接完成，無需 CTE 或子查詢：

這將返回與John Smith 1Employees合作過但不是John Smith的人：Project

SELECT DISTINCT
 Coworker.EmpNum
,Coworker.Fname
,Coworker.Lname
FROM
 t1 Emp
INNER JOIN
 t2 EmpProject
   ON EmpProject.EmpNum = Emp.EmpNum
INNER JOIN
 t2 CoworkerProject
   ON CoworkerProject.ProjNum = EmpProject.ProjNum
       AND CoworkerProject.EmpNum <> Emp.EmpNum
INNER JOIN
 t1 Coworker
   ON Coworker.EmpNum = CoworkerProject.EmpNum
WHERE
 Emp.Fname = 'John'
   AND Emp.Lname = 'Smith'

解釋查詢的邏輯：

1. 查找所有EmpNum位置FName = 'John'和Lname = 'Smith'。
2. 查找所有與 1 匹配ProjNum的EmpNum結果。因為主鍵應該是 ( ProjNum,EmpNum)，這將產生每 1 行ProjNum。
3. 找到所有與 2EmpNum中的ProjNum匹配但EmpNum不等於EmpNum1 中找到的所有位置。
4. 在 3 中檢索Fname並Lname為所有EmpNum。
5. 刪除重複項（同樣Employee適用於 John Smith 的多個項目）。

1顯然，可能有多個 John Smith，因此在搜尋之前直接選擇特定的 John Smith 可能會更好EmpNum，但如果您不關心上面的哪個John Smith 就足夠了。

選項1

您想要的員工不是John Smith：

SELECT
 TgtEmp.EmpNum
, TgtEmp.Fname
, TgtEmp.Lname
FROM
 T1 AS TgtEmp
WHERE
 NOT (TgtEmp.Fname = 'John' AND TgtEmp.Lname = 'Smith')
;

他們每個人都必須有一些項目：

...
WHERE
 NOT (TgtEmp.Fname = 'John' AND TgtEmp.Lname = 'Smith')
 AND EXISTS
 (
   SELECT
     ...
   FROM
     T2 AS TgtProjEmp
   WHERE
     TgtProjEmp.EmpNum = TgtEmp.EmpNum
   ...
 )

這些項目列表必須與 John(s) Smith(s) 完成的項目列表相交：

...
 AND EXISTS
 (
   SELECT
     TgtProjEmp.ProjNum
   FROM
     T2 AS TgtProjEmp
   WHERE
     TgtProjEmp.EmpNum = TgtEmp.EmpNum

   INTERSECT

   SELECT
     SrcProjEmp.ProjNum
   FROM
     T2 AS SrcProjEmp
   WHERE
     SrcProjEmp.EmpNum IN
     (
       SELECT
         SrcEmp.EmpNum
       FROM
         T1 AS SrcEmp
       WHERE
         (SrcEmp.Fname = 'John' AND SrcEmp.Lname = 'Smith')
     )
 )

把所有東西放在一起，我們會得到這樣的東西：

SELECT
 TgtEmp.EmpNum
, TgtEmp.Fname
, TgtEmp.Lname
FROM
 T1 AS TgtEmp
WHERE
 NOT (TgtEmp.Fname = 'John' AND TgtEmp.Lname = 'Smith')
 AND EXISTS
 (
   SELECT
     TgtProjEmp.ProjNum
   FROM
     T2 AS TgtProjEmp
   WHERE
     TgtProjEmp.EmpNum = TgtEmp.EmpNum

   INTERSECT

   SELECT
     SrcProjEmp.ProjNum
   FROM
     T2 AS SrcProjEmp
   WHERE
     SrcProjEmp.EmpNum IN
     (
       SELECT
         SrcEmp.EmpNum
       FROM
         T1 AS SrcEmp
       WHERE
         (SrcEmp.Fname = 'John' AND SrcEmp.Lname = 'Smith')
     )
 )
;

---

選項 2

這不是唯一的選擇。還有一種方法不涉及多次引用表。首先，您將兩個表連接在一起並使用視窗聚合來標記由 (a) John Smith 完成的項目。

SELECT
 Emp.EmpNum
, Emp.Fname
, Emp.Lname
, MAX(CASE WHEN Emp.Fname = 'John' AND Emp.Lname = 'Smith' THEN 1 END)
 OVER (PARTITION BY ProjEmp.ProjNum) AS HasProjectDoneByJohnSmith
FROM
 T1 AS Emp
 INNER JOIN T2 AS ProjEmp ON Emp.EmpNum = ProjEmp.EmpNum

然後，您將上面的內容用作派生表或 CTE，並提取所有非 John Smith 的不同員工姓名，其中HasProjectDoneByJohnSmith等於 1：

• 派生表：

SELECT DISTINCT
 EmpNum
, Fname
, Lname
FROM
 (
   SELECT
     Emp.EmpNum
   , Emp.Fname
   , Emp.Lname
   , MAX(CASE WHEN Emp.Fname = 'John' AND Emp.Lname = 'Smith' THEN 1 END)
     OVER (PARTITION BY ProjEmp.ProjNum)  AS HasProjectDoneByJohnSmith
   FROM
     T1 AS Emp
     INNER JOIN T2 AS ProjEmp ON Emp.EmpNum = ProjEmp.EmpNum
 ) AS derived
WHERE
 NOT (Fname = 'John' AND Lname = 'Smith')
 AND HasProjectDoneByJohnSmith = 1
;

• 熱電偶：

WITH
 EmployeeProjects AS
 (
   SELECT
     Emp.EmpNum
   , Emp.Fname
   , Emp.Lname
   , MAX(CASE WHEN Emp.Fname = 'John' AND Emp.Lname = 'Smith' THEN 1 END)
     OVER (PARTITION BY ProjEmp.ProjNum)  AS HasProjectDoneByJohnSmith
   FROM
     T1 AS Emp
     INNER JOIN T2 AS ProjEmp ON Emp.EmpNum = ProjEmp.EmpNum
 )
SELECT DISTINCT
 EmpNum
, Fname
, Lname
FROM
 EmployeeProjects
WHERE
 NOT (Fname = 'John' AND Lname = 'Smith')
 AND HasProjectDoneByJohnSmith = 1
;

---

所有三個最終查詢都可以在 db<>fiddle 的現場展示中找到。

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