mysql - SQL 价格调度，选择最后一个时间戳 <= UTC

问题描述

我有一张桌子叫product_pricing.

在这里，我可以为每个 ID 添加多个价格。它们的不同之处在于product_pricing. timestamp_valid...这是我用来提前安排价格变化的工具。

    SELECT 
        `products`.`wo_id`,
        `products`.`fty_id`,
        `products`.`price` AS price1,

        `product_attributes`.`fty_id`,
        `product_attributes`.`cat_id`,
        `product_attributes`.`design_id`,
        `product_attributes`.`season_id`,

        `products_u_ids`.`u_id`,
        `products_u_ids`.`link_id`,

        `product_designs`.`design_id`,
        `product_designs`.`brand_id`,

        COALESCE(`product_pricing`.`u_id`, NULL) AS price2_u_id,
        COALESCE(`product_pricing`.`currency`, NULL) AS price2_currency,
        COALESCE(`product_pricing`.`price`, NULL) AS price2,
        COALESCE(`product_pricing`.`formula_id`, NULL) price2_formula_id,
        COALESCE(`product_pricing`.`vat_calculated`) AS price2_vat_calculated,
        COALESCE(`product_pricing`.`vat_id`, NULL) AS price2_vat_id,
        COALESCE(`product_pricing`.`timestamp_valid`, NULL) price2_timestamp_valid, 

        COALESCE(`product_price_formulas`.`formula_id`, NULL) AS price2_formula_id,
        COALESCE(`product_price_formulas`.`formula`, NULL) AS price2_formula,

        COALESCE(`global_vat_tariffs`.`vat_id`, NULL) AS price2_vat_id,
        COALESCE(`global_vat_tariffs`.`percentage`, NULL) AS price2_vat_tariff

    FROM `products`

    LEFT JOIN `product_attributes`
        ON `products`.`fty_id` = `product_attributes`.`fty_id`

    LEFT JOIN `products_u_ids`
        ON `product_attributes`.`fty_id` = `products_u_ids`.`link_id`

    LEFT JOIN `product_designs` 
        ON `product_attributes`.`design_id` = `product_designs`.`design_id`

    LEFT JOIN `product_pricing`
        ON `products_u_ids`.`u_id` = `product_pricing`.`u_id`

    LEFT JOIN `product_price_formulas` 
        ON `product_pricing`.`formula_id` = `product_price_formulas`.`formula_id`

    LEFT JOIN `global_vat_tariffs` 
        ON `product_pricing`.`vat_id` = `global_vat_tariffs`.`vat_id`

         LEFT OUTER JOIN (
            SELECT `product_pricing`.`u_id`, MAX(`timestamp_valid`) AS MaxDate
            FROM `product_pricing`
            WHERE `product_pricing`.`timestamp_valid` <= UTC_TIMESTAMP
            GROUP BY `product_pricing`.`u_id`
        ) AS temp ON temp.u_id = `product_pricing`.`u_id` AND temp.MaxDate = `product_pricing`.`timestamp_valid`

    WHERE `products`.`wo_id` IN ('028284', '018305', '031536')

我上面的 SQL 返回给定 ID 的所有行，而不是只返回product_pricing. timestamp_valid``<= UTC_TIMESTAMP().

中存在问题LEFT OUTER JOIN。

此 SQL 所做的另一件事是，当没有为设置任何内容时price2，它不应该破坏脚本，而只是为 . 返回 NULL price2。我正在通过使用来解决这个问题COALESCE。

这就是我得到的：

这是我应该得到的：

假设查询日期为 2018-5-7。

任何想法如何解决这个问题LEFT OUTER JOIN？

标签： mysqlsql

mysql - SQL 价格调度，选择最后一个时间戳 <= UTC_TIMESTAMP()

问题描述

解决方案

推荐阅读