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

求一条复杂的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,.....)的增项合计数,不知可否实现?谢谢
...全文
104 点赞 收藏 10
写回复
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+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告