-- 创建数据表 create table tb ( id int, name varchar(10), data int ) go --测试数据 insert into tb select '1','a',123 union allselect '1','b',456 union all select '2','a',789 union allselect '2','b',159 go select id, sum(case name when 'a' then data else 0 end) a, sum(case name when 'b' then data else 0 end) b from tb group by id --1 123 456 --2 789 159
数字型可以,但如果name和data是varchar型的怎么办?
--name不用管什么类型,将data转一下,这没环境,你测下吧select id, sum(case name when 'a' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) a, sum(case name when 'b' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) b from tb group by id
select t.id,max(decode(t.name,'a',t.data)),max(decode(t.name,'b',t.data)) from tb t group by t.id
SELECT A.ID,A.DATA,B.DATA FROM (SELECT ID,DATA FROM T WHERE NAME = 'A') A FULL JOIN SELECT ID,DATA FROM T WHERE NAME = 'B') B ON A.ID = B.ID;
SELECT ID,MAX(CASE WHEN NAME='a' THEN DATA ELSE NULL END) A, max(CASE WHEN NAME='b' THEN DATA ELSE NULL END) b FROM tb GROUP BY ID;
create table t_data ( id varchar(10), name varchar(10), data varchar(10) ) sql实现如下: select t2.id,wm_concat(t2.data) from t_data t2 group by t2.id order by t2.id
select A.id, A.a, sum(decode(A.name, 'a', A.data, 0)) a sum(decode(A.name, 'b', A.data, 0)) b from A
select A.id, A.a, sum(decode(A.name, 'a', A.data, 0)) a sum(decode(A.name, 'b', A.data, 0)) b from A group by A.id,A.a
路过路过,select id, sum(case name when 'a' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) a, sum(case name when 'b' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) b from tb group by id 推荐这个
create table tb ( id int, name varchar(10), data int )insert into tb select '1','a',123 from dual union allselect '1','b',456 from dual union all select '2','a',789 from dual union allselect '2','b',159 from dual ; commit;select id,a,b from ( select id,name,data from tb ) pivot ( sum(data) for name in ('a' as a,'b' as b) )
select id,max(decode(name,'a',data)) as a, max(decode(name,'b',data)) as b from data1 group by id;
select id, sum(decode(name,1,data) as a, sum(decode(name,2,date) as b from table group by id
select id, sum(decode(name,1,data) as a, sum(decode(name,2,date) as b from table group by id
-- 创建数据表
create table tb
(
id int,
name varchar(10),
data int
)
go
--测试数据
insert into tb select '1','a',123 union allselect '1','b',456 union all select '2','a',789 union allselect '2','b',159
go
select id,
sum(case name when 'a' then data else 0 end) a,
sum(case name when 'b' then data else 0 end) b
from tb
group by id
--1 123 456
--2 789 159
--name不用管什么类型,将data转一下,这没环境,你测下吧select id,
sum(case name when 'a' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) a,
sum(case name when 'b' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) b
from tb
group by id
FROM (SELECT ID,DATA FROM T WHERE NAME = 'A') A
FULL JOIN SELECT ID,DATA FROM T WHERE NAME = 'B') B
ON A.ID = B.ID;
SELECT ID,MAX(CASE WHEN NAME='a' THEN DATA ELSE NULL END) A,
max(CASE WHEN NAME='b' THEN DATA ELSE NULL END) b
FROM tb GROUP BY ID;
create table t_data
(
id varchar(10),
name varchar(10),
data varchar(10)
)
sql实现如下:
select
t2.id,wm_concat(t2.data)
from t_data t2
group by t2.id
order by t2.id
A.a,
sum(decode(A.name, 'a', A.data, 0)) a
sum(decode(A.name, 'b', A.data, 0)) b
from A
A.a,
sum(decode(A.name, 'a', A.data, 0)) a
sum(decode(A.name, 'b', A.data, 0)) b
from A
group by A.id,A.a
sum(case name when 'a' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) a,
sum(case name when 'b' then cast(to_number('data') AS NUMBER(8,2)) else 0 end) b
from tb
group by id
推荐这个
(
id int,
name varchar(10),
data int
)insert into tb select '1','a',123 from dual union allselect '1','b',456 from dual union all select '2','a',789 from dual union allselect '2','b',159 from dual ;
commit;select id,a,b from (
select id,name,data from tb
)
pivot (
sum(data)
for name in ('a' as a,'b' as b)
)
max(decode(name,'b',data)) as b
from data1 group by id;
sum(decode(name,1,data) as a,
sum(decode(name,2,date) as b
from table
group by id
select id,
sum(decode(name,1,data) as a,
sum(decode(name,2,date) as b
from table
group by id