我的测试表结构,是下面这样的:这边的ID相当于你的ID,NAME相当于你的"汽车A"啊什么的值, ID NAME ---------- -------------------- 1 A 2 B 3 C 4 D 5 E 6 F 1 B 1 C 4 A 4 B 4 C执行下面的SQL之后: SELECT id, str FROM (SELECT id, row_number() over(PARTITION BY id ORDER BY name) AS rn, name || lead(',' || name, 1) over(PARTITION BY id ORDER BY name) || lead(',' || name, 2) over(PARTITION BY id ORDER BY name) || lead(',' || name, 3) over(PARTITION BY id ORDER BY name) AS str FROM test_row_cell) WHERE rn = 1 ORDER BY 1; 得到下面的结果: ID STR ---------- -------------------------------------------------------------------------------- 1 A,B,C 2 B 3 C 4 A,B,C,D 5 E 6 F
这边有个,希望有帮组
ID NAME
---------- --------------------
1 A
2 B
3 C
4 D
5 E
6 F
1 B
1 C
4 A
4 B
4 C执行下面的SQL之后:
SELECT id, str
FROM (SELECT id,
row_number() over(PARTITION BY id ORDER BY name) AS rn,
name || lead(',' || name, 1) over(PARTITION BY id ORDER BY name) || lead(',' || name, 2) over(PARTITION BY id ORDER BY name) || lead(',' || name, 3) over(PARTITION BY id ORDER BY name) AS str
FROM test_row_cell)
WHERE rn = 1
ORDER BY 1; 得到下面的结果:
ID STR
---------- --------------------------------------------------------------------------------
1 A,B,C
2 B
3 C
4 A,B,C,D
5 E
6 F