mysql - 为什么此查询在 MySQL 5.1.56 中失败？

Question

以下查询在 MySQL 5.1.56 中失败:

SELECT 
shop_id, products.product_id AS
product_id, brand, title, price, image, image_width, image_height

FROM products, users LEFT JOIN

(
    SELECT fav5.product_id AS product_id, SUM(CASE 
    WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
    WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
    ELSE 0
    END) AS favorites_count
    FROM favorites fav5
    GROUP BY fav5.product_id 

) AS fav6 ON products.product_id=fav6.product_id
WHERE products.product_id= 46876 AND users.user_id!=products.product_id

错误是

#1054 - Unknown column 'products.product_id' in 'on clause'

没有用户表的这种修改不会失败:

SELECT 
shop_id, products.product_id AS
product_id, brand, title, price, image, image_width, image_height

FROM products LEFT JOIN

(
    SELECT fav5.product_id AS product_id, SUM(CASE 
    WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
    WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
    ELSE 0
    END) AS favorites_count
    FROM favorites fav5
    GROUP BY fav5.product_id 

) AS fav6 ON products.product_id=fav6.product_id
WHERE products.product_id= 46876

MySQL 5.0.67 中的查询均未失败。 (我从 5.0.67 导出数据库并导入到 5.1.56，所以结构应该是相同的。)

产品表确实有一个 product_id 列，类型为 int(10)。 favorites 表还有一个类型为 int(10) 的 product_id 列。这是怎么回事？

Answer 1

就像交换表顺序一样简单:

FROM users, products LEFT JOIN

小心，你在混join notations .