2张表之间是时间相联系，但是时间是不完全相等,ID均为自增列
A表 B表
ID DATE SUP NW ID DATE WEIGHT
1 10-21 A 100 1 10-22 90
2 10-29 B 100 2 10-27 5
3 11-01 A 20 3 10-30 50
4 11-02 10
想要的表是
MONTH SUP NW W
10 A 100 95
10 B 100 50
11 A 20 10
可以把A表理解为进货表，B表理解为消耗表，B表日期消耗的重量都是A表中最近一次进货中的部分重量,我是sql2008，能不能在不该动数据库的情况下写出查询语句
------解决思路----------------------
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY DATE)RN1
,ROW_NUMBER()OVER(PARTITION BY SUP ORDER BY DATE)RN2
,*
FROM
A
)
,CTE2 AS(
SELECT ROW_NUMBER()OVER(ORDER BY MIN(DATE))RN,MONTH(DATE)[MONTH],MIN(DATE)DATE,SUP,SUM(NW)NW FROM CTE GROUP BY MONTH(DATE),SUP,RN1-RN2
)
SELECT T1.[MONTH],T1.SUP,T1.NW,SUM(T3.WEIGHT)[W]
FROM CTE2 T1
LEFT JOIN CTE2 T2 ON T1.RN+1=T2.RN
LEFT JOIN B T3 ON T3.DATE BETWEEN T1.DATE AND ISNULL(T2.DATE,'9999-12-31')
GROUP BY T1.[MONTH],T1.SUP,T1.NW
你试下