带范围条件的SQL JOIN匹配对应价格的跨库兼容实现方法

现有3张业务表，结构如下：

• order订单表，字段包括：id、date（下单日期）、supplier_id（供应商ID）
• order_lineitem订单明细表，字段包括：id、order_id（关联订单ID）、article_id（商品ID）、order_quantity（下单数量）、order_price（订单实际成交价）
• prices商品价格表，字段包括：id、article_id（商品ID）、supplier_id（供应商ID）、valid_until（价格有效期至）、minimum_order_quantity（价格适用最小起订量）、list_price（目录标价）

需要对比订单实际成交价order_price与对应生效的目录价list_price，由于价格表不一定存在匹配的有效记录，关联时需使用左外连接（LEFT OUTER JOIN），最终需返回全量订单及对应匹配的目录价，供下游数据处理使用，不可拆分查询任务。

针对每一条订单明细，匹配的价格需同时满足：

• 商品ID与订单明细一致
• 供应商ID与订单一致
• 下单日期在价格有效期内：valid_until为NULL时视为永久有效（取值为'2099-12-31'），优先选取大于下单日期的最小valid_until对应的价格
• 价格适用最小起订量小于等于订单下单数量，优先选取其中最大minimum_order_quantity对应的价格

原有尝试通过JOIN关联后加ORDER BY排序、再GROUP BY取单条价格记录的方案存在明显缺陷：

• 根据MySQL官方文档，GROUP BY未覆盖所有非聚合列时返回的记录是非确定性的，结果不可靠
• 该写法不符合SQL标准，无法在SQL Server、MariaDB、Google BigQuery等其他SQL引擎中运行

同时由于不存在采购量越大价格越低的固定规律，无法直接通过取min(list_price)得到正确结果。需求不接受通过嵌套子查询先排序再分组的非标准方案，需要符合SQL标准、可跨主流SQL引擎运行的确定性查询方案。问题附带完整测试表结构、样例数据可用于方案验证。

内容的提问来源于stack exchange，提问作者Fabian K