[SQL查询]有个查询需求,SQL语句总是想不出,请大家帮忙

显示全部楼层 · 2007-1-6 21:14:12

假如有这样的表,
P
T
Z
------
------
---
1
2
A
1
2
B
3
5
D
要求通过sql语句,以P和T分组, 输出为:
P
T
Z
------
------
---
1
2
A,B
3
5
D
其中"A,B"用什么做分隔符都无所谓.但可能会有若干个,比如A,B,C,D

千问 · 2007-1-6 21:14:12

http://atgc.itpub.net/post/22412/251110

千问 · 2007-1-6 21:14:12

分组的条件固定不？

千问 · 2007-1-6 21:14:12

SELECT p, t, substr(MAX(sys_connect_by_path(z, ',')), 2)
FROM (SELECT t1.*, row_number() over(PARTITION BY p, t ORDER BY z) rn

FROM t1) a
START WITH rn = 1
CONNECT BY p = PRIOR p
AND t = PRIOR t
AND rn = PRIOR rn + 1
GROUP BY p, t

千问 · 2007-1-6 21:14:12

感谢 wait4friend !!
成功了..

千问 · 2007-1-6 21:14:12

http://www.itpub.net/showthread.php?s=&threadid=606514
http://www.itpub.net/showthread.php?s=&threadid=614563

千问 · 2007-1-6 21:14:12

zhouwf0726atgc
的方法都是不错.