原表如下:
F1 F2 F3
------------
A S01 3
A S02 3
A S03 3
B S01 4
B S02 4
B S03 4
C S01 5
C S02 5
C S03 5
C S04 5
请问如何写SQL使其变成如下表?
F2 A B C SUM
-------------------------
S01 3 4 5 12
S02 3 4 5 12
S03 3 4 5 12
S04 0 0 5 5
A,B,C这个字段是已知的。
...全文
4517打赏收藏
请问这张表该如何写SQL?
原表如下: F1 F2 F3 ------------ A S01 3 A S02 3 A S03 3 B S01 4 B S02 4 B S03 4 C S01 5 C S02 5 C S03 5 C S04 5 请问如何写SQL使其变成如下表? F2 A B C SUM ------------------------- S01 3 4 5 12 S02 3 4 5 12 S03 3 4 5 12 S04 0 0 5 5 A,B,C这个字段是已知的。
SELECT P1.*, (P1.A + P1.B + P1.C ) AS SUM
FROM (SELECT F2,
SUM(CASE P.F1 WHEN 'A' THEN P.F3 ELSE 0 END) AS A,
SUM(CASE P.F1 WHEN 'B' THEN P.F3 ELSE 0 END) AS B,
SUM(CASE P.F1 WHEN 'C' THEN P.F3 ELSE 0 END) AS C
insert into test(F1,F2,F3)
SELECT 'A','S01','3'
UNION ALL SELECT 'A', 'S02','3'
UNION ALL SELECT 'A', 'S03','3'
UNION ALL SELECT 'B', 'S01','4'
UNION ALL SELECT 'B', 'S02','4'
UNION ALL SELECT 'B', 'S03','4'
UNION ALL SELECT 'C', 'S01','5'
UNION ALL SELECT 'C', 'S02','5'
UNION ALL SELECT 'C', 'S03','5'
UNION ALL SELECT 'C', 'S04','5'
-------------------------------
--***************************--
-------------------------------
SELECT F2,
SUM(CASE f1 WHEN 'A' THEN F3 ELSE 0 END) AS A,
SUM(CASE f1 WHEN 'B' THEN F3 ELSE 0 END) AS B,
SUM(CASE f1 WHEN 'C' THEN F3 ELSE 0 END) AS C,
SUM(F3) AS SUM
FROM TEST
GROUP BY F2
-------------------------------
--***************************--
-------------------------------
F2 A B C SUM
-------------------------
S01 3 4 5 12
S02 3 4 5 12
S03 3 4 5 12
S04 0 0 5 5
select f2,
case when f1 = A then f3 else 0 end as A,
case when f1 = B then f3 else 0 end as B,
case when f1 = C then f3 else 0 end as C,
SUM(F3) AS SUM
FROM TABLE
GROUP BY F2
全是笨蛋,看我的:
select F2, sum(A),Sum(B),Sum(C),Sum(和)
from
(
select A=F3,B=0,C=0,和=F3 from tableName where F1='A'
select 0,F3,0,F3 from tableName where F1='B'
select 0,0,F3,F3 from tableName where F1='C'
) a
group by F2
select F2,decode(sum(A),'','0',sum(A),sum(A)) A,decode(sum(B),'','0',sum(B),sum(B)) B,decode(sum(C),'','0',sum(C),sum(C)) C,decode(sum(SUM),'','0',sum(SUM),sum(SUM)) SUM
from
(
select f2,decode(f1,'a',f3) A,decode(f1,'b',0) B,decode(f1,'c',0) C,decode(f1,'a',f3) SUM
from tableName
where f1='a' union
select f2,decode(f1,'a',0) A,decode(f1,'b',f3) B,decode(f1,'c',0) C,decode(f1,'b',f3) SUM
from tableName
where f1='b' union
select f2,decode(f1,'a',0) A,decode(f1,'b',0) B,decode(f1,'c',f3) C,decode(f1,'c',f3) SUM
from tableName
where f1='c'
)
group by f2
select F2,decode(sum(A),'','0',sum(A),sum(A)) A,decode(sum(B),'','0',sum(B),sum(B)) B,decode(sum(C),'','0',sum(C),sum(C)) C,decode(sum(SUM),'','0',sum(SUM),sum(SUM)) SUM
from
(
select f2,decode(f1,'a',f3) A,decode(f1,'b',0) B,decode(f1,'c',0) C,decode(f1,'a',f3) SUM
from rong
where f1='a' union
select f2,decode(f1,'a',0) A,decode(f1,'b',f3) B,decode(f1,'c',0) C,decode(f1,'b',f3) SUM
from rong
where f1='b' union
select f2,decode(f1,'a',0) A,decode(f1,'b',0) B,decode(f1,'c',f3) C,decode(f1,'c',f3) SUM
from tabelName
where f1='c'
)
group by f2
兄弟,这准没错,我验证了
select F2,sum(A),sum(B),sum(C),sum(D)
from
(
select F2,decode(F1,'A',F3,0) A
,decode(F1,'B',F3,0) B
,decode(F1,'C',F3,0) C
,(decode(F1,'A',F3,0)+decode(F1,'B',F3,0)+decode(F1,'C',F3,0)) D
from table_name
)
group by F2
可以這樣寫:
SELECT F1,A,B,C,A+B+C AS "SUM" FROM (
SELECT C.F1,C.F2,NVL(A.F3,0) AS A,NVL(B.F3,0) AS B,C.F3 AS C
FROM
(SELECT F1,F2,F3 FROM SH_TEST WHERE F1='A') A,
(SELECT F1,F2,F3 FROM SH_TEST WHERE F1='B') B,
(SELECT F1,F2,F3 FROM SH_TEST WHERE F1='C') C
WHERE A.F2=B.F2(+)
AND A.F2(+)=C.F2
)