基于列值及价格规则获取产品最低价格行的技术问询

我来帮你构建符合需求的SQL查询！先把需求里的规则拆解成可执行的条件，再一步步实现。

核心规则转化为SQL条件 #

要让special_price被纳入价格评估，必须同时满足以下所有条件：

• special_price不能为NULL（对应规则2）
• special_from_date不为NULL且早于当前日期（对应规则4）
• 特价未过期：要么special_to_date为NULL（无结束日期），要么当前日期在special_to_date之前/当天（对应规则3）

用SQL表达就是：

special_price IS NOT NULL 
AND special_from_date IS NOT NULL 
AND special_from_date < CURRENT_DATE() 
AND (special_to_date IS NULL OR special_to_date >= CURRENT_DATE())

计算每个产品的有效最低价格 #

接下来，我们为每个产品计算当前有效最低价格：如果符合条件的特价存在，就取常规价和特价中的较小值；否则直接用常规价（对应规则1的优先评估逻辑）。

完整查询方案 #

这里提供两种常用方案，你可以根据数据库类型和需求选择：

方案1：CTE+子查询（适配大多数数据库）

WITH product_effective_prices AS (
    SELECT 
        *, -- 可替换为你需要的具体字段，比如product_id, name, price等
        CASE 
            WHEN special_price IS NOT NULL 
                 AND special_from_date IS NOT NULL 
                 AND special_from_date < CURRENT_DATE() 
                 AND (special_to_date IS NULL OR special_to_date >= CURRENT_DATE())
            THEN LEAST(price, special_price)
            ELSE price
        END AS effective_price
    FROM your_product_table -- 替换成你的实际表名
)
SELECT *
FROM product_effective_prices
WHERE effective_price = (SELECT MIN(effective_price) FROM product_effective_prices);

方案2：窗口函数（支持返回并列最低价格的产品）

如果有多个产品的最低价格相同，这个方案会返回所有并列的记录：

WITH product_effective_prices AS (
    SELECT 
        *,
        CASE 
            WHEN special_price IS NOT NULL 
                 AND special_from_date IS NOT NULL 
                 AND special_from_date < CURRENT_DATE() 
                 AND (special_to_date IS NULL OR special_to_date >= CURRENT_DATE())
            THEN LEAST(price, special_price)
            ELSE price
        END AS effective_price,
        RANK() OVER (ORDER BY 
            CASE 
                WHEN special_price IS NOT NULL 
                     AND special_from_date IS NOT NULL 
                     AND special_from_date < CURRENT_DATE() 
                     AND (special_to_date IS NULL OR special_to_date >= CURRENT_DATE())
                THEN LEAST(price, special_price)
                ELSE price
            END ASC) AS price_rank
    FROM your_product_table
)
SELECT *
FROM product_effective_prices
WHERE price_rank = 1;

注意事项 #

• 不同数据库的当前日期函数略有差异：
  • MySQL/MariaDB：用CURDATE()替代CURRENT_DATE()
  • SQL Server：用GETDATE()或者CURRENT_DATE
  • Oracle：用SYSDATE
• 若不需要返回所有字段，把*替换成具体字段（比如product_id, name, effective_price）会更高效。

内容的提问来源于stack exchange，提问作者Jaimin Sutariya