请教一个存储过程

Question

有2张表，如下
ID   productID  serviceID   serviceRateID  startDate   endDate
1    00000908     1               1        2007-08-28  2008-08-27 
2    00000908     2               1        2007-08-28  2008-08-27 
3    00000808     3               2        2007-08-28  2008-08-27
4    00000808     5               2        2007-08-28  2008-08-27另一张表
ID  serviceRateID  serviceID   serviceCost
1      1               1           20 （每月）
2      1               2           30 
3      2               3           10
4      2               5           10 
5      3               6           40问题;写一个存储过程，有2个参数@serviceRateID和 @startDate,
得到如下的记录集productID  startDate    endDate   money money的解释
比如说  00000908定了serviceRateID（1）中的两个服务，时间是一年，那一年要付的money ＝ 12 *(20+30) ,请大家帮帮忙，刚接触SQL，不怎么会！ 

Answer 1

select productid , startDate,endDate,money = datediff(startDate,endDate) * sum(serviceCost)
from tb1,tb2
where tb1.serviceID = tb2.serviceID
group by productid , startDate,endDate

Answer 2

create table tb1(ID int,productID varchar(10),serviceID int,serviceRateID int,startDate datetime,endDate datetime)
insert into tb1 values(1,    '00000908',     1,               1,        '2007-08-28',  '2008-08-27')
insert into tb1 values(2,    '00000908',     2,               1,        '2007-08-28',  '2008-08-27')
insert into tb1 values(3,    '00000808',     3,               2,        '2007-08-28',  '2008-08-27')
insert into tb1 values(4,    '00000808',     5,               2,        '2007-08-28',  '2008-08-27')
create table tb2(ID int, serviceRateID int, serviceID int,  serviceCost int)
insert into tb2 values(1,      1,               1,           20)
insert into tb2 values(2,      1,               2,           30 )
insert into tb2 values(3,      2,               3,           10)
insert into tb2 values(4,      2,               5,           10)
insert into tb2 values(5,      3,               6,           40)
goselect productid , startDate,endDate,money = datediff(month,startDate,endDate) * sum(serviceCost)
from tb1,tb2
where tb1.serviceID = tb2.serviceID
group by productid , startDate,endDatedrop table tb1,tb2/*
productid  startDate                                              endDate                                                money       
---------- ------------------------------------------------------ ------------------------------------------------------ ----------- 
00000808   2007-08-28 00:00:00.000                                2008-08-27 00:00:00.000                                240
00000908   2007-08-28 00:00:00.000                                2008-08-27 00:00:00.000                                600（所影响的行数为 2 行）
*/

Answer 3

create table tb1(ID int,productID varchar(10),serviceID int,serviceRateID int,startDate datetime,endDate datetime)
insert into tb1 values(1,'00000908',1,1,'2007-08-28',  '2008-08-27')
insert into tb1 values(2,'00000908',2,1,'2007-08-28',  '2008-08-27')
insert into tb1 values(3,'00000808',3,2,'2007-08-28',  '2008-08-27')
insert into tb1 values(4,'00000808',5,2,'2007-08-28',  '2008-08-27')
create table tb2(ID int, serviceRateID int, serviceID int,  serviceCost int)
insert into tb2 values(1,      1,               1,           20)
insert into tb2 values(2,      1,               2,           30 )
insert into tb2 values(3,      2,               3,           10)
insert into tb2 values(4,      2,               5,           10)
insert into tb2 values(5,      3,               6,           40)
goselect productid , convert(varchar(10),startDate,120) startdate,convert(varchar(10),endDate,120) enddate,money = datediff(month,startDate,endDate) * sum(serviceCost)
from tb1,tb2
where tb1.serviceID = tb2.serviceID
group by productid , startdate,enddate
drop table tb1,tb2/*
productid  startdate  enddate    money       
---------- ---------- ---------- ----------- 
00000808   2007-08-28 2008-08-27 240
00000908   2007-08-28 2008-08-27 600
（所影响的行数为 2 行）
*/

Answer 4

如果serviceID的时间不一样这么写还可以吗???
就是 1,2 的时间不一样???

Answer 5

create table t1(ID int,productID varchar(20),serviceID int,serviceRateID int,startDate datetime,endDate datetime)
insert t1
select  1,'00000908',1,1,'2007-08-28','2008-08-27' 
union select 2,'00000908',2,1,'2007-08-28','2008-08-27' 
union select 3,'00000808',3,2,'2007-08-28','2008-08-27'
union select 4,'00000808',5,2,'2007-08-28','2008-08-27'create table t2(ID int,serviceRateID int,serviceID int,serviceCost int)
insert t2
select 1  ,    1     ,          1       ,    20
union select 2  ,    1    ,           2  ,         30 
union select 3  ,    2    ,           3  ,         10
union select 4  ,    2    ,           5  ,         10 
union select 5  ,    3    ,           6  ,         40go create proc p1 @serviceRateID int , @startDate datetime
as
select productid,startDate,endDate=max(endDate),money = datediff(m,startDate,max(endDate)) * sum(serviceCost) 
from t1 a inner join t2 b on a.serviceRateID=b.serviceRateID and a.serviceID=b.serviceID
where a.serviceRateID=@serviceRateID and startDate=@startDate
group by productid,startDatego --例子
exec p1 1,'2007-08-28'
drop proc p1
drop table t1,t2
/*    结果
productid            startDate                                              endDate                                                money       
-------------------- ------------------------------------------------------ ------------------------------------------------------ ----------- 
00000908             2007-08-28 00:00:00.000                                2008-08-27 00:00:00.000                                600(1 row(s) affected)
*/

