--测试--测试数据
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-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-12'
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
13 2004-05-12 50 0 50(所影响的行数为 3 行)
--*/
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-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-12'
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
13 2004-05-12 50 0 50(所影响的行数为 3 行)
--*/
最初的问题在
http://community.csdn.net/Expert/topic/3129/3129439.xml?temp=.4323236当表中只有一个月的数据时(如5月份的),我上面的sql语句查2004-05-01至2004-05-31段的数据时正确,但当表中有5、6两个月份的数据时我再查询2004-05-01至2004-05-31段的数据就不对了。
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 ,13union all select 1,'2004-06-11', 50 ,11
union all select 1,'2004-06-20',-30 ,11
union all select 2,'2004-06-12', 50 ,11
union all select 2,'2004-06-25',-100 ,11
union all select 3,'2004-06-12', 100 ,13
union all select 3,'2004-06-13',-50 ,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 ,13union all select 1,'2004-06-11', 130 ,11
union all select 1,'2004-06-20', 100 ,11
union all select 2,'2004-06-12', 200 ,11
union all select 2,'2004-06-25', 100 ,11
union all select 3,'2004-06-12', 140 ,13
union all select 3,'2004-06-13', 90 ,13
go--查询
日期范围:'2004-05-10' and '2004-05-20'*--结果
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
--查询
日期范围:'2004-05-12' and '2004-05-20'*--结果
ctc rq 进 出 余数
----------- ---------- ----------- ----------- -----------
11 2004-05-12 50 0 250
11 2004-05-15 0 20 330
13 2004-05-12 50 0 50
13 2004-05-20 0 10 40第二次查询结果2004-05-12的余数少了100(就是id=1的2004-05-10的100没统计上)
declare @dt1 datetime,@dt2 datetime
select @dt1='2004-05-12',@dt2='2004-05-20'--查询语句
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 <=v1.rq
and exists(
select 1 from v
where ctc=v1.ctc and id=b.id
and rq <=v1.rq)
group by id
)a on a.id=b.id and a.rq=b.rq)
from v v1
where rq between @dt1 and @dt2
group by ctc,rq
order by ctc,rq