try:select userId , a.sum * b.price as sum
from 用户用气记录表 a
left outer join 用户价格变化表 b
on a.userid=b.userid and b.inputDay=(select max(inputDay) from 用户价格变化表 where inputDay<a.inputDay)
where a.inputDay between '开始日期' and '结束日期'
group by UserID
from 用户用气记录表 a
left outer join 用户价格变化表 b
on a.userid=b.userid and b.inputDay=(select max(inputDay) from 用户价格变化表 where inputDay<a.inputDay)
where a.inputDay between '开始日期' and '结束日期'
group by UserID
create table riceList(ID int,UserID int,Price numeric(12,2),InputDay datetime)go
insert into List values(1,1,100,'2006-01-01')
insert into List values(1,1,90,'2006-02-01')
insert into List values(1,1,80,'2006-03-01')
insert into List values(1,1,70,'2006-04-01')
insert into riceList values(1,1,1,'2005-12-01')
insert into riceList values(1,1,2,'2006-02-10')select a.* ,
(select top 1 Price
from riceList
where datediff(d,InputDay,a.InputDay)>= 0 and UserID = a.UserID order by InputDay desc
) * Sum as 金额
from list a
/*ID UserID Sum InputDay 金额
----------- ----------- ----------- ------------------------------- -------------------------
1 1 100 2006-01-01 00:00:00.000 100.00
1 1 90 2006-02-01 00:00:00.000 90.00
1 1 80 2006-03-01 00:00:00.000 160.00
1 1 70 2006-04-01 00:00:00.000 140.00*/
drop table List,riceList
create table tab_user(ID int , UserID int , Sum int , InputDay DateTime)
insert into tab_user
select 1,1,2,'2005-01-05'
union select 2,1,3,'2005-05-06'
union select 3,1,4,'2005-06-06'
union select 4,2,1,'2005-07-06'
union select 5,2,3,'2005-05-06'
union select 6,2,2,'2005-08-06'
union select 7,2,1,'2005-09-06'create table tab_price(ID int , UserID int , Price int , InputDay DateTime)
insert into tab_price
select 1,1,1,'2005-01-01'
union select 2,1,2,'2005-05-07'
union select 3,1,3,'2005-07-07'
union select 4,2,1,'2005-04-06'
union select 6,2,3,'2005-08-01'
union select 7,2,1,'2005-09-01'select a.userId , sum(a.sum * b.price) as sum
from tab_user a
left outer join tab_price b
on b.inputDay=(select max(inputDay) from tab_price where inputDay<a.inputDay and userid=a.userid)
where a.inputDay between '2005-05-01' and '2005-09-07'
group by a.UserID结果:
/* userid sum
---------------------
1 11 (3*1+2*4)
2 11 (1*1+3*1+2*3+1*1)
*/--删除测试数据
drop table tab_user
drop table tab_price
union select 2,1,3,'2005-05-06'
union select 3,1,4,'2005-06-06'
create table tab_price(ID int , UserID int , Price int , InputDay DateTime)
insert into tab_price
select 1,1,1,'2005-01-01'
union select 2,1,2,'2005-05-07'
union select 3,1,3,'2005-07-07'
----------------
1 11 ?= (2*1 + 3*1 +2*4
insert into tab_user
select 1,1,2,'2005-01-05' 1
union select 2,1,3,'2005-05-06'1
union select 3,1,4,'2005-06-06'2
union select 4,2,1,'2005-07-06'
union select 5,2,3,'2005-05-06'
union select 6,2,2,'2005-08-06'
union select 7,2,1,'2005-09-06'create table tab_price(ID int , UserID int , Price int , InputDay DateTime)
insert into tab_price
select 1,1,1,'2005-01-01'
union select 2,1,2,'2005-05-07'
union select 3,1,3,'2005-07-07'
union select 4,2,1,'2005-04-06'
union select 6,2,3,'2005-08-01'
union select 7,2,1,'2005-09-01'select U.UserID,U.InputDay,U.[Sum]*(select top 1 Price from tab_price where UserID=U.UserID and InputDay<=U.InputDay order by InputDay DESC) as 价钱
from tab_user U