• 主页
  • 基础类
  • 应用实例
  • 新技术前沿

关于量表之间取值

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
...全文
169 点赞 收藏 4
写回复
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
回复 点赞
发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告