在MYSQL复杂查询中使用LIMIT的子查询

The Mysql version i'm using don't let me use a LIMIT inside subquery so i can speed up the query. How to use join instead in the next query?

我正在使用的Mysql版本不允许我在子查询中使用LIMIT,所以我可以加快查询速度。如何在下一个查询中使用join?

SELECT p . * , (

SELECT AVG( review_rating ) AS rating_total
FROM reviews r
WHERE r.review_item_ref = p.pattern_ref
AND r.review_state =1
GROUP BY r.review_item_ref
) AS review_rating
FROM patterns p
WHERE pattern_active >=0
AND p.pattern_family =27
AND p.pattern_ref
IN (

SELECT item_pattern_ref
FROM items
WHERE item_pattern_ref = p.pattern_ref LIMIT 1
)
GROUP BY p.pattern_id
ORDER BY p.pattern_description ASC , LCASE( p.pattern_description ) ASC
LIMIT 0 , 16

2 个解决方案

#1

Looks like you're trying to validate that p.pattern_ref has a match in items, is that correct?

看起来你正在尝试验证p.pattern_ref在项目中是否匹配,这是正确的吗?

If so,

SELECT 
    p . *,
    (SELECT AVG( review_rating ) AS rating_total
        FROM reviews r
        WHERE r.review_item_ref = p.pattern_ref
            AND r.review_state =1
        GROUP BY r.review_item_ref
    ) AS review_rating
FROM patterns p
INNER JOIN items i ON p.pattern_ref = i.item_pattern_ref
WHERE p.pattern_active >= 0
    AND p.pattern_family = 27
GROUP BY p.pattern_id
ORDER BY p.pattern_description ASC , LCASE( p.pattern_description ) ASC
LIMIT 0 , 16

should work. INNER JOIN will only return rows where both sides have a match.

应该管用。 INNER JOIN只返回双方都匹配的行。

#2

You can't use LIMIT in a subquery.

您不能在子查询中使用LIMIT。

#1