时间戳

首页 > 解决方案 > 如何避免 MySQL 中 where 子句中的语句重复？

mysql - 如何避免 MySQL 中 where 子句中的语句重复？

问题描述

有没有办法在子句中使用别名代替单个结果查询WHERE来避免以下查询中的语句重复？

SELECT * 
FROM (
    SELECT *
    FROM SomeTable T 
    WHERE created <= (CASE WHEN 
        (SELECT created 
        FROM SomeTable 
        WHERE sales > 0 
        ORDER BY created DESC 
        LIMIT 1)
    IS NULL THEN NOW() ELSE 
    (SELECT created 
        FROM SomeTable 
        WHERE sales > 0 
        ORDER BY created DESC 
        LIMIT 1)
    END)
    ORDER BY created DESC LIMIT 10
) as inverseOrder
ORDER BY created ASC;

是否可以修改该查询以避免语句重复。即类似的东西？

SELECT * 
FROM (
    SELECT *
    FROM SomeTable T 
    WHERE created <= someAliasssssssss
    IS NULL THEN NOW() ELSE someAliasssssssss
    ORDER BY created DESC LIMIT 10
) as inverseOrder
ORDER BY created ASC;

标签： mysqlwhere-clause

解决方案

您可以使用COALESCE()：

SELECT * 
FROM (
    SELECT *
    FROM SomeTable T 
    WHERE created <= COALESCE(
        (SELECT created 
        FROM SomeTable 
        WHERE sales > 0 
        ORDER BY created DESC 
        LIMIT 1), NOW())
    ORDER BY created DESC LIMIT 10
) as inverseOrder
ORDER BY created ASC;

推荐阅读