Answer 6

create table t1(ID int,productID varchar(20),serviceID int,serviceRateID int,startDate datetime,endDate datetime)
insert t1
select  1,'00000908',1,1,'2007-08-28','2008-08-27' 
union select 2,'00000908',2,1,'2007-08-28','2008-08-27' 
union select 3,'00000808',3,2,'2007-08-28','2008-08-27'
union select 4,'00000808',5,2,'2007-08-28','2008-08-27'create table t2(ID int,serviceRateID int,serviceID int,serviceCost int)
insert t2
select 1  ,    1     ,          1       ,    20
union select 2  ,    1    ,           2  ,         30 
union select 3  ,    2    ,           3  ,         10
union select 4  ,    2    ,           5  ,         10 
union select 5  ,    3    ,           6  ,         40go --创建存储过程
create proc p1 @serviceRateID int , @startDate datetime
as
select productid,startDate,endDate=max(endDate),money = datediff(m,startDate,max(endDate)) * sum(serviceCost) 
from t1 a inner join t2 b on a.serviceRateID=b.serviceRateID and a.serviceID=b.serviceID
where a.serviceRateID=@serviceRateID and startDate=@startDate
group by productid,startDatego --例子
exec p1 1,'2007-08-28'
drop proc p1
drop table t1,t2
/*    结果
productid   startDate     endDate     money       
----------- ------------- ----------- ----------- 
00000908    2007-08-28    2008-08-27  600(1 row(s) affected)
*/

Answer 7

yms_wangxm() ( ) 信誉：100  2007-08-28 10:29:33  得分: 0  
 
 
   如果serviceID的时间不一样这么写还可以吗???
就是 1,2 的时间不一样???
  
 
group by productid , startdate,enddate按时间分组了.

Answer 8

弱弱的问下：为什么加endDate=max(endDate),
直接datediff(m,startDate,endDate) 可以不？

Answer 9

弱弱的问下：为什么加endDate=max(endDate),
直接datediff(m,startDate,endDate) 可以不？-----------------------------------如果endDate有多个，并且不同，取结束时间最长的,这样能包含所有的区间，如果要得到他们共同的区间，就换成min(endDate),这个根据lz的要求定

Answer 10

if exists(select name from sysobjects where name ='ServiceList'and type='U')
drop table [dbo].[ServiceList]
go
create table [dbo].[ServiceList]
(ID int,productID char(8),serviceID int,serviceRateID int,startDate datetime,endDate datetime)
insert into [dbo].[ServiceList]
select 1,'00000908',1,1,'2007-07-28','2008-08-27' 
union all select 2,'00000908',2,1,'2007-08-28','2008-08-27'
union all select 3,'00000909',3,2,'2007-07-28','2008-08-27'
union all select 4,'00000909',5,2,'2007-08-28','2009-08-27'
union all select 5,'00000808',1,1,'2007-07-28','2008-08-27'
union all select 6,'00000808',2,1,'2007-08-28','2008-08-27'
union all select 7,'00000809',3,2,'2007-07-28','2008-08-27'
union all select 8,'00000809',5,2,'2007-08-28','2009-08-27'
go
if exists(select name from sysobjects where name ='ServiceCost'and type='U')
drop table [dbo].[ServiceCost]
go
create table [dbo].[ServiceCost]
(ID int,serviceRateID int,serviceID int,serviceCost money)
insert into [dbo].[ServiceCost]
select 1,1,1,20
union all select 2,1,2,30 
union all select 3,2,3,10
union all select 4,2,5,10 
union all select 5,3,6,40 
go
if exists(select name from sysobjects where name ='sp_test'and type='P')
drop procedure [dbo].[sp_test]
go
create procedure [dbo].[sp_test]
@serviceRateID int,
@startDate datetime 
as
begin 
    select a.productID,a.startDate,max(a.endDate)as endDate,
        sum(b.serviceCost*abs(datediff(mm,a.startDate,a.endDate)))as [money]
    from  [ServiceList] a inner join 
        [ServiceCost] b on a.serviceID=b.serviceID and a.serviceRateID=b.serviceRateID
    where a.serviceRateID=@serviceRateID and a.startDate= @startDate
    group by a.productID,a.startDate 
end
go
execute [dbo].[sp_test] 1,'2007-08-28'go
/*
productID startDate               endDate                 money
--------- ----------------------- ----------------------- ---------------------
00000808  2007-08-28 00:00:00.000 2008-08-27 00:00:00.000 360.00
00000908  2007-08-28 00:00:00.000 2008-08-27 00:00:00.000 360.00
*/