SQL Server 使用ROW_NUMBER和GROUP BY分组查询每组的第一条和最后一条数据记录

假如我们有个分组查询的需求，要求按照指定字段进行分组并同时查询出分组字段的第一条和最后记录，大致数据如下： UNION查询1：

SELECT 1 AS ID, 110  AS TRAIN_ID
UNION
SELECT 2 AS ID, 111  AS TRAIN_ID
UNION
SELECT 3 AS ID, 112  AS TRAIN_ID

UNION查询2：

SELECT 110  AS TRAIN_ID , 1 AS ID,11111 NUM
UNION
SELECT 110  AS TRAIN_ID,2 AS ID,22222 NUM
UNION
SELECT 110  AS TRAIN_ID,3 AS ID,33333 NUM
UNION
SELECT 111  AS TRAIN_ID,1 AS ID,44444 NUM
UNION
SELECT 111  AS TRAIN_ID,2 AS ID,55555 NUM
UNION
SELECT 112  AS TRAIN_ID,1 AS ID,66666 NUM

要求查询的结果如下：

TRAIN_ID    FIRST       LAST
----------- ----------- -----------
110         11111       33333
111         44444       55555
112         66666       66666

你这时想的处理方式是怎样的呢？本文在这里也分享一下其中一种实现方式，供参考，实现代码如下：

WITH T1 AS(
	SELECT 1 AS ID, 110  AS TRAIN_ID
	UNION
	SELECT 2 AS ID, 111  AS TRAIN_ID
	UNION
	SELECT 3 AS ID, 112  AS TRAIN_ID
),
T2 AS(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY TRAIN_ID ORDER BY NUM) AS RN1,
		ROW_NUMBER() OVER (PARTITION BY TRAIN_ID ORDER BY NUM DESC) AS RN2
		FROM (
			SELECT 110  AS TRAIN_ID , 1 AS ID,11111 NUM
			UNION
			SELECT 110  AS TRAIN_ID,2 AS ID,22222 NUM
			UNION
			SELECT 110  AS TRAIN_ID,3 AS ID,33333 NUM
			UNION
			SELECT 111  AS TRAIN_ID,1 AS ID,44444 NUM
			UNION
			SELECT 111  AS TRAIN_ID,2 AS ID,55555 NUM
			UNION
			SELECT 112  AS TRAIN_ID,1 AS ID,66666 NUM
		) AS T
)
SELECT T1.TRAIN_ID,
	MAX(CASE WHEN RN1 = 1 THEN NUM END) AS [FIRST],
	MAX(CASE WHEN RN2 = 1 THEN NUM END) AS [LAST] 
	FROM T1
	LEFT JOIN T2 ON T1.TRAIN_ID=T2.TRAIN_ID 
GROUP BY T1.TRAIN_ID