请教 SQL 写法

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

已注册用户请登录

这是一个创建于 1673 天前的主题，其中的信息可能已经有所发展或是发生改变。

由于业务需要，要在报表中查询库存物料的历史价格，表结构如下：
历史库存表：
物料年份月份库存
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，支持窗口函数

物料

SQL

成本

生成

10 条回复 2021-05-27 17:41:16 +08:00

c6h6benzene

2021-05-26 18:38:35 +08:00 via iPhone

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

ooyy

2021-05-26 22:32:07 +08:00

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

setsunakute

2021-05-27 09:35:38 +08:00

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

2021-05-27 11:36:45 +08:00

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

c6h6benzene

2021-05-27 13:56:45 +08:00

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

2021-05-27 16:22:20 +08:00

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

ooyy

2021-05-27 16:26:02 +08:00

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

ooyy

2021-05-27 16:27:12 +08:00

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

setsunakute

2021-05-27 16:56:55 +08:00

@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

2021-05-27 17:41:16 +08:00

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