日期:2014-05-17 浏览次数:20494 次
declare @A table(ccode int,cdate datetime,cmoney int) insert into @A select 801,'2012-08-01',120 union all select 801,'2012-08-05',110 union all select 801,'2012-08-10',100 union all select 802,'2012-07-01',80 union all select 802,'2012-08-10',60 union all select 803,'2012-08-06',86 declare @B table(ccode int,selldate datetime,sellmoney int) insert into @B select 801,'2012-08-01',130 union all select 801,'2012-08-02',130 union all select 801,'2012-08-08',150 union all select 801,'2012-08-11',120 union all select 802,'2012-08-12',100 union all select 803,'2012-08-13',100 select t.*,sellmoney-cmoney as 毛利 from ( select b.*,(select top 1 cmoney from @A a where a.ccode=b.ccode and datediff(day,a.cdate,b.selldate)>=0 order by a.cdate desc) cmoney from @B b ) t /* ccode selldate sellmoney cmoney 毛利 ----------- ----------------------- ----------- ----------- ----------- 801 2012-08-01 00:00:00.000 130 120 10 801 2012-08-02 00:00:00.000 130 120 10 801 2012-08-08 00:00:00.000 150 110 40 801 2012-08-11 00:00:00.000 120 100 20 802 2012-08-12 00:00:00.000 100 60 40 803 2012-08-13 00:00:00.000 100 86 14 */
------解决方案--------------------
用一下上在老兄数据。
;WITH cte AS (
SELECT b.ccode,a.cmoney,b.sellmoney,rr=ROW_NUMBER() OVER(PARTITION BY b.rn ORDER BY ABS(datediff(dd,a.cdate,b.selldate))) FROM @a a join
(SELECT rn=ROW_NUMBER() OVER(ORDER BY GETDATE()),* FROM @b) b
ON a.ccode=b.ccode
)
SELECT *,sellmoney-cmoney FROM cte WHERE rr=1;
/*
ccode cmoney sellmoney rr
----------- ----------- ----------- -------------------- -----------
801 120 130 1 10
801 120 130 1 10
801 100 150 1 50
801 100 120 1 20
802 60 100 1 40
803 86 100 1 14
*/
------解决方案--------------------
SELECT t.*, sellmoney - cmoney AS 毛利 FROM ( SELECT b.* , ( SELECT TOP 1 cmoney FROM ta a WHERE a.ccode = b.ccode AND DATEDIFF(day, a.cdate, b.selldate) >= 0 ORDER BY a.cdate DESC ) cmoney FROM tb b ) t
------解决方案--------------------