--糾正如下: SELECT SID ,SUM(CASE WHEN IO='应收' THEN Fee ELSE 0 END) feeI ,SUM(CASE WHEN IO='应收' THEN 0 ELSE fee END) feeO FROM 表A GROUP BY SID
试试这个: --drop table a --gocreate table a(ID int, sID varchar(10), Name varchar(20), Fee int, IO varchar(10))
insert into a select 1 ,'s1', '运费', 1500 ,'应收' union all select 2 ,'s1', '劳务费', 1200 ,'应收' union all select 3 ,'s1', '报关费', 800 ,'应付' union all select 4 ,'s1', '业务费', 500 ,'应付' union all select 5 ,'s2', '邮费', 300 ,'应付' union all select 6 ,'s3', '邮费', 400 ,'应付' goselect sID, SUM(case when io = '应收' then Fee else 0 end) as feeI, SUM(case when io = '应付' then Fee else 0 end) as feeO from a group by sID /* sID feeI feeO s1 2700 1300 s2 0 300 s3 0 400 */
--糾正如下:
SELECT SID
,SUM(CASE WHEN IO='应收' THEN Fee ELSE 0 END) feeI
,SUM(CASE WHEN IO='应收' THEN 0 ELSE fee END) feeO
FROM 表A
GROUP BY SID
--drop table a
--gocreate table a(ID int, sID varchar(10), Name varchar(20),
Fee int, IO varchar(10))
insert into a
select 1 ,'s1', '运费', 1500 ,'应收' union all
select 2 ,'s1', '劳务费', 1200 ,'应收' union all
select 3 ,'s1', '报关费', 800 ,'应付' union all
select 4 ,'s1', '业务费', 500 ,'应付' union all
select 5 ,'s2', '邮费', 300 ,'应付' union all
select 6 ,'s3', '邮费', 400 ,'应付'
goselect sID,
SUM(case when io = '应收' then Fee else 0 end) as feeI,
SUM(case when io = '应付' then Fee else 0 end) as feeO
from a
group by sID
/*
sID feeI feeO
s1 2700 1300
s2 0 300
s3 0 400
*/