请教 SQL 写法

[ooyy]

由于业务需要，要在报表中查询库存物料的历史价格，表结构如下：
历史库存表：
物料 年份 月份 库存
A 2021 年 3 月 3
A 2021 年 2 月 5
A 2021 年 1 月 0
A 2020 年 12 月 7
A 2020 年 11 月 2
A 2020 年 10 月 1
历史成本表：
物料 年份 月份 成本
A 2021 年 1 月 3.22
A 2020 年 11 月 2.68
A 2020 年 3 月 2.55
成本表的逻辑不是每月生成一条记录，而是当有成本变动才生成一条，没有变动不生成记录。如上表，2021 年 2 、3 、4 月的成本都是 3.22 ，2020 年 12 月成本是 2.68
现在需要以左表为基础，得到物料历史月份的成本。求问大佬能否在不用存储过程的情况下用 SQL 查询实现？
数据库是 HANA （关系型数据库），类 SQL Server，支持窗口函数

[c6h6benzene]

如果有 begin month/end month 的话 join 条件可以用 between and 。没有的话只能子查询里面用 row_number 自己拼一下了？

[ooyy]

@c6h6benzene 感谢回复，子查询的方式我考虑过，只能实现特定月份的，没办法实现所有月份的，能否再详细说一下？

[setsunakute]

select *, (select cost from cost where repertory.material = cost.material and repertory.year >= cost.year and repertory.month >= cost.month limit 1) as cost from repertory;


cost 表是成本表
repertory 是库存表

[Aksura]

@ooyy 库存表是每个月都有值的，先用库存表 LEFT JOIN 成本表，连接条件物料相等，日期(年份和月份转换，按每月第一日算)取成本表小于等于库存表，SELECT 出每个物料最大日期，得库存表对应每月的成本值。

[c6h6benzene]

WITH costWithID AS (
SELECT ROW_NUMBER() OVER (PARTITION BY material ORDER BY DATEFROMPARTS(yearNo, monthNo, 1) DESC) rowNo
, material
, yearNo
, monthNo
, cost
FROM #materials
)
SELECT i.*, c.cost
FROM #inventory i
LEFT JOIN (SELECT cur.rowNo ID
, cur.material
, DATEFROMPARTS(cur.yearNo, cur.monthNo, 1) BeginYearMonth
, ISNULL(DATEADD(DAY, -1, DATEFROMPARTS(next.yearNo, next.monthNo, 1)),
'9999-12-31') EndYearMonth
, cur.cost
FROM costWithID cur
LEFT JOIN costWithID next ON cur.rowNo = next.rowNo + 1) c ON i.material = c.material
AND DATEFROMPARTS(i.yearNo, i.monthNo, 1) BETWEEN c.BeginYearMonth AND c.EndYearMonth

你说类似 SQL SERVER，我就按 T-SQL 来写了，希望排版不会乱掉。反正中心思想就是找出每个开始日期结束的对应日期，这样你可以根据范围来取值。

[ooyy]

@setsunakute 感谢回复，这个方法的子查询没有办法排序，limit 1 的方式不能保证取到最近一次的成本吧？

[ooyy]

@Aksura 感谢回复，这个方法我试了可以，用的是在左连接时候加的子查询的方式：
select * from 库存表 d
left join 成本表 b on d.编码 = b.编码 and b.年份&月份 = (select max(年份&月份) from 成本表 z where z.编码 = d.编码 and z.年份&月份 <= d.年份&月份)

[ooyy]

@c6h6benzene 感谢回复，把字段转化为日期的思路很棒，感觉效率比子查询会高一些。

[setsunakute]

@ooyy 可以排序的, select *, (select cost from cost where repertory.material = cost.material and repertory.year >= cost.year and repertory.month >= cost.month order by cost.year desc,cost.month desc limit 1) as cost from repertory; 这样取到的就是最近时间的的成本了

[ooyy]

@setsunakute 不知道其他数据库怎样，我使用 HANA 查询提示子查询不可使用 top 或者 order by 子句
“correlated subquery cannot have TOP or ORDER BY”