一SQL语句

[foxe]

表1：
=======================================
P M
a a1
a a2
a a3
b b1
b b2
c c1
c c2
c c3
c c4
========================================

表2:
========================================
D P M I O
2001-1-1 a a1 1 0
2001-1-1 b b1 1 1
2001-1-2 a a1 2 0
2001-1-2 a a2 3 1
2001-1-2 a a3 5 5
2001-1-4 c c1 2 0
2001-1-5 c c1 3 3
2001-1-5 c c2 1 0
2001-1-7 b b2 5 4

结果集（要求）：
=======================================
D P M I O B
2001-1-1 a a1 1 0 1
2001-1-1 a a2 0 0 0
2001-1-1 a a3 0 0 0
2001-1-1 b b1 1 1 0
2001-1-1 b b2 0 0 0
2001-1-1 c c1 0 0 0
2001-1-1 c c2 0 0 0
2001-1-1 c c3 0 0 0
2001-1-1 c c4 0 0 0
2001-1-2 a a1 2 0 3
2001-1-2 a a2 3 1 2
2001-1-2 a a3 5 5 0
2001-1-2 b b1 1 1 0
2001-1-2 b b2 0 0 0
2001-1-2 c c1 0 0 0
2001-1-2 c c2 0 0 0
2001-1-2 c c3 0 0 0
2001-1-2 c c4 0 0 0
2001-1-3 a a1 0 0 3
2001-1-3 a a2 0 0 2
2001-1-3 a a3 0 0 0
2001-1-3 b b1 0 0 0
2001-1-3 b b2 0 0 0
2001-1-3 c c1 0 0 0
2001-1-3 c c2 0 0 0
2001-1-3 c c3 0 0 0
2001-1-3 c c4 0 0 0
....
公式：B(n) = I(n)-O(n)+B(n-1)

[foxe]

谢谢各位指教，刚忙了一天，才能上线看结果，试完后结帖！

[WangZWang]

不是很明白你的结果集, 是交叉联接,也不是?
两表关联的字段是那个?

[了缘]

select D,P,M,I , O, B from 表2 left join 表1 on 2.p=1.p and 2.m=1.m
然后用游标改吧

[Hahahahahaha]

提示：
1、cross join产生所有（D，P，M ）组合
2、函数实现B(n) = I(n)-O(n)+B(n-1)


本来想说错了不管，不过只是提示，没可能错，就不说了，楼主努力

哈,哈哈,哈哈哈......

[子陌红尘]

--创建日期字典临时表#D:
create table #D (
D datetime);


--向日期字典临时表插入数据：
insert into #D value(cast('2004-01-01' as datetime));
insert into #D value(cast('2004-01-02' as datetime));
insert into #D value(cast('2004-01-03' as datetime));
insert into #D value(cast('2004-01-04' as datetime));
...


--创建汇总数据临时表#E：
create table #E (
D datetime,
P int(4),
M int(4),
B int(4), );


--生成并向临时表插入汇总数据：
insert into #E
select c.D ,
c.P ,
c.M ,
sum(c.I - c.O) as B
from (select a.D ,
a.P ,
a.M ,
b.I ,
b.O
from
表2 a , 表2 b
where
a.D >= b.D
and
a.P = b.P
and
a.M = b.M ) as c
group by c.D , c.P , c.M


--查询数据，输出的结果自己看
select e.D, e.P ,e.M ,isnull(f.I,0) as I,isnull(f.O,0) as O ,e.B
from (select a.D,
b.P ,
b.M ,
max(b.B) as B
from (select b.D ,a.P ,a.M from 表1 a cross join #D b) c ,#E d
where c.D >= d.D and c.M = d.M and c.P = d.P
group by a.D, b.P ,b.M) e
left join
表2 f
on e.D = f.D and e.P = f.P and e.M = f.M


--删除临时表：
drop table #D;
drop table #E;

[pbsql]

declare @t1 table(p varchar(10),m varchar(10))
insert into @t1
select 'a', 'a1' union all
select 'a', 'a2' union all
select 'a', 'a3' union all
select 'b', 'b1' union all
select 'b', 'b2' union all
select 'c', 'c1' union all
select 'c', 'c2' union all
select 'c', 'c3' union all
select 'c', 'c4'

declare @t2 table(d datetime,p varchar(10),m varchar(10),i int,o int)
insert into @t2
select '2001-1-1', 'a', 'a1', 1, 0 union all
select '2001-1-1', 'b', 'b1', 1, 1 union all
select '2001-1-2', 'a', 'a1', 2, 0 union all
select '2001-1-2', 'a', 'a2', 3, 1 union all
select '2001-1-2', 'a', 'a3', 5, 5 union all
select '2001-1-4', 'c', 'c1', 2, 0 union all
select '2001-1-5', 'c', 'c1', 3, 3 union all
select '2001-1-5', 'c', 'c2', 1, 0 union all
select '2001-1-7', 'b', 'b2', 5, 4

declare @mind datetime,@maxd datetime,@sql varchar(8000)
declare @p varchar(10),@m varchar(10),@b int
select @mind=min(d),@maxd=max(d) from @t2
set @sql='select top '+cast(datediff(day,@mind,@maxd)+1 as varchar)+' identity(int,0,1) id into ##dt from syscolumns a,syscolumns b'
exec(@sql)--生成所有日期列表
select t.d,t.p,t.m,sum(isnull(t2.i,0)) i,sum(isnull(t2.o,0)) o,0 b into #tem
from (select dateadd(day,id,@mind) d,t1.* from ##dt,@t1 t1) t left join @t2 t2
on t.d=t2.d and t.p=t2.p and t.m=t2.m
group by t.d,t.p,t.m order by t.p,t.m,t.d--所有日期的所有数据
update #tem set @b=case when p=@p and m=@m then @b+i-o else i-o end,b=@b,@p=p,@m=m
select * from #tem order by d,p,m
drop table ##dt,#tem

[foxe]

to libin_ftsafe(子陌红尘)
你的最后一测过不了，也不太看的懂，所以没有全部测完，请帮忙改一改，最后一部是最关键的，因为前的数据我均已实现了！

[foxe]

To pbsql(风云)
你的方法我可能不能用，你的数据创建要耗掉我N多时间，因为我的库里有好多数据，你的UNION建法，是不是要我全部手工重构一张表啊！或许我没看明白！