【轉】【SQL】 實現左單一匹配

陆陆无为而治者發表於2024-07-02
SQL

原文地址：https://blog.csdn.net/weixin_46156257/article/details/131234451

SQL的表連線中，如果主表中同一條資料對應被連線表有多條資料，則連線後資料會被擴大，但有時候我們希望資料不要被擴大，與主表中資料條數保持一致，即實現左單一匹配連線。

假設我們有學生資訊表TEST_TAB_STUDENTS和學生成績表TEST_TAB_SCORE，如下：

TEST_TAB_STUDENTS：

NAME STU_NUMBER CLASS GRADE
張三 23578 三班 五年級
李四 23632 二班 五年級
王五 23448 一班 六年級
趙六 32251 三班 四年級

TEST_TAB_SCORE：

如果需要將學生表中匹配出每個學生的任意一筆成績(或按一定順序一條)，則需要用到左單一匹配。

先將被連線表排序後取一條(如果是任意一條則可以寫order by 1)

SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,C.MONTH_TIMEKEY,C.CHINESE,C.MATHS,C.ENGLISH,C.PHYSICS,C.CHEMISTRY,C.BIOLOGY
FROM TEST_TAB_STUDENTS A
--如果需要按某一列排序，例如取時間最後的一條成績，可改為ORDER BY MONTH_TIMEKEY DESC
LEFT JOIN (SELECT B.*,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY 1) RN FROM TEST_TAB_SCORE B) C
ON A.NAME = C.NAME 
WHERE C.RN = 1

輸出：

2. 透過hint：first_rows查詢被連線表中按一定順序後的第一條(或任意一條)

如果連線後只需查詢一列，如語文成績，則可直接查詢：

 1 --根據MONTH_TIMEKEY排序取最後一條資料
 2 SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
 3 (SELECT /*+ FIRST_ROWS*/CHINESE
 4     FROM
 5     (SELECT CHINESE 
 6     FROM TEST_TAB_SCORE B
 7     WHERE A.NAME = B.NAME
 8     ORDER BY MONTH_TIMEKEY DESC 
 9     ) WHERE ROWNUM<=1
10 ) AS CHINESE
11 FROM TEST_TAB_STUDENTS A
12  
13 --不需要排序取任意一條
14 SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
15 (SELECT /*+ FIRST_ROWS*/CHINESE
16     FROM TEST_TAB_SCORE B
17     WHERE A.NAME = B.NAME
18     AND ROWNUM<=1
19 ) AS CHINESE
20 FROM TEST_TAB_STUDENTS A

如果連線後需要的列較多，如各科成績都需要，則重複查詢會耗費效能，可以先查詢出ROWID，再左連線：

 1 --根據MONTH_TIMEKEY排序取最後一條資料
 2 SELECT AA.NAME,AA.STU_NUMBER,AA.CLASS,AA.GRADE,C.MONTH_TIMEKEY,C.CHINESE,C.MATHS,C.ENGLISH,C.PHYSICS,C.CHEMISTRY,C.BIOLOGY
 3 FROM (
 4     SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
 5     (SELECT /*+ FIRST_ROWS*/ROW_ID
 6         FROM
 7         (SELECT ROWID ROW_ID 
 8         FROM TEST_TAB_SCORE B
 9         WHERE A.NAME = B.NAME
10         ORDER BY MONTH_TIMEKEY DESC
11         ) WHERE ROWNUM<=1
12     ) AS ROW_ID
13     FROM TEST_TAB_STUDENTS A
14 ) AA 
15 LEFT JOIN TEST_TAB_SCORE C ON AA.ROW_ID = C.ROWID
16  
17 --不需要排序取任意一條
18 SELECT AA.NAME,AA.STU_NUMBER,AA.CLASS,AA.GRADE,C.MONTH_TIMEKEY,C.CHINESE,C.MATHS,C.ENGLISH,C.PHYSICS,C.CHEMISTRY,C.BIOLOGY
19 FROM (
20     SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
21     (SELECT /*+ FIRST_ROWS*/ ROWID ROW_ID 
22         FROM TEST_TAB_SCORE B
23         WHERE A.NAME = B.NAME
24         AND ROWNUM<=1
25     ) AS ROW_ID
26     FROM TEST_TAB_STUDENTS A
27 ) AA 
28 LEFT JOIN TEST_TAB_SCORE C ON AA.ROW_ID = C.ROWID

3. 兩種方法比較

兩種方式均能實現需求，在不同場景效能表現有所不同：

方法1採用排序後hash join的方式，適用於主表(TEST_TAB_STUDENTS)較大，而被匹配表(TEST_TAB_SCORE)較小的情況，因為被匹配表需要全部排序；方法2則採用first_row方式迭代取出B表的第一條資料，適用於主表(取出的資料)較小，而B表較大的情況。

相關文章