为什么这个SQL查询返回一个错误？

Question

我使用子查询来访问where子句中的别名。只要我只选择"MIN(r.price)“，就可以正常工作。当我选择"*，“它返回一个错误"...duplicate列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

只需将别名名称添加到列

试试这个：

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;

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

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;

Answer 2

您已经给子选择了一个别名，这很好！

使用它:-)

) as inner_t
WHERE inner_t.min_price >= 100

更好的是，您不需要将其包装到子选择中：

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