当我尝试连接两个表时，Mysql 抛出 "Unknow column column_name in on clause"错误。

Question

这个问题已经有答案了:

Mixing ANSI 1992 JOINs and COMMAs in a query (2 个回答)

已关闭 10 年前。

我试图从下面两张表中显示不同的低价酒店房间(低价房间的酒店，无需显示该酒店的其他房间)。但我收到错误“未知子句中的列column_name”。请检查我的查询

    SELECT  r.*,h.*
    FROM hotels_temp h,rooms_temp r
    INNER JOIN
    (
      SELECT 
        hotel_code,MIN(amount) mincost
      FROM 
        rooms_temp 
      GROUP BY hotel_code
    ) m ON r.amount = m.mincost and h.hotel_code=r.hotel_code 
    ORDER BY r.amount
    LIMIT 30

表名称:hotels_temp

表名称:rooms_temp

Answer 1

我可以通过以下查询自己解决这个问题。

    SELECT * FROM hotels_temp
    INNER JOIN
    (
        SELECT  r.amount,r.hotel_code FROM rooms_temp r
        INNER JOIN
        (
            SELECT hotel_code,amount, MIN(amount) mincost FROM rooms_temp  GROUP BY hotel_code
        ) m ON 
        r.amount = m.mincost GROUP BY r.hotel_code
    ) ht ON 
    ht.hotel_code=hotels_temp.hotel_code LIMIT 0,30