已经一个表TestInfo有三个字段TPI,TSI,SNO,如下所示数据,
TPI TSI SNO
1 1 1
1 5 2
1 8 3
1 9 4
1 10 5
16 984 1
16 985 2
54 283 1
57 258 1
57 500 2
57 503 3现在要得到下面这样的数据
TPI TSI2 NUM
1 1,5,8,9,10 5
16 984,985 2
54 283 1
57 258,500,503 3
即新记录集中TPI唯一, TSI2为TPI对应的TSI的**,NUM为TPI对应的TSI的最大序号(TSI2的个数).
请问oracle下sql语句怎么写?谢谢!
TPI TSI SNO
1 1 1
1 5 2
1 8 3
1 9 4
1 10 5
16 984 1
16 985 2
54 283 1
57 258 1
57 500 2
57 503 3现在要得到下面这样的数据
TPI TSI2 NUM
1 1,5,8,9,10 5
16 984,985 2
54 283 1
57 258,500,503 3
即新记录集中TPI唯一, TSI2为TPI对应的TSI的**,NUM为TPI对应的TSI的最大序号(TSI2的个数).
请问oracle下sql语句怎么写?谢谢!
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,substr(max(sys_connect_by_path(name,';')),2) name
from (select grade,name,row_number() over(partition by grade order by name) rn from tb)
start with rn=1
connect by rn= prior rn+1 and
connect_by_root(grade)=grade
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
--
10g的实现:
with t as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual)
select grade,wmsys.wm_concat(name) name
from t
group by grade;
--
GRADE NAME
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb
from TestInfo
group by tpi;
--或者
select TPI,wm_conca(tTSI),count(*)
from TestInfo
group by tpi;
wm_concat(TSI),
max(SNO)
from TestInfo
group by tpi
(
TPI NUMBER(4),
TSI NUMBER(4),
SNO NUMBER(4)
);
INSERT INTO T90 VALUES(1, 1, 1);
INSERT INTO T90 VALUES(1, 5, 2);
INSERT INTO T90 VALUES(1, 8, 3);
INSERT INTO T90 VALUES(1, 9, 4);
INSERT INTO T90 VALUES(1, 10, 5);
INSERT INTO T90 VALUES(16, 984, 1);
INSERT INTO T90 VALUES(16, 985, 2);
INSERT INTO T90 VALUES(54, 283, 1);
INSERT INTO T90 VALUES(57, 258, 1);
INSERT INTO T90 VALUES(57, 500, 2);
INSERT INTO T90 VALUES(57, 503, 3);
实测结果: