mysql - 如何在查询中重用派生值

必须有一种更有效的方法来执行此操作，但是我对mySQL不够熟悉，还不知道要研究哪些术语。我一直在看AS关键字。

以下是我的工作查询。看看我如何必须多次导出持续时间（TIME_TO_SEC（TIMEDIFF（FinishTime，StartTime））。是否可以一次导出并在查询中重用？

table: AuraUsers
fields: ID, StartTime, FinishTime, Completed


SELECT
    TIME_TO_SEC(
        TIMEDIFF(
            FinishTime,
            StartTime
        )
    )
    AS json
    FROM AuraUsers
    WHERE (
        StartTime IS NOT NULL
        AND
        FinishTime IS NOT NULL
        AND
        Completed=7
        AND
        TIME_TO_SEC(
            TIMEDIFF(
                FinishTime,
                StartTime
            )
        ) > 0
        AND
        TIME_TO_SEC(
            TIMEDIFF(
                FinishTime,
                StartTime
            )
        ) < 90275
    )
    ORDER BY TIME_TO_SEC(
        TIMEDIFF(
            FinishTime,
            StartTime
        )
    ) DESC
    LIMIT 0,535;

最佳答案

使用HAVING子句，但实际上并没有重用，每次在查询中使用别名时，它将评估整个表达式

SELECT
    TIME_TO_SEC(
        TIMEDIFF(
            FinishTime,
            StartTime
        )
    )
    AS json
    FROM AuraUsers
    HAVING (
        StartTime IS NOT NULL
        AND
        FinishTime IS NOT NULL
        AND Completed=7
        AND json > 0
        AND json < 90275
    )
    ORDER BY json DESC
    LIMIT 0,535;