如何在SQL中按需复制特定行且不修改原表？

需求明确：不修改Test原表，动态生成每个id1分组下第一行（即该id1对应最小id2的行）的副本，副本的id2固定设为0，同时保留原表中chk=1的所有行。

原表结构与测试数据 #

CREATE TABLE [Test](
[id1] [tinyint] NOT NULL,
[id2] [int] NOT NULL,
[chk] [int] NOT NULL,
[ref] [varchar](50) NOT NULL)

INSERT INTO [Test]
VALUES
(4,1,1,'ab'),
(4,2,0,'cd'),
(5,2,0,'ef'),
(6,1,0,'gh'),
(6,2,1,'ij'),
(7,1,0,'kl'),
(7,2,0,'mn'),
(8,1,1,'op'),
(8,2,1,'qr'),
(8,3,1,'st'),
(9,1,1,'uv'),
(9,2,1,'wx'),
(10,1,1,'yz'),
(10,2,1,'?!')

你的原查询（可行但可优化） #

Select * from Test 
Where chk = 1
Union All
Select a.id1, 0, 0, a.ref From Test a
Inner Join (Select id1, Min(id2) as matchid From Test
            Group By id1) b On a.id1 = b.id1 ANd a.id2 = b.matchid
Order By id1, id2

更优实现方案 #

方案1：用ROW_NUMBER()窗口函数减少表扫描

窗口函数可以一次性完成首行标记和原数据筛选，只需要扫描一次Test表，比原查询的两次全表扫描更高效：

SELECT id1, id2, chk, ref
FROM Test
WHERE chk = 1
UNION ALL
SELECT id1, 0, 0, ref
FROM (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY id2) AS rn
    FROM Test
) t
WHERE rn = 1
ORDER BY id1, id2;

逻辑说明：通过ROW_NUMBER()按id1分组、id2升序排序，标记每个分组的第一行为rn=1，直接筛选出这些行生成副本，同时保留原表chk=1的行。

方案2：用CTE提升可读性

如果需要更清晰的逻辑拆分，可以用CTE（公共表表达式）把首行标记的逻辑单独提取，性能和方案1一致：

WITH RankedTest AS (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY id2) AS rn
    FROM Test
)
SELECT id1, id2, chk, ref
FROM RankedTest
WHERE chk = 1
UNION ALL
SELECT id1, 0, 0, ref
FROM RankedTest
WHERE rn = 1
ORDER BY id1, id2;

方案3：用EXISTS判断首行（适合特定场景）

如果你的id2在每个id1分组内是连续递增的，也可以用EXISTS来判断是否为当前分组的首行，但这种方式在数据量大时性能不如窗口函数：

SELECT id1, id2, chk, ref
FROM Test
WHERE chk = 1
UNION ALL
SELECT id1, 0, 0, ref
FROM Test a
WHERE NOT EXISTS (
    SELECT 1 FROM Test b
    WHERE b.id1 = a.id1 AND b.id2 < a.id2
)
ORDER BY id1, id2;

优化点说明 #

原查询需要对Test表执行两次全表扫描（一次筛选chk=1，一次分组聚合找最小id2），而窗口函数方案只需要一次全表扫描，在数据量较大时能明显降低IO开销，同时逻辑更简洁易维护。

内容的提问来源于stack exchange，提问作者aludebe