10
请教 SQL 写法
source link: https://www.v2ex.com/t/779389
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
由于业务需要，要在报表中查询库存物料的历史价格，表结构如下：
历史库存表：
物料 年份 月份 库存
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，支持窗口函数
历史库存表：
物料 年份 月份 库存
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，支持窗口函数
10 条回复 • 2021-05-27 17:41:16 +08:00
c6h6benzene 1 天前 via iPhone
如果有 begin month/end month 的话 join 条件可以用 between and 。没有的话只能子查询里面用 row_number 自己拼一下了？
setsunakute 17 小时 28 分钟前
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 是库存表
cost 表是成本表
repertory 是库存表
Aksura 15 小时 27 分钟前
@ooyy 库存表是每个月都有值的，先用库存表 LEFT JOIN 成本表，连接条件物料相等，日期(年份和月份转换，按每月第一日算)取成本表小于等于库存表，SELECT 出每个物料最大日期，得库存表对应每月的成本值。
c6h6benzene 13 小时 7 分钟前
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 来写了，希望排版不会乱掉。反正中心思想就是找出每个开始日期结束的对应日期，这样你可以根据范围来取值。
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 10 小时 38 分钟前
@Aksura 感谢回复，这个方法我试了可以，用的是在左连接时候加的子查询的方式：
select * from 库存表 d
left join 成本表 b on d.编码 = b.编码 and b.年份&月份 = (select max(年份&月份) from 成本表 z where z.编码 = d.编码 and z.年份&月份 <= d.年份&月份)
select * from 库存表 d
left join 成本表 b on d.编码 = b.编码 and b.年份&月份 = (select max(年份&月份) from 成本表 z where z.编码 = d.编码 and z.年份&月份 <= d.年份&月份)
setsunakute 10 小时 7 分钟前
@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 9 小时 23 分钟前
@setsunakute 不知道其他数据库怎样，我使用 HANA 查询提示子查询不可使用 top 或者 order by 子句
“correlated subquery cannot have TOP or ORDER BY”
“correlated subquery cannot have TOP or ORDER BY”
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK