SQL的表连接中,如果主表中同一条数据对应被连接表有多条数据,则连接后数据会被扩大,但有时候我们希望数据不要被扩大,与主表中数据条数保持一致,即实现左单一匹配连接。
假设我们有学生信息表TEST_TAB_STUDENTS和学生成绩表TEST_TAB_SCORE,如下:
TEST_TAB_STUDENTS:
NAME | STU_NUMBER | CLASS | GRADE |
---|---|---|---|
张三 | 23578 | 三班 | 五年级 |
李四 | 23632 | 二班 | 五年级 |
王五 | 23448 | 一班 | 六年级 |
赵六 | 32251 | 三班 | 四年级 |
TEST_TAB_SCORE:
NAME | MONTH_TIMEKEY | CHINESE | MATHS | ENGLISH | PHYSICS | CHEMISTRY | BIOLOGY |
---|---|---|---|---|---|---|---|
张三 | 2023M01 | 72 | 62 | 67 | 65 | 89 | 71 |
张三 | 2023M02 | 80 | 70 | 83 | 79 | 76 | 97 |
张三 | 2023M03 | 68 | 90 | 63 | 94 | 70 | 93 |
张三 | 2023M04 | 86 | 86 | 95 | 72 | 69 | 72 |
张三 | 2023M05 | 73 | 96 | 99 | 83 | 95 | 82 |
李四 | 2023M02 | 86 | 61 | 70 | 61 | 90 | 90 |
李四 | 2023M03 | 74 | 93 | 73 | 90 | 84 | 66 |
李四 | 2023M04 | 99 | 89 | 61 | 86 | 73 | 86 |
李四 | 2023M05 | 88 | 94 | 77 | 99 | 66 | 79 |
李四 | 2023M06 | 68 | 63 | 85 | 65 | 66 | 75 |
王五 | 2023M03 | 69 | 94 | 64 | 95 | 67 | 96 |
王五 | 2023M04 | 91 | 61 | 94 | 83 | 91 | 74 |
王五 | 2023M05 | 66 | 74 | 82 | 74 | 77 | 97 |
王五 | 2023M06 | 96 | 84 | 82 | 87 | 83 | 70 |
王五 | 2023M07 | 98 | 81 | 78 | 91 | 69 | 76 |
赵六 | 2023M01 | 95 | 91 | 93 | 74 | 69 | 64 |
赵六 | 2023M02 | 90 | 64 | 77 | 94 | 63 | 95 |
赵六 | 2023M03 | 66 | 95 | 66 | 61 | 80 | 91 |
赵六 | 2023M04 | 96 | 67 | 76 | 82 | 87 | 75 |
赵六 | 2023M05 | 92 | 61 | 95 | 98 | 79 | 97 |
如果需要将学生表中匹配出每个学生的任意一笔成绩(或按一定顺序一条),则需要用到左单一匹配。
1. 先将被连接表排序后取一条(如果是任意一条则可以写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
输出:
NAME | STU_NUMBER | CLASS | GRADE | MONTH_TIMEKEY | CHINESE | MATHS | ENGLISH | PHYSICS | CHEMISTRY | BIOLOGY |
---|---|---|---|---|---|---|---|---|---|---|
张三 | 23578 | 三班 | 五年级 | 2023M01 | 72 | 62 | 67 | 65 | 89 | 71 |
李四 | 23632 | 二班 | 五年级 | 2023M06 | 68 | 63 | 85 | 65 | 66 | 75 |
王五 | 23448 | 一班 | 六年级 | 2023M06 | 96 | 84 | 82 | 87 | 83 | 70 |
赵六 | 32251 | 三班 | 四年级 | 2023M01 | 95 | 91 | 93 | 74 | 69 | 64 |
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表较大的情况。