关于量表之间取值

zhouguoguang 2019-05-13 05:02:04
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
...全文
207 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhouguoguang 2019-05-16
  • 打赏
  • 举报
回复
不好意思,说反了 或者可能我说的不够清晰,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
zhouguoguang 2019-05-16
  • 打赏
  • 举报
回复
或者可能我说的不够清晰,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
RINK_1 2019-05-16
  • 打赏
  • 举报
回复
引用 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
RINK_1 2019-05-13
  • 打赏
  • 举报
回复


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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