代码之家 › 专栏 › 技术社区 › Wei Lin

使用明细记录查询上一个unt价格

sql-server sql

Wei Lin · 技术社区 · 5 年前

样品

+---------+------------+----------+------------+
| prdt_no | order_date | quantity | unit_price |
+---------+------------+----------+------------+
| A001    | 2020-01-01 |      100 |         10 |
| A001    | 2020-01-10 |      200 |         10 |
| A001    | 2020-02-01 |      100 |         20 |
| A001    | 2020-02-05 |      100 |         20 |
| A001    | 2020-02-07 |      100 |         20 |
| A001    | 2020-02-10 |      100 |         15 |
| A002    | 2020-01-01 |      100 |         10 |
| A002    | 2020-01-10 |      200 |         10 |
| A002    | 2020-02-01 |      100 |         20 |
| A002    | 2020-02-05 |      100 |         20 |
| A002    | 2020-02-07 |      100 |         20 |
| A002    | 2020-02-10 |      100 |         15 |
+---------+------------+----------+------------+

预期

如果查询条件是 order_date between 2020-02-02 and 2020-02-10 然后数据会低于结果

+---------+------------+----------+------------+------------------------+-----------------+-------------+-----------------------------+
| prdt_no | order_date | quantity | unit_price | last_unit_price_before | unit_price_diff | cost_reduce | last_unit_price_change_date |
+---------+------------+----------+------------+------------------------+-----------------+-------------+-----------------------------+
| A001    | 2020-02-05 |      100 |         20 |                     10 |              10 |        1000 | 2020-02-01                  |
| A001    | 2020-02-07 |      100 |         20 |                     10 |              10 |        1000 | 2020-02-01                  |
| A001    | 2020-02-10 |      100 |         15 |                     20 |              -5 |        -500 | 2020-02-10                  |
| A002    | 2020-02-05 |      100 |         20 |                     10 |              10 |        1000 | 2020-02-01                  |
| A002    | 2020-02-07 |      100 |         20 |                     10 |              10 |        1000 | 2020-02-01                  |
| A002    | 2020-02-10 |      100 |         15 |                     20 |              -5 |        -500 | 2020-02-10                  |
+---------+------------+----------+------------+------------------------+-----------------+-------------+-----------------------------+

我希望得到相同的产品最后的单价,然后用它来计算差价
数据记录数实际超过200K

喜欢照片

测试演示链接

SQL Server 2012 | db<>fiddle

e、 g 2020-02-10的A001价格是15,但最后一个价格是2020-02-07的20

意思是最后一次改变价格

1 回复 | 直到 5 年前

Squirrel 5 年前

你可以用 OUTER APPLY() 以差价买到最后一个低价

SELECT *,
       unit_price_diff = T.[unit_price] - L.[last_unit_price_before]
FROM   T
       OUTER APPLY
       (
           SELECT TOP 1 
                  last_unit_price_before = x.[unit_price],
                  last_unit_price_change_date = x.[order_date]
           FROM   T x
           WHERE  x.[prdt_no] = T.[prdt_no]
           AND    x.[order_date] < T.[order_date]
           AND    x.[unit_price] <> T.[unit_price]
           ORDER BY x.[order_date] DESC
       ) L
WHERE  T.[order_date] >= '2020-02-01'
AND    T.[order_date] <= '2020-02-10'

db<>fiddle