mysql - 为什么此 SQL 查询返回错误？

Question

我正在使用子查询来访问我的 where 子句中的别名。只要我只选择“MIN(r.price)”，这就可以正常工作。当我选择“*, ...”时，它返回错误“...重复的列 ID ...”。这是为什么？子查询本身运行良好。

这里是:

SELECT *
FROM
(
    SELECT *, MIN(r.price) as min_price
    FROM tl_frp_presentation as p
    INNER JOIN tl_frp_object as o
        ON p.objectID = o.id
    INNER JOIN tl_tag as c
        ON c.id = o.id
        AND c.from_table = "tl_frp_object"
    LEFT JOIN tl_frp_rooms as r
        ON r.pid = o.id
    WHERE p.type = "tl_frp_object"
    GROUP BY p.id
) as inner_t
WHERE min_price >= 100

感谢任何帮助!

Answer 1

只需将ALIAS 名称添加到列

试试这个:

SELECT *
FROM (SELECT *, MIN(r.price) AS min_price 
      FROM tl_frp_presentation AS p
      INNER JOIN tl_frp_object AS o ON p.objectID = o.id
      INNER JOIN tl_tag AS c ON c.id = o.id AND c.from_table = "tl_frp_object"
      LEFT JOIN tl_frp_rooms AS r ON r.pid = o.id
      WHERE p.type = "tl_frp_object"
      GROUP BY p.id
     ) AS inner_t
WHERE min_price >= 100;

您还可以使用HAVING 子句来满足您的要求，如下所示

SELECT *, MIN(r.price) AS min_price 
FROM tl_frp_presentation AS p
INNER JOIN tl_frp_object AS o ON p.objectID = o.id
INNER JOIN tl_tag AS c ON c.id = o.id AND c.from_table = "tl_frp_object"
LEFT JOIN tl_frp_rooms AS r ON r.pid = o.id 
WHERE p.type = "tl_frp_object"
GROUP BY p.id HAVING min_price >= 100;