Sqlite

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

  • July 29, 2021

我是 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 匹配ProjNumEmpNum結果。因為主鍵應該是 ( ProjNum,EmpNum),這將產生每 1 行ProjNum
  3. 找到所有與 2EmpNum中的ProjNum匹配但EmpNum不等於EmpNum1 中找到的所有位置。
  4. 在 3 中檢索FnameLname為所有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