6094
社区成员
- 主页
请问这张表该如何写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这个字段是已知的。
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这个字段是已知的。
...全文
请发表友善的回复…
发表回复
soapyboy 2004-01-17
--或
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
FROM TEST AS P
GROUP BY P.F2) AS P1
GO
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
FROM TEST AS P
GROUP BY P.F2) AS P1
GO
soapyboy 2004-01-17
-------------------------------
--***************************--
-------------------------------
create table test(f1 char(1),f2 char(4),f3 int)
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
--***************************--
-------------------------------
create table test(f1 char(1),f2 char(4),f3 int)
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
windf726 2004-01-12
都是写什么东西啊
没一个正确的
没一个正确的
火电 2004-01-04
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
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
火电 2004-01-04
原表如下：
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
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
johnnyshabi 2004-01-04
喂上面的!最基本的语法都不懂,还回复!
NoThatNo 2004-01-02
全是笨蛋，看我的：
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, 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
xuzhengchang1029 2003-12-24
create table #a (department nvarchar(50),goodsname nvarchar(50),quantity int)
insert into #a values ('部门M','货品A',100)
insert into #a values ('部门M','货品B',200)
insert into #a values ('部门N','货品C',200)
insert into #a values ('部门N','货品B',5)
declare @sql varchar(8000)
set @sql = 'select department ,'
select @sql = @sql + 'sum(case goodsname when '''+goodsname+'''
then quantity else 0 end) as '+goodsname+','
from (select distinct goodsname from #a ) as a
select @sql = left(@sql,len(@sql)-1) + ' from #a group by department'
exec(@sql)
drop table #a
go
你看看这段,就可以实现
insert into #a values ('部门M','货品A',100)
insert into #a values ('部门M','货品B',200)
insert into #a values ('部门N','货品C',200)
insert into #a values ('部门N','货品B',5)
declare @sql varchar(8000)
set @sql = 'select department ,'
select @sql = @sql + 'sum(case goodsname when '''+goodsname+'''
then quantity else 0 end) as '+goodsname+','
from (select distinct goodsname from #a ) as a
select @sql = left(@sql,len(@sql)-1) + ' from #a group by department'
exec(@sql)
drop table #a
go
你看看这段,就可以实现
wangrong001 2003-12-24
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
上头的有两个地方rong改为tabelName了，试试！
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
上头的有两个地方rong改为tabelName了，试试！
wangrong001 2003-12-24
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
兄弟，这准没错，我验证了
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
兄弟，这准没错，我验证了
shawnzhao 2003-12-18
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
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
cb8011 2003-12-18
顶
bluesky1980 2003-12-16
学习
shi11 2003-12-13
可以這樣寫：
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
)
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
)
anuoxiang 2003-12-09
顶一下。
anuoxiang 2003-12-08
能写的详细一些吗？看不懂哦。。。。
xpace 2003-12-08
子查询
select
from (
select ... from
select ... from
...
)
select
from (
select ... from
select ... from
...
)