请教下各位一个SQL写法,突然想不出来了
p_name sun_p_name
A 111
A 122
C 333
D 555
B 566
B 3221321显示为:
p_name sun_p_name
A 111
122
B 566
3221321
C 333
D 555
p_name sun_p_name
A 111
A 122
C 333
D 555
B 566
B 3221321显示为:
p_name sun_p_name
A 111
122
B 566
3221321
C 333
D 555
A 111
这里没有显示 122
-----------------------------------------------WITH t AS
(SELECT 'A' AS p_name, '111' AS sun_pname
FROM dual
UNION ALL
SELECT 'A' AS p_name, '122' AS sun_pname
FROM dual
UNION ALL
SELECT 'C' AS p_name, '333' AS sun_pname
FROM dual
UNION ALL
SELECT 'D' AS p_name, '555' AS sun_pname
FROM dual
UNION ALL
SELECT 'B' AS p_name, '566' AS sun_pname
FROM dual
UNION ALL
SELECT 'B' AS p_name, '3221321' AS sun_pname FROM dual)SELECT decode(row_number() over(PARTITION BY p_name ORDER BY sun_pname), 1, p_name, ''), sun_pname FROM t