SQL如何实现左单一匹配(两种方法)

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表较大的情况。