--先贴下来测试数据
create table ta(ID varchar(50) primary key, SubID varchar(50),
Fee varchar(50), InOrOut varchar(50))
insert ta select '01', 'a', '100', '收'
union all select '02', 'a', '200', '付'
union all select '03', 'a', '50', '付'
union all select '04', 'a', '300', '收'
union all select '05', 'b', '150', '付'
union all select '06', 'b', '200', '收'
--查询
select * from ta
--清除
drop table ta
create table ta(ID varchar(50) primary key, SubID varchar(50),
Fee varchar(50), InOrOut varchar(50))
insert ta select '01', 'a', '100', '收'
union all select '02', 'a', '200', '付'
union all select '03', 'a', '50', '付'
union all select '04', 'a', '300', '收'
union all select '05', 'b', '150', '付'
union all select '06', 'b', '200', '收'
--查询
select * from ta
--清除
drop table ta
a.ID,
a.SubID,
[Fee(收)] = SUM(case b.InOrOut when '收' then b.Fee else 0 end),
[付费用合计] = SUM(case b.InOrOut when '付' then b.Fee else 0 end),
[差额] = SUM(case b.InOrOut when '收' then b.Fee else -b.Fee end)
from
(select * from 表 where InOrOut = '收') a
left join
表 b
where
a.ID>=b.ID and a.SubID=b.SubID
group by
a.ID,a.SubID
order by
a.ID
from tablename M,
(select a.id as FID,(select max(id) as SID from tablename where id<a.id and InOrOut='收') from tablename a where InOrOut='付') N,tablename H
where M.InOrOut='收' and M.id=N.SID and H.id=N.FID
group by M.id
subid等于a的记录有两条,既然是统计值,那要ID字段还有什么意义呢?
create table #t(ID varchar(50),SubID varchar(50),Fee varchar(50),InOrOut varchar(50))
insert into #t select '01','a','100','收'
insert into #t select '02','a','200','付'
insert into #t select '03','a','50' ,'付'
insert into #t select '04','a','300','收'
insert into #t select '05','b','150','付'
insert into #t select '06','b','200','收'
--执行查询
select
a.ID,
a.SubID,
[Fee(收)] = SUM(case when b.InOrOut='收' and b.ID<=a.ID then cast(b.Fee as int) else 0 end),
[付费用合计] = SUM(case b.InOrOut when '付' then cast(b.Fee as int) else 0 end),
[差额] = SUM(case when b.InOrOut ='收' and b.ID<=a.ID then cast(b.Fee as int) when b.InOrOut='付' then -cast(b.Fee as int) else 0 end)
from
(select * from #t where InOrOut = '收') a
left join
#t b
on
a.SubID=b.SubID
group by
a.ID,a.SubID
order by
a.ID
--输出结果
ID SubID Fee(收) 付费用合计 差额
---- ------- -------- ----------- -----
01 a 100 250 -150
04 a 300 250 150
06 b 200 150 50
create table ta(ID varchar(50) primary key, SubID varchar(50),
Fee varchar(50), InOrOut varchar(50))
insert ta select '01', 'a', '100', '收'
union all select '02', 'a', '200', '付'
union all select '03', 'a', '50', '付'
union all select '04', 'a', '300', '收'
union all select '05', 'b', '150', '付'
union all select '06', 'b', '200', '收'
--查询
select t1.id, subid=max(t1.subid), fee=max(convert(int,t1.fee))
,付总数=sum(case t2.inorout when '付' then convert(int,t2.fee) else 0 end)
,差额=max(t1.fee)
-sum(case t2.inorout when '付' then convert(int, t2.fee) else 0 end)
from (select * from ta where inorout='收')t1
join (select * from ta)t2
on t1.subid=t2.subid
group by t1.id
--清除
drop table ta
我在你给的数据后又添加了一些数据 统计出来得数据毫无意思
无法看到A 和B得最终统计结果
谢谢各位大侠的指点!!!
刚刚我忘记了还有一个“币制”的字段,用来记录RMB或者USD。数据变成如下形式
ID SubID Fee InOrOut 币制
01 a 100 收 RMB
02 a 200 付 RMB
03 a 50 付 USD
04 a 300 收 USD
05 b 150 付 RMB
06 b 200 收 RMB
查询的结果为:
ID SubID Fee(收) “付”费用合计 差额 币制
01 a 100 200 -100 RMB
04 a 300 50 250 USD
06 b 200 150 50 RMB小弟尝试着修改上面给出的Sql语句来实现这个功能,但是没有成功,望大侠再指点一下,谢谢!!关注……
Fee varchar(50), InOrOut varchar(50),bz varchar(10))
insert #ta select '01', 'a', '100', '收','RMB'
union all select '02', 'a', '200', '付' ,'RMB'
union all select '03', 'a', '50', '付','USD'
union all select '04', 'a', '300', '收','USD'
union all select '05', 'b', '150', '付','RMB'
union all select '06', 'b', '200', '收','RMB'
--查询
select * from #taselect a.id,a.subid,a.fee,b.num as '“付”费用合计',a.fee - b.num as '差额',a.bz
from
(
select id,subid,cast(fee as int) as fee,inorout,bz from #ta where inorout = '收') a join
(
select subid,sum(cast(fee as int)) as num,inorout from #ta where inorout = '付'
group by subid,inorout) b
on a.subid = b.subid
--清除
drop table #ta
结果如下
01 a 100 250 -150 RMB
04 a 300 250 50 USD
06 b 200 150 50 RMB
convert(int,a.free) - convert(int,b.free) '差额',a.bz '币种'
from (select * from ta a where inorout = '付') b,
(select * from ta a where inorout = '收') a
where a.subid = b.subid and a.bz = b.bz