具有限制的 MySQL 嵌套 SQL

Question

我知道不可能在嵌套的 IN 中使用限制，但我认为有一种方法可以做到这一点，我只是不确定如何做。

我有一个包含评分和评论的表格(为解释而简化)

mySingleTable: +----+------------------+-----------+-----------+-----------------+| id | reviewer_comment | is_rating | parent_id | reviewer_rating | +----+------------------+-----------+-----------+-----------------+ | 1  | well done rateA  | 1         |  0        | 5 Stars         | | 2  | commentAonRateA  | 0         |  1        |                 || 3  | commentBonRateA  | 0         |  1        |                 | | 4  | commentConRateA  | 0         |  1        |                 || 5  | commentDonRateA  | 0         |  1        |                 | | 6  | commentEonRateA  | 0         |  1        |                 | | 7  | commentFonRateA  | 0         |  1        |                 | | 8  | well done rateB  | 1         |  0        | 4 Stars         | | 9  | well done rateC  | 1         |  0        | 5 Stars         | | 11 | well done rateD  | 1         |  0        | 3 Stars         | | 12 | well done rateE  | 1         |  0        | 2 Stars         || 13 | well done rateF  | 1         |  0        | 5 Stars         | | 14 | well done rateG  | 1         |  0        | 3 Stars         | | 15 | commentAonRateD  | 0         |  11       |                 | +----+------------------+-----------+-----------+-----------------+ 

So,
if is_rating = 1, its a rating.
if is_rating = 0, its a comment on the rating (its parent rating is where parent_id=id)

so this would look like:

well done rateA *****   commentAonRateA   commentBonRateA   commentConRateA   commentDonRateA   commentEonRateA   commentFonRateA    well done rateB ****  well done rateC *****  well done rateD ***   commentAonRateD    well done rateE **  well done rateF *****  well done rateG ***  

What I want to do is select the newest five ratings, with the ASSOCIATED newest 5 comments, using only 1 query

So, some how join these two:

SELECT ratings.*
        FROM mySingleTable as ratings
        WHERE 
        is_rating = 1
        ORDER BY timestamp DESC LIMIT 0, 5  

    SELECT comments.*
        FROM mySingleTable as comments
        Where 
        comments.parent_id = ratings.id 
        AND is_rating = 0
        ORDER BY timestamp DESC LIMIT 0, 5

第二个查询需要以某种方式了解评级查询

Answer 1

请尝试这个现已简化并经过测试的查询。

SELECT *
FROM
 (SELECT *,
        IF (group_id = @prev,
            @n := @n + 1,
            @n := 1 AND @prev := group_id) as position
 FROM (
   SELECT mySingleTable.*, group_id
   FROM   mySingleTable
   INNER JOIN
       (SELECT id AS group_id
        FROM   mySingleTable
        WHERE  is_rating = 1
        ORDER BY timestamp DESC LIMIT 0, 5
       ) AS top5ratings
   ON     mySingleTable.id = group_id OR mySingleTable.parent_id = group_id
   ORDER BY group_id DESC,
            mySingleTable.parent_id ASC,
            timestamp DESC) AS all_reviews
  JOIN  (SELECT @n := 0, @prev := 0) AS setup) AS reviews
WHERE reviews.position < 7

请记住，SELECT * 是不好的做法。我用它来简化阅读。