TABLE: TA
fid fqty fgood_flag
1 20 Y
1 30 N
2 40 Y
2 50 N合并为
fid fgood ffail
1 20 30
2 40 50
请问这样的语句该怎么写?
fid fqty fgood_flag
1 20 Y
1 30 N
2 40 Y
2 50 N合并为
fid fgood ffail
1 20 30
2 40 50
请问这样的语句该怎么写?
调试欢乐多
insert into ta
select 1,20,'Y' from dual
union all
select 1,30,'N' from dual
union all
select 2,40,'Y' from dual
union all
select 2,50,'N' from dual
//////////
select fid,sum(t.fgood) fgood ,sum(t.ffail) ffail
from(
select fid,sum(decode(fgood_flag,'Y',fqty)) fgood ,sum(decode(fgood_flag,'N',fqty)) ffail
from ta group by fid,fgood_flag
)t
group by fid
/////////输出结果
1 20 30
2 40 50
INSERT INTO TA VALUES('1',20,'Y');
INSERT INTO TA VALUES('1',30,'N');
INSERT INTO TA VALUES('2',40,'Y');
INSERT INTO TA VALUES('2',50,'N');
INSERT INTO TA VALUES('3',50,'N');
INSERT INTO TA VALUES('4',40,'Y');
COMMIT;SELECT T1.fid,fgood,ffail FROM(
SELECT fid,fqty fgood FROM TA WHERE fgood_flag='Y')T1,
(SELECT fid,fqty ffail FROM TA WHERE fgood_flag='N')T2
WHERE T1.fid=T2.fid(+)
UNION
SELECT T2.fid,fgood,ffail FROM(
SELECT fid,fqty fgood FROM TA WHERE fgood_flag='Y')T1,
(SELECT fid,fqty ffail FROM TA WHERE fgood_flag='N')T2
WHERE T2.fid=T1.fid(+);
如果fgood_flag是有一个N就有一个Y的话,就不用UNION了,用上面的语句就好,也不用外连.
写的语句太长了,等高手写好的方法吧.
select fid,sum(decode(fgood_flag,'Y',fqty)) fgood ,
sum(decode(fgood_flag,'N',fqty)) ffail
from ta
group by fid