table1
col1(字符串) col2(字符串) col3(数字)
a1 b1 1
a1 b2 2
a3 b3 3
AA1 BB1 4
AA1 BB2 5
AAA1 BBB1 61>SQL怎么实现下面结果
col1(字符串) col2(字符串) col3(数字)
a1 b1,B2,B3 1+2+3=6
AA1 BB1,BB2 4+5=9
AAA1 BBB1 62>SQL怎么实现结果
列: a1 AA1 AAA1
行: b1,B2,B3 BB1,BB2 BBB1
SQL如何实现记录分组
col1(字符串) col2(字符串) col3(数字)
a1 b1 1
a1 b2 2
a3 b3 3
AA1 BB1 4
AA1 BB2 5
AAA1 BBB1 61>SQL怎么实现下面结果
col1(字符串) col2(字符串) col3(数字)
a1 b1,B2,B3 1+2+3=6
AA1 BB1,BB2 4+5=9
AAA1 BBB1 62>SQL怎么实现结果
列: a1 AA1 AAA1
行: b1,B2,B3 BB1,BB2 BBB1
SQL如何实现记录分组
with t as
(select 'a1' col1, 'b1' col2, 1 col3 from dual
union all
select 'a1', 'b2', 2 from dual
union all
select 'a1', 'b3', 3 from dual
union all
select 'AA1', 'BB1', 4 from dual
union all
select 'AA1', 'BB2', 5 from dual
union all
select 'AAA1', 'BBB1', 6 from dual)
select t.col1,
listagg(col2, ',') within group(order by rownum) col2,
dbms_aw.eval_number(listagg(col3, '+') within group(order by rownum)) col3
from t
group by t.col1;
with t as
(select 'a1' col1, 'b1' col2, 1 col3 from dual
union all
select 'a1', 'b2', 2 from dual
union all
select 'a1', 'b3', 3 from dual
union all
select 'AA1', 'BB1', 4 from dual
union all
select 'AA1', 'BB2', 5 from dual
union all
select 'AAA1', 'BBB1', 6 from dual)
select t.col1,
listagg(col2, ',') within group(order by rownum) col2,
dbms_aw.eval_number(listagg(col3, '+') within group(order by rownum)) col3
from t
group by t.col1;
with t as
(select 'a1' col1, 'b1' col2, 1 col3
from dual
union all
select 'a1', 'b2', 2
from dual
union all
select 'a1', 'b3', 3
from dual
union all
select 'AA1', 'BB1', 4
from dual
union all
select 'AA1', 'BB2', 5
from dual
union all
select 'AAA1', 'BBB1', 6 from dual)
select max(decode(col1, 'a1', col2)) a1,
max(decode(col1, 'AA1', col2)) AA1,
max(decode(col1, 'AAA1', col2)) AAA1
from (select t.col1, listagg(col2, ',') within group(order by rownum) col2
from t
group by t.col1);
回答问题很迅速啊。
dbms_aw.eval_number 这个第一次见到 学习了。
不过这个可以直接sum(col3)吧
回答问题很迅速啊。
dbms_aw.eval_number 这个第一次见到 学习了。
不过这个可以直接sum(col3)吧
上班无聊随便玩玩。。