关于量表之间取值

[zhouguoguang]

a表，数据列表
cus, item, date，price
c001,i001,2019-01-01
c001,i001,2019-01-02
c001,i001,2019-01-03
c001,i001,2019-01-04
c001,i001,2019-01-10
c001,i001,2019-02-01
c002,i001,2019-02-02
c002,i001,2019-03-01
c002,i002,2019-03-01
c002,i002,2019-03-03

b表，为单价列表
cus, item, date, price
c001,i001,2019-01-03,100
c001,i001,2019-02-04,101
c002,i001,2019-02-10,200
c002,i001,2019-03-10,201
c002,i002,2019-03-02,202

条件
1，取对应b表中日期在a表中记录日期之后的最靠近单价
2，当b表中的日期比a表中的记录日期旧的，取最新的单价

例如
cus, item, date，price
c001,i001,2019-01-01，100
c001,i001,2019-01-02，100
c001,i001,2019-01-03，100
c001,i001,2019-01-04，101
c001,i001,2019-01-10，101
c001,i001,2019-02-01，101
c002,i001,2019-02-02，200
c002,i001,2019-03-01，201
c002,i002,2019-03-01，202
c002,i002,2019-03-03, 202

[RINK_1]

SELECT A.*,ISNULL(B.PRICE,C.PRICE) FROM TABLE_A A
OUTER APPLY
(SELECT TOP 1 PRICE FROM TABLE_B WHERE A.CUS=CUS AND A.ITEM=ITEM AND DATE>=A.DATE ORDER BY DATE) AS B
OUTER APPLY
((SELECT TOP 1 PRICE FROM TABLE_B WHERE A.CUS=CUS AND A.ITEM=ITEM AND DATE<A.DATE ORDER BY DATE DESC) AS C

[RINK_1]

引用 3 楼 zhouguoguang 的回复:

不好意思，说反了 或者可能我说的不够清晰，a表中的日期大于，b表中的日期，取b表中的price，我就以单个cus和单个item为例子 a表，数据列表 cus, item, date，price c001,i001,2019-01-01 c001,i001,2019-01-02 c001,i001,2019-01-03 c001,i001,2019-01-04 c001,i001,2019-01-10 b表，为单价列表 cus, item, date, price c001,i001,2019-01-01,100 c001,i001,2019-01-04,101 结果 cus, item, date，price c001,i001,2019-01-01 100 c001,i001,2019-01-02 100 c001,i001,2019-01-03 100 c001,i001,2019-01-04 101 c001,i001,2019-01-10 101

试试下面这个

SELECT A.*,B.PRICE FROM TABLE_A A
OUTER APPLY
(SELECT TOP 1 PRICE FROM TABLE_B WHERE A.CUS=CUS AND A.ITEM=ITEM AND DATE<=A.DATE ORDER BY DATE DESC) AS B

[zhouguoguang]

或者可能我说的不够清晰，a表中的日期大于，b表中的日期，取b表中的price，我就以单个cus和单个item为例子 a表，数据列表 cus, item, date，price c001,i001,2019-01-01 c001,i001,2019-01-02 c001,i001,2019-01-03 c001,i001,2019-01-04 c001,i001,2019-01-10 b表，为单价列表 cus, item, date, price c001,i001,2019-01-03,100 c001,i001,2019-02-04,101 结果 cus, item, date，price c001,i001,2019-01-01 100 c001,i001,2019-01-02 100 c001,i001,2019-01-03 100 c001,i001,2019-01-04 101 c001,i001,2019-01-10 101

[zhouguoguang]

不好意思，说反了 或者可能我说的不够清晰，a表中的日期大于，b表中的日期，取b表中的price，我就以单个cus和单个item为例子 a表，数据列表 cus, item, date，price c001,i001,2019-01-01 c001,i001,2019-01-02 c001,i001,2019-01-03 c001,i001,2019-01-04 c001,i001,2019-01-10 b表，为单价列表 cus, item, date, price c001,i001,2019-01-01,100 c001,i001,2019-01-04,101 结果 cus, item, date，price c001,i001,2019-01-01 100 c001,i001,2019-01-02 100 c001,i001,2019-01-03 100 c001,i001,2019-01-04 101 c001,i001,2019-01-10 101