求一条复杂的sql

[yhyhai]

有如下一张表
X
person_id item_type 金额
p101 增项 10
p101 增项 20
p101 增项合计 0
p102 增项 10
p102 增项 20
p102 增项合计 0
..............................
.............................
问题
我想用一条sql来得到所有person_id(p101,p102,.....)的增项合计数,不知可否实现?谢谢

[kk19840210]

select person_id,item_type,合计=sum(金额) from x group by person_id,item_type having item_type='增项'

[SeerMi]

是不是这个意思

select sum(金额) from X

[SeerMi]

楼主应该是这个意思

update a set a.金额 = b.summoney 
from x as a,
	(select person_id,sum(金额) summoney from x where item_type = '增项' group by person_id) as b
where a.item_type='增项合计' and a.person_id = b.person_id 

[changjiangzhibin]

create table X(person_id varchar(10),item_type varchar(10),金额 int)
go

insert X select 'p101','增项',10
insert X select 'p101','增项',20

insert X select 'p102','增项',10
insert X select 'p102','增项',20
go

select person_id,sum(金额) from X group by person_id

drop table X

[free1879]

create table #t(person_id varchar(20), item_type varchar(20),金额 int)

insert  #t select 'p101','增项',10 union all
               select 'p101','增项',10 union all
			   select 'p101','增项合计',0 union all
               select 'p102','增项',10 union all
               select 'p102','增项',10 union all
			   select 'p102','增项合计',0 


update #t
set #t.金额= a.总金额
from (select person_id, sum(金额) 总金额  from #t
group by person_id) a
where a.person_id = #t.person_id and #t.item_type = '增项合计'

--select * from #t

/*结果
p101	增项	10
p101	增项	10
p101	增项合计	20
p102	增项	10
p102	增项	10
p102	增项合计	20
*/

--drop table #t 
--drop table #tmp

[areswang]

create table a(person_id varchar(10),item_type varchar(10),金额 int)
go
insert into a select 'p101','增项',10
union all
select 'p101','增项',20
union all
select 'p102','增项',10
union all
select 'p102','增项',20


SELECT person_id,item_type,金额
FROM(
--明细
SELECT person_id,item_type,金额
,a1=0,a2=person_id,a3=0
FROM a
-- GROUP BY person_id,item_type
UNION ALL
--各Item合计
SELECT ' ', ' 增项合计 ',金额=SUM(金额)
,a1=0,a2=person_id,a3=1
FROM a
GROUP BY person_id


)a
ORDER BY a1,a2,a3
----------------------------
楼主应该是要的这种吧？

[areswang]

SELECT person_id,item_type,金额
FROM(
--明细
SELECT person_id,item_type,金额
,a1=person_id,a2=0
FROM a

UNION ALL
--各Item合计
SELECT ' ', ' 增项合计 ',金额=SUM(金额)
,a1=person_id,a2=1
FROM a
GROUP BY person_id


)a
ORDER BY a1,a2

[yhyhai]

3楼的说的对;
但如果我想把表改成这样:
X
person_id item_type 金额
p101 增项 10
p101 增项 20
p101 减项 10
p101 减项 20
p101 合计 0
p102 增项 10
p102 增项 20
p102 减项 10
p102 减项 20
p102 合计 0
.....................................

我想在合计行里得到所有person_id(p101,p102......)的:(sum(增项)-sum(减项))
请问我又应该如何写呢?谢谢!

[-狙击手-]

create table #t(person_id varchar(20), item_type varchar(20),金额 int)

insert  #t select 'p101','增项',10 union all
               select 'p101','减项',10 union all
--               select 'p101','增项合计',0 union all
               select 'p102','增项',10 union all
               select 'p102','减项',10-- union all
--               select 'p102','增项合计',0 
go
select * 
from (
	select * from #t
	
	union 
	select person_id ,'合计' as item_type,sum(case when item_type = '增项' then 金额 else -1 * 金额 end)
	from #t group by person_id) a
order by person_id ,case when item_type = '合计' then 1 else 0 end
drop table #t

/*

person_id            item_type            金额          
-------------------- -------------------- ----------- 
p101                 减项                   10
p101                 增项                   10
p101                 合计                   0
p102                 减项                   10
p102                 增项                   10
p102                 合计                   0

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

[-狙击手-]

create table #t(person_id varchar(20), item_type varchar(20),金额 int)

insert  #t select 'p101','增项',50 union all
               select 'p101','减项',10 union all
--               select 'p101','增项合计',0 union all
               select 'p102','增项',10 union all
               select 'p102','减项',30-- union all
--               select 'p102','增项合计',0 
go
select * 
from (
	select * from #t
	
	union 
	select person_id ,'合计' as item_type,sum(case when item_type = '增项' then 金额 else -1 * 金额 end)
	from #t group by person_id) a
order by person_id ,case when item_type = '合计' then 1 else 0 end
drop table #t

/*

person_id            item_type            金额          
-------------------- -------------------- ----------- 
p101                 减项                   10
p101                 增项                   50
p101                 合计                   40
p102                 减项                   30
p102                 增项                   10
p102                 合计                   -20

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