php - 如何使用嵌套别名让查询更简单？

Question

假设我有这个查询:`

SELECT
    IF(uPer="Yes", "Y", "N") AS Manage,
    IF(date_check="Yes", "Y", "N") As dCheck,
    IF(dCheck="Yes", "Great", "Not Great") AS firstNested,
    IF(firstNested="Great",1,0) AS secondNested
FROM table

因此，在这个示例中，我有超过 1 个别名，并且我想在其他别名中使用其中一些，就像嵌套别名一样。

如何在不将别名复制/粘贴到另一个别名中的情况下实现这一点，例如:

IF(
   IF(dCheck="Yes", "Great", "Not Great") AS firstNested) ="Great",1,0
) AS secondNested

注意确定上面的语法是否正确。

Answer 1

试试这个:

SELECT
    Manage,
    dCheck,
    firstNested,
    IF(firstNested="Great",1,0) AS secondNested
FROM
(
    SELECT
        Manage,
        dCheck,
        IF(dCheck="Yes", "Great", "Not Great") AS firstNested
    FROM
    (
        SELECT
            IF(uPer="Yes", "Y", "N") AS Manage,
            IF(date_check="Yes", "Y", "N") As dCheck
            FROM table
    ) as t1
) as t2

错误:dCheck 结果将为 Y 或 N，并且您在下一个测试中检查 Yes结果为 false，并且所有行的 firstNested 都将 Not Great。

您的查询应该是:

SELECT
    Manage,
    dCheck,
    firstNested,
    IF(firstNested="Great",1,0) AS secondNested
FROM
(
    SELECT
        Manage,
        dCheck,
        IF(dCheck="Y", "Great", "Not Great") AS firstNested
    FROM
    (
        SELECT
            IF(uPer="Yes", "Y", "N") AS Manage,
            IF(date_check="Yes", "Y", "N") As dCheck
            FROM table
    ) as t1
) as t2