【转】【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查找被连接表中按一定顺序后的第一条(或任意一条)

如果连接后只需查找一列，如语文成绩，则可直接查找：

--根据MONTH_TIMEKEY排序取最后一条数据
SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
(SELECT /*+ FIRST_ROWS*/CHINESE
    FROM
    (SELECT CHINESE 
    FROM TEST_TAB_SCORE B
    WHERE A.NAME = B.NAME
    ORDER BY MONTH_TIMEKEY DESC 
    ) WHERE ROWNUM<=1
) AS CHINESE
FROM TEST_TAB_STUDENTS A
 
--不需要排序取任意一条
SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
(SELECT /*+ FIRST_ROWS*/CHINESE
    FROM TEST_TAB_SCORE B
    WHERE A.NAME = B.NAME
    AND ROWNUM<=1
) AS CHINESE
FROM TEST_TAB_STUDENTS A

如果连接后需要的列较多，如各科成绩都需要，则重复查找会耗费性能，可以先查找出ROWID，再左连接：

--根据MONTH_TIMEKEY排序取最后一条数据
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
FROM (
    SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
    (SELECT /*+ FIRST_ROWS*/ROW_ID
        FROM
        (SELECT ROWID ROW_ID 
        FROM TEST_TAB_SCORE B
        WHERE A.NAME = B.NAME
        ORDER BY MONTH_TIMEKEY DESC
        ) WHERE ROWNUM<=1
    ) AS ROW_ID
    FROM TEST_TAB_STUDENTS A
) AA 
LEFT JOIN TEST_TAB_SCORE C ON AA.ROW_ID = C.ROWID
 
--不需要排序取任意一条
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
FROM (
    SELECT A.NAME,A.STU_NUMBER,A.CLASS,A.GRADE,
    (SELECT /*+ FIRST_ROWS*/ ROWID ROW_ID 
        FROM TEST_TAB_SCORE B
        WHERE A.NAME = B.NAME
        AND ROWNUM<=1
    ) AS ROW_ID
    FROM TEST_TAB_STUDENTS A
) AA 
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表较大的情况。