主页
基础类
应用实例
新技术前沿

求一条复杂的sql

yhyhai 2008-01-14 02:01:09

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

...全文

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

-狙击手- 2008-01-14

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 行）

*/

回复赞

-狙击手- 2008-01-14

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 行）

*/

回复赞

yhyhai 2008-01-14

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(减项))
请问我又应该如何写呢?谢谢!

回复赞

areswang 2008-01-14

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

回复赞

areswang 2008-01-14

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
----------------------------
楼主应该是要的这种吧？

回复赞

free1879 2008-01-14



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

回复赞

changjiangzhibin 2008-01-14

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

回复赞

SeerMi 2008-01-14

楼主应该是这个意思

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

回复赞

SeerMi 2008-01-14

是不是这个意思

select sum(金额) from X

回复赞

kk19840210 2008-01-14

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

回复赞

发动态

发帖子

MS-SQL Server

创建于2007-09-28

加入

3.2w+

社区成员

25.4w+

社区内容

MS-SQL Server相关内容讨论专区

申请成为版主

社区公告

暂无公告