求一个存储过程！

[清天灵月]

问题如下：
先有下面两个Select得到的集合：
select MatNo,sum(InStoTotal)总数
from InSto090210cyr
where datediff(day,InStoDate,@time1)<=0 and datediff (day,InStoDate,@time2)>=0
group by MatNo;

select MatNo,sum(OutStoTotal)总数 from OutSto090210cyr
where datediff(day,OutStoDate,@time1)<=0 and datediff (day,OutStoDate,@time2)>=0
group by MatNo;
现在要把这个集合中，MatNo如果相等，则把 总数 列的值相加，并把MatNo相同的项合并.
比如：
SELECT 1的结果：
MatNo 总数
CC-001 10
CP03 70
CP04 400
CP05 220
FP01 160
FP02 144

SELECT 2的结果：
MatNo 总数
CP02 16
CP03 24
FP01 9
FP02 24
Hs002 9
NP01 10

要得到：
MatNo 总数
-- --
MatNo冗余的项重合，并把总数相加！

不知道我把问题描述清楚了么？欢迎大侠指点，感激不尽！
最好是给一个存储过程！
create procedure XX
@time1 datetime,
@time2 datetime

这个存储过程返回一个集合！

在线等大神！

[清天灵月]

个人这样尝试，新手，这个系统在运行报错！
代码如下：
drop PROCEDURE ProStadisToTal090210cyr

create PROCEDURE ProStadisToTal090210cyr
(
@time1 datetime,
@time2 datetime
)
as
create table #temple_stadis(MatNo char (6),总数 char(10));
select MatNo,sum(InStoTotal)总数 into #temple_stadis
from InSto090210cyr
where datediff(day,InStoDate,@time1)<=0 and datediff (day,InStoDate,@time2)>=0
group by MatNo;

select MatNo,sum(OutStoTotal)总数 into #temple_stadis
from OutSto090210cyr
where datediff(day,OutStoDate,@time1)<=0 and datediff (day,OutStoDate,@time2)>=0
group by MatNo;
select MatNo,Sum(总数)总数 from #temple_stadis group by MatNo;
GO

[中国风]

create PROCEDURE ProStadisToTal090210cyr
(
 @time1 datetime,
 @time2 datetime
)
as
  create table #temple_stadis(MatNo char (6),总数 char(10));

  insert into #temple_stadis
  select MatNo,sum(InStoTotal)总数 
  from InSto090210cyr  
  where datediff(day,InStoDate,@time1)<=0 and datediff (day,InStoDate,@time2)>=0
  group by MatNo
   union all
  select MatNo,sum(OutStoTotal)总数 
  from OutSto090210cyr  
  where datediff(day,OutStoDate,@time1)<=0 and datediff (day,OutStoDate,@time2)>=0
  group by MatNo;
  select MatNo,Sum(总数)总数 from #temple_stadis group by MatNo;
  

[中国风]

你的结果集应该是 入的数-出的数?
sum(OutStoTotal)总数--这是出应该用负数

[清天灵月]

[Quote=引用 3 楼 roy_88 的回复:]

你的结果集应该是 入的数-出的数?
sum(OutStoTotal)总数--这是出应该用负数
[/Quote]

不是，这是流通量大小，出+入 ！！！我为的是，最终的排名！ 流通量大小的排名，需要两列值 MatNo 和 Total!!!谢谢你啦！一直是你帮我解决问题！ 我是新手，做起来，很蛋疼啊！

[清天灵月]

大神，崇拜你的智慧啊！！！我什么时候能有你这样的水平啊！

[dawugui]

create procedure my_proc @time1 datetime,@time2 datetime
as
begin
select MatNo , sum(总数) 总数 from 
(
  select MatNo,sum(InStoTotal)总数  
  from InSto090210cyr  
  where datediff(day,InStoDate,@time1)<=0 and datediff (day,InStoDate,@time2)>=0
  group by MatNo;
  union all 
  select MatNo,sum(OutStoTotal)总数 from OutSto090210cyr  
  where datediff(day,OutStoDate,@time1)<=0 and datediff (day,OutStoDate,@time2)>=0
  group by MatNo;
) t
group by matno
end

[dawugui]

--如果需要排名,sql 2005则如下:

create procedure my_proc @time1 datetime,@time2 datetime
as
begin
select MatNo , sum(总数) 总数 , row_number() over(order by sum(总数) desc) 排名 from 
(
  select MatNo,sum(InStoTotal)总数  
  from InSto090210cyr  
  where datediff(day,InStoDate,@time1)<=0 and datediff (day,InStoDate,@time2)>=0
  group by MatNo;
  union all 
  select MatNo,sum(OutStoTotal)总数 from OutSto090210cyr  
  where datediff(day,OutStoDate,@time1)<=0 and datediff (day,OutStoDate,@time2)>=0
  group by MatNo;
) t
group by matno
order by 排名
end

[dawugui]

--如果需要排名,sql 2000则如下:

create procedure my_proc @time1 datetime,@time2 datetime
as
begin
select m.* , (select count(1) from 
(
select MatNo , sum(总数) 总数  from 
(
  select MatNo,sum(InStoTotal)总数  
  from InSto090210cyr  
  where datediff(day,InStoDate,@time1)<=0 and datediff (day,InStoDate,@time2)>=0
  group by MatNo;
  union all 
  select MatNo,sum(OutStoTotal)总数 from OutSto090210cyr  
  where datediff(day,OutStoDate,@time1)<=0 and datediff (day,OutStoDate,@time2)>=0
  group by MatNo;
) t
group by matno
) n where n.总数 > m.总数) + 1 排名 from
(
select MatNo , sum(总数) 总数  from 
(
  select MatNo,sum(InStoTotal)总数  
  from InSto090210cyr  
  where datediff(day,InStoDate,@time1)<=0 and datediff (day,InStoDate,@time2)>=0
  group by MatNo;
  union all 
  select MatNo,sum(OutStoTotal)总数 from OutSto090210cyr  
  where datediff(day,OutStoDate,@time1)<=0 and datediff (day,OutStoDate,@time2)>=0
  group by MatNo;
) t
group by matno
) m
order by 排名
end