求一SQL

[lkskldkldsds]

create table #ta (id int,tdate smalldatetime )

insert into #ta values(1,'2012-06-05')
insert into #ta values(2,'2012-05-12')
insert into #ta values (3,'2012-06-30')

create table #tb(id int,fdate smalldatetime,priece Decimal)

insert into #tb values(1,'2012-06-03',38)
insert into #tb values(1,'2012-06-01',15)
insert into #tb values(1,'2012-03-05',25)

insert into #tb values(2,'2011-03-12',60)
insert into #tb values(2,'2011-03-19',30)
insert into #tb values(2,'2011-01-25',60)
insert into #tb values(2,'2011-02-20',60)


insert into #tb values(3,'2012-02-12',12)
insert into #tb values(3,'2012-02-20',30)
insert into #tb values(3,'2012-01-15',25)




根据#ta表取#tb表对应小于当前日期的priece平均
如果不存在取后一个月，以此类推

需要得到的結果如下

id      tdate     priece
--------------------------------------------
1    2012-06-05   26.5
2    2012-05-12   45
3    2012-06-30   21

[NET_2011]

没看懂，能表达清楚点么，比如第1条是怎样得到的

[koumingjie]

3 2012-06-30 21

这个怎么来的，lz的描述不是很清楚

[lkskldkldsds]

第一条日期为2012-06-05
对应的
（1,'2012-06-03',38)
(1,'2012-06-01',15)
两条是同一个月的 然后计算得到(38+15)/2=26.5

其它的记录如是6月不存在对应的记录就找5月的来计算，5月不存在就找4月的来算，以此类推。

[koumingjie]

[Quote=引用 3 楼 的回复:]
第一条日期为2012-06-05
对应的
（1,'2012-06-03',38)
(1,'2012-06-01',15)
两条是同一个月的 然后计算得到(38+15)/2=26.5

其它的记录如是6月不存在对应的记录就找5月的来计算，5月不存在就找4月的来算，以此类推。
[/Quote]

2012-06-30应该也是

（1,'2012-06-03',38)
(1,'2012-06-01',15)
？？？？

[NET_2011]

ID列不需要关联么?

[NET_2011]

create table #ta (id int,tdate smalldatetime )

insert into #ta values(1,'2012-06-05')
insert into #ta values(2,'2012-05-12')
insert into #ta values (3,'2012-06-30')

create table #tb(id int,fdate smalldatetime,priece Decimal)

insert into #tb values(1,'2012-06-03',38)
insert into #tb values(1,'2012-06-01',15)
insert into #tb values(1,'2012-03-05',25)

insert into #tb values(2,'2011-03-12',60)
insert into #tb values(2,'2011-03-19',30)
insert into #tb values(2,'2011-01-25',60)
insert into #tb values(2,'2011-02-20',60)


insert into #tb values(3,'2012-02-12',12)
insert into #tb values(3,'2012-02-20',30)
insert into #tb values(3,'2012-01-15',25)
GO


SELECT
	a.ID,a.tdate,cast(AVG(b.priece) AS DECIMAL(18,2))AS priece
FROM (SELECT a.ID,a.tdate,MAX(b.fdate) AS fdate FROM #tb AS b INNER JOIN #ta AS a ON a.ID=b.ID AND DATEDIFF(mm,a.tdate,b.fdate)<=0 GROUP BY a.ID,a.tdate) AS a
	LEFT JOIN #tb AS b ON a.ID=b.ID AND DATEDIFF(mm,a.fdate,b.fdate)=0
GROUP BY a.ID,a.tdate
ORDER BY ID
/*
ID	tdate	priece
1	2012-06-05 00:00:00	26.50
2	2012-05-12 00:00:00	45.00
3	2012-06-30 00:00:00	21.00
*/

[lkskldkldsds]

需要关联ID列

(3,'2012-06-30')这个记录先关联id等于3 日期小于'2012-06-30' 大于 2012-06-01但在没有
再关联id等于3 日期小于'2012-05-30' 大于 2012-05-01 也不存在
再关联id等于3 日期小于'2012-04-30' 大于 2012-04-01 也不存在
.....
再关联id等于3 日期小于'2012-02-30' 大于 2012-02-01 存在了

insert into #tb values(3,'2012-02-12',12)
insert into #tb values(3,'2012-02-20',30)

这两条记录（30+12)/2=21

[koumingjie]

create table #ta (id int,tdate smalldatetime )

insert into #ta values(1,'2012-06-05')
insert into #ta values(2,'2012-05-12')
insert into #ta values (3,'2012-06-30')

create table #tb(id int,fdate smalldatetime,priece Decimal)

insert into #tb values(1,'2012-06-03',38)
insert into #tb values(1,'2012-06-01',15)
insert into #tb values(1,'2012-03-05',25)

insert into #tb values(2,'2011-03-12',60)
insert into #tb values(2,'2011-03-19',30)
insert into #tb values(2,'2011-01-25',60)
insert into #tb values(2,'2011-02-20',60)


insert into #tb values(3,'2012-02-12',12)
insert into #tb values(3,'2012-02-20',30)
insert into #tb values(3,'2012-01-15',25)

;with cte as 
(
 --MinDate为最接近#ta的月的第一天
select *,MinDate=(select convert(varchar(8),MAX(b.fdate),120) + '01'   from #tb b where a.id=b.id and b.fdate <=a.tdate) from #ta a 
)


select *,a=(select avg(b.priece)  from #tb b where a.id=b.id and b.fdate <=a.tdate and b.fdate >=a.MinDate)  from cte a 


id          tdate                   MinDate    a
----------- ----------------------- ---------- ---------------------------------------
1           2012-06-05 00:00:00     2012-06-01 26.500000
2           2012-05-12 00:00:00     2011-03-01 45.000000
3           2012-06-30 00:00:00     2012-02-01 21.000000

(3 行受影响)

[NET_2011]

[Quote=引用 7 楼 的回复:]

需要关联ID列

(3,'2012-06-30')这个记录先关联id等于3 日期小于'2012-06-30' 大于 2012-06-01但在没有
再关联id等于3 日期小于'2012-05-30' 大于 2012-05-01 也不存在
再关联id等于3 日期小于'2012-04-30' 大于 2012-04-01 也不存在
.....
再关联id等于3 日期小于'2012-0……
[/Quote]
6楼已贴方法

[Dear SQL(燊)]

create table #ta (id int,tdate smalldatetime )

insert into #ta values(1,'2012-06-05')
insert into #ta values(2,'2012-05-12')
insert into #ta values (3,'2012-06-30')

create table #tb(id int,fdate smalldatetime,priece Decimal)

insert into #tb values(1,'2012-06-03',38)
insert into #tb values(1,'2012-06-01',15)
insert into #tb values(1,'2012-03-05',25)

insert into #tb values(2,'2011-03-12',60)
insert into #tb values(2,'2011-03-19',30)
insert into #tb values(2,'2011-01-25',60)
insert into #tb values(2,'2011-02-20',60)


insert into #tb values(3,'2012-02-12',12)
insert into #tb values(3,'2012-02-20',30)
insert into #tb values(3,'2012-01-15',25)

;
WITH LIST AS(
	SELECT RID=RANK()OVER(PARTITION BY ID ORDER BY CONVERT(NVARCHAR(6),FDATE,112) DESC)
		,*
	FROM #TB
)
SELECT TA.ID,TA.TDATE,PRICE=AVG(LT.PRIECE)
FROM #TA TA
INNER JOIN LIST LT ON TA.ID=LT.ID AND LT.RID=1
GROUP BY TA.ID,TA.TDATE
ORDER BY TA.ID