PostgreSQL 11
¿Cómo pasar el valor o.create_date a INNER JOIN? Necesito Max ID antes o.create_date
SELECT o.id, o.create_date date, sum(oi.quantity) qty, sum(oi.quantity * sp.price) total FROM ax_order o LEFT JOIN ax_order_invenotry oi on o.id = oi.order_id LEFT JOIN ax_inventory i on i.id = oi.inventory_id LEFT JOIN ax_suppliers s on s.id = o.supplier_id INNER JOIN ax_supplier_price sp ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id) INNER JOIN ( SELECT inventory_id, max(id) id FROM ax_supplier_price WHERE create_date <= o.create_date GROUP BY inventory_id ) lsp ON (sp.id = lsp.id) WHERE o.store_id = 13 AND o.supplier_id = 35 GROUP BY o.id, o.create_date ORDER BY o.idPuede usar el mecanismo de unión LATERAL para que funcione:
WITH ax_order AS ( SELECT * FROM (VALUES (1, '2000-1-1'::date, 1, 1)) as x(id, create_date, store_id, supplier_id) ), ax_order_inventory AS ( SELECT * FROM (VALUES (1, 2, 4)) as x(order_id, inventory_id, quantity) ), ax_supplier_price AS ( SELECT * FROM (VALUES (1, 2, 1, 10, '1999-12-31'::date)) as x(id, inventory_id, supplier_id, price, create_date) ) SELECT o.id, o.create_date date, sum(oi.quantity) qty, sum(oi.quantity * sp.price) total FROM ax_order o LEFT JOIN ax_order_inventory oi on o.id = oi.order_id INNER JOIN ax_supplier_price sp ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id) INNER JOIN LATERAL ( SELECT inventory_id, max(lsp.id) id FROM ax_supplier_price lsp WHERE sp.create_date <= o.create_date GROUP BY inventory_id ) lsp ON sp.id = lsp.id GROUP BY o.id, o.create_date ORDER BY o.id Eliminé algunos JOIN que no eran estrictamente necesarios y me burlé de sus datos tan bien como pude ver. Tenga en cuenta, sin embargo, que también podría usar una cláusula WHERE para encontrarlo, lo que debería ser más eficiente:
WITH ax_order AS ( SELECT * FROM (VALUES (1, '2000-1-1'::date, 1, 1)) as x(id, create_date, store_id, supplier_id) ), ax_order_inventory AS ( SELECT * FROM (VALUES (1, 2, 4)) as x(order_id, inventory_id, quantity) ), ax_supplier_price AS ( SELECT * FROM (VALUES (1, 2, 1, 10, '1999-12-31'::date)) as x(id, inventory_id, supplier_id, price, create_date) ) SELECT o.id, o.create_date date, sum(oi.quantity) qty, sum(oi.quantity * sp.price) total FROM ax_order o LEFT JOIN ax_order_inventory oi on o.id = oi.order_id INNER JOIN ax_supplier_price sp ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id) WHERE sp.id = ( -- NOTE: no GROUP BY necessary! SELECT max(lsp.id) id FROM ax_supplier_price lsp WHERE sp.create_date <= o.create_date AND lsp.inventory_id = sp.inventory_id ) GROUP BY o.id, o.create_date ORDER BY o.id