求一SQL语句

[vfan2010]

根据以下数据生成后面的结果，很容易看出来是什么逻辑。就是把同一个SubContractorID下的IsChangeOrder为0,1的分别求和。

SubContractorID SubContractDollarValue IsChangeOrder
-------------------- --------------------------------------- -------------
125 50.00 1
125 125.00 1
125 100.00 0
13 6.00 1
13 5000.00 0
13 500.00 1
13 500.00 0


---------------------------------
SubContractorID NotChangeOrderAmount ChangeOrderAmount
125 100 175
13 5500 506

[中国风]

select
[SubContractorID],
[NotChangeOrderAmount]=sum(case when IsChangeOrder =0 then SubContractDollarValue else 0 end),
[ChangeOrderAmount]=sum(case when IsChangeOrder =1 then SubContractDollarValue else 0 end)
from
t
group by SubContractorID

[OracleRoob]

select 
    SubContractorID,
    sum(case when IsChangeOrder=0 then SubContractDollarValue else 0 end) as NotChangeOrderAmount,
    sum(case when IsChangeOrder=1 then SubContractDollarValue else 0 end) as ChangeOrderAmount
from 表
group by SubContractorID

[中国风]

select 
	[SubContractorID], 
	[NotChangeOrderAmount]=sum(case   when   IsChangeOrder   =0   then   SubContractDollarValue     else   0   end), 
	[ChangeOrderAmount]=sum(case   when   IsChangeOrder   =1   then   SubContractDollarValue     else   0   end) 
from   
	t 
group   by   SubContractorID

[dawugui]

select SubContractorID,
  sum(case IsChangeOrder when 0 then SubContractDollarValue else 0 end) NotChangeOrderAmount,
  sum(case IsChangeOrder when 1 then SubContractDollarValue else 0 end) ChangeOrderAmount 
from tb
group by SubContractorID

[kuangdp]

select subcontratorID ,sum(case when ischangeorder=0 then SubContractDollarValue END) as NotChangeOrderAmount,
sum(case when ischangeorder=1 then SubContractDollarValue END) as ChangeOrderAmount
from TABLE
GROUP BY subcontratorID

[dawugui]

create table tb(SubContractorID int,SubContractDollarValue int,IsChangeOrder int)
insert into tb values(125,50.00   ,1) 
insert into tb values(125,125.00  ,1) 
insert into tb values(125,100.00  ,0) 
insert into tb values(13 ,6.00    ,1) 
insert into tb values(13 ,5000.00 ,0) 
insert into tb values(13 ,500.00  ,1) 
insert into tb values(13 ,500.00  ,0)
go
select SubContractorID,
  sum(case IsChangeOrder when 0 then SubContractDollarValue else 0 end) NotChangeOrderAmount,
  sum(case IsChangeOrder when 1 then SubContractDollarValue else 0 end) ChangeOrderAmount 
from tb
group by SubContractorID
order by SubContractorID desc
drop table tb

/*
SubContractorID NotChangeOrderAmount ChangeOrderAmount 
--------------- -------------------- ----------------- 
125             100                  175
13              5500                 506

（所影响的行数为 2 行）
*/

[fcuandy]

SELECT SubContractorID,
	SUM(SubContractDollarValue * (IsChangeOrder^1) NoChangeOrderAmount
	SUM(SubContractDollarValue * IsChangeOrder) ChangeOrderAmount
	FROM tb
	GROUP BY SubContractorID