Sqlite
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 1
Employees
合作過但不是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'
解釋查詢的邏輯:
- 查找所有
EmpNum
位置FName = 'John'
和Lname = 'Smith'
。- 查找所有與 1 匹配
ProjNum
的EmpNum
結果。因為主鍵應該是 (ProjNum,EmpNum
),這將產生每 1 行ProjNum
。- 找到所有與 2
EmpNum
中的ProjNum
匹配但EmpNum
不等於EmpNum
1 中找到的所有位置。- 在 3 中檢索
Fname
並Lname
為所有EmpNum
。- 刪除重複項(同樣
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 的現場展示中找到。