select a.etc,a.rq,a.jin,a.c,b.ys from (
select etc,rq,jin=sum(case when 进出数>0 then 进出数 else 0 end),
c=sum(case when 进出数<0 then 进出数 else 0 end) from a group by etc,rq) a join (select etc,rq,sum(余数) as ys from b group by etc,rq) b
on a.etc=b.etc and a.rq=b.rq
select etc,rq,jin=sum(case when 进出数>0 then 进出数 else 0 end),
c=sum(case when 进出数<0 then 进出数 else 0 end) from a group by etc,rq) a join (select etc,rq,sum(余数) as ys from b group by etc,rq) b
on a.etc=b.etc and a.rq=b.rq
from(
select ctc,rq=convert(char(10),rq,120)
,进=sum(case when 进出数>0 then 进出数 else 0 end)
,出=-sum(case when 进出数<0 then 进出数 else 0 end)
from v
where rq between '2004-05-10' and '2004-05-20'
group by ctc,convert(char(10),rq,120)
)a order by ctc,rq
create table v(id int,rq datetime,进出数 int,ctc int)
insert v select 1,'2004-05-10', 100,11
union all select 1,'2004-05-15',-20 ,11
union all select 2,'2004-05-10', 200,11
union all select 2,'2004-05-12', 50, 11
union all select 2,'2004-06-02',-100,11
union all select 3,'2004-05-12', 50, 13
union all select 3,'2004-05-20',-10, 13create table b(id int,rq datetime,余数 int,ctc int)
insert b select 1,'2004-05-10',100,11
union all select 1,'2004-05-15',80 ,11
union all select 2,'2004-05-10',200,11
union all select 2,'2004-05-12',250,11
union all select 2,'2004-06-02',150,11
union all select 3,'2004-05-12',50, 13
union all select 3,'2004-05-20',40, 13
go--查询
select *,余数=(select sum(进出数) from v where ctc=a.ctc and rq<=a.rq)
from(
select ctc,rq=convert(char(10),rq,120)
,进=sum(case when 进出数>0 then 进出数 else 0 end)
,出=-sum(case when 进出数<0 then 进出数 else 0 end)
from v
where rq between '2004-05-10' and '2004-05-20'
group by ctc,convert(char(10),rq,120)
)a order by ctc,rq
go--删除测试
drop table v,b/*--测试结果ctc rq 进 出 余数
----------- ---------- ----------- ----------- -----------
11 2004-05-10 300 0 300
11 2004-05-12 50 0 350
11 2004-05-15 0 20 330
13 2004-05-12 50 0 50
13 2004-05-20 0 10 40(所影响的行数为 5 行)
--*/
,(select sum(进出数) from v where 进出数 > 0 and rq = a.rq and ctc = a.ctc)
,(select sum(进出数) from v where 进出数 < 0 and rq = a.rq and ctc = a.ctc)
,(select sum(进出数) from v where ctc=a.ctc and rq<=a.rq)
from V a
where rq >= @rq1 and rq <= @rq2
group by ctc,rq
想要的余数,举个例子:比如2004-05-12日ctc为11的余数就是其下id为1和2所对应b表最大日期的余数之和。1的余数为b表中<=2004-05-12的最大日期2004-05-10的余数100,2的余数是
2004-05-12日的250,两者之和为350
这样说明白不?
我这样说清楚没?
我测试了,不是我想要的结果,看来我没说明白。
,进=sum(case when 进出数>0 then 进出数 else 0 end)
,出=-sum(case when 进出数<0 then 进出数 else 0 end)
,余数=(
select sum(余数)
from b join(
select id,rq=max(rq) from b
where ctc=v1.ctc and rq between '2004-04-10' and v1.rq
and exists(
select 1 from v
where ctc=v1.ctc and id=b.id
and rq between '2004-04-10' and v1.rq)
group by id
)a on a.id=b.id and a.rq=b.rq)
from v v1
where rq between '2004-05-10' and '2004-05-20'
group by ctc,rq
order by ctc,rq
create table v(id int,rq datetime,进出数 int,ctc int)
insert v select 1,'2004-05-10', 100,11
union all select 1,'2004-05-15',-20 ,11
union all select 2,'2004-05-10', 200,11
union all select 2,'2004-05-12', 50, 11
union all select 2,'2004-06-02',-100,11
union all select 3,'2004-05-12', 50, 13
union all select 3,'2004-05-20',-10, 13create table b(id int,rq datetime,余数 int,ctc int)
insert b select 1,'2004-05-10',100,11
union all select 1,'2004-05-15',80 ,11
union all select 2,'2004-05-10',200,11
union all select 2,'2004-05-12',250,11
union all select 2,'2004-06-02',150,11
union all select 3,'2004-05-12',50, 13
union all select 3,'2004-05-20',40, 13
go--查询
select ctc,rq=convert(char(10),rq,120)
,进=sum(case when 进出数>0 then 进出数 else 0 end)
,出=-sum(case when 进出数<0 then 进出数 else 0 end)
,余数=(
select sum(余数)
from b join(
select id,rq=max(rq) from b
where ctc=v1.ctc and rq between '2004-04-10' and v1.rq
and exists(
select 1 from v
where ctc=v1.ctc and id=b.id
and rq between '2004-04-10' and v1.rq)
group by id
)a on a.id=b.id and a.rq=b.rq)
from v v1
where rq between '2004-05-10' and '2004-05-20'
group by ctc,rq
order by ctc,rq
go--删除测试
drop table v,b/*--测试结果ctc rq 进 出 余数
----------- ---------- ----------- ----------- -----------
11 2004-05-10 300 0 300
11 2004-05-12 50 0 350
11 2004-05-15 0 20 330
13 2004-05-12 50 0 50
13 2004-05-20 0 10 40(所影响的行数为 5 行)
to zjcxc(邹建):
想要的余数,举个例子:比如2004-05-12日ctc为11的余数就是
其下id为1和2所对应b表最大日期的余数之和。
1的余数为b表中<=2004-05-12的最大日期2004-05-10的余数100,
2的余数是
2004-05-12日的250,两者之和为350
这样说明白不?--*/
,进=sum(case when 进出数>0 then 进出数 else 0 end)
,出=-sum(case when 进出数<0 then 进出数 else 0 end)
,余数=(
select sum(余数)
from b join(
select id,rq=max(rq) from b
where ctc=v1.ctc and rq between '2004-05-10' and v1.rq
and exists(
select 1 from v
where ctc=v1.ctc and id=b.id
and rq between '2004-05-10' and v1.rq)
group by id
)a on a.id=b.id and a.rq=b.rq)
from v v1
where rq between '2004-05-10' and '2004-05-20'
group by ctc,rq
order by ctc,rq
余數=(select sum(b.余數) from b inner join (select id,max(rq) rq,max(ctc) ctc from b where ctc=a.ctc and rq<=a.rq group by id) bb on b.id=bb.id and b.rq=bb.rq and b.ctc=bb.ctc )
from v as a where rq>='2004/5/10' and rq<='2004/5/20' group by a.ctc,a.rq order by 1