复合查询！不同日期的产品对应不同日期的单价计算问题。

sales表List：ID  Quantity SalesDate（出售日期）                                   
                     2   14         2016/10/1
                     2   49         2016/10/25
                     2   8         2017/2/10
                     2   3         2017/3/1
                     2   5         2017/7/26
                     2   14         2017/8/2price表List：ID Price       EffectiveDate（生效日期）
                     2  80.47      2016/10/1
                     2  81.27      2017/2/1
                     2  81.00      2017/8/1现在要把price表里的单价匹配到sales表里，求出总价。（产品种类很多，价格变化也非常频繁，这里就举了一个代码为2的例子）
注意价格生效日期和产品出售日期：
2016/10/1-2016/10/25的产品价格应为80.47；
2017/2/10-2017/7-26的产品价格应为81.27；
2017/8/2的产品价格应为81.
就是让sales表里的日期大于等于price表里日期的最大值时的价格
看到有个气量题和这个差不多，然后照着写了，但是跑出来2017/3/1-2017/7/26这段时间产品的价格是null，不知道哪里有问题
哪位大神帮忙看看!刚学sql，找不出问题。(SELECT ID,Quantity,SalesDate FROM Sales)a
(SELECT ID,Price,EffectiveDate FROM Price)bSELECT a.SKU,a.Quantity,a.SalesDate,b.Price
FROM (SELECT ID,Price,EffectiveDate FROM Price)b
RIGHT JOIN (SELECT ID,Quantity,SalesDate FROM Sales)a
ON b.ID=a.ID 
and 
b.EffectiveDate=(select max(b.EffectiveDate) from (SELECT ID,Price,EffectiveDate FROM Price)b
where b.EffectiveDate <=a.SalesDate)
ORDER BY a.SKU,a.SalesDate 

解决方案 »

  1.   


    SELECT * 
    FROM SALES A
    OUTER APPLY (SELECT TOP 1 PRICE FROM PRICE WHERE EFFECTIVEDATE<=A.SALESDATE AND ID=A.ID ORDER BY EFFECTIVEDATE DESC) AS B
      

  2.   

    DECLARE @sales TABLE(ID INT, Quantity INT, SalesDate DATE);INSERT  @sales(ID, Quantity, SalesDate)
    VALUES(2, 14, '2016/10/1'), (2, 49, '2016/10/25'), (2, 8, '2017/2/10'),
          (2, 3, '2017/3/1'), (2, 5, '2017/7/26'), (2, 14, '2017/8/2');DECLARE @price TABLE(ID INT, Price DECIMAL(10, 2), EffectiveDate DATE);INSERT  @price(ID, Price, EffectiveDate)
    VALUES(2, 80.47, '2016/10/1'), (2, 81.27, '2017/2/1'), (2, 81.00, '2017/8/1');SELECT  *,
            (SELECT     TOP(1)Price
             FROM       @price p
             WHERE      p.ID = s.ID AND s.SalesDate >= p.EffectiveDate
             ORDER BY   p.EffectiveDate DESC) AS Price
    FROM    @sales s;