mysql - mysql 5.5中'having clause'中的未知列

我尝试运行一个查询，其中包含：

        SELECT
            `doctors`.*,
            (
                SELECT GROUP_CONCAT(`areas`.`areaName` SEPARATOR ', ')
                FROM `areas_has_doctors`
                INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
                WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
            ) as `areas`,
            (
                SELECT GROUP_CONCAT(`areas`.`areaId`)
                FROM `areas_has_doctors`
                INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
                WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
            ) as `areasIdies`
        FROM
            `cats_has_doctors`
            INNER JOIN `doctors` ON `doctors`.`doctorId` = `cats_has_doctors`.`doctorId`
        WHERE
            `cats_has_doctors`.`catId` = '1' && `doctors`.`disable` = 0
        GROUP BY
            `cats_has_doctors`.`relationId`
         HAVING FIND_IN_SET('1,2,3,4', `areasIdies`)

服务器的工作状态
但在服务器中：

5.5.61-cll - MySQL Community Server (GPL)

我搞错了：

Unknown column 'areasIdies' in 'having clause'

我能做什么？

最佳答案

也许它认为面积是一个变量？
我会尝试将areasidies子查询拉到一个临时表中并尝试这样做。
只需将查询保持为要测试的状态，但只需在顶部执行此操作并更改find in set param。

CREATE TEMPORARY TABLE IF NOT EXISTS areasIdiesTemp AS (
    SELECT GROUP_CONCAT(`areas`.`areaId`)
    FROM `areas_has_doctors`
    INNER JOIN `areas`
    ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
    WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
);

SELECT
    `doctors`.*,
    (
        SELECT GROUP_CONCAT(`areas`.`areaName` SEPARATOR ', ')
        FROM `areas_has_doctors`
        INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
        WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
    ) as `areas`,
    (
        SELECT GROUP_CONCAT(`areas`.`areaId`)
        FROM `areas_has_doctors`
        INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
        WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
    ) as `areasIdies`
FROM
    `cats_has_doctors`
    INNER JOIN `doctors` ON `doctors`.`doctorId` = `cats_has_doctors`.`doctorId`
WHERE
    `cats_has_doctors`.`catId` = '1' && `doctors`.`disable` = 0
GROUP BY
    `cats_has_doctors`.`relationId`
HAVING FIND_IN_SET('1,2,3,4', `areasIdiesTemp`);

关于mysql - mysql 5.5中'having clause'中的未知列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53634336/