JavaEar 专注于收集分享传播有价值的技术资料

MySQL: Link answers to comments, grouped and limited

I got the following MySQL table:

 +----+--------+------+
 | id | answer | type |
 +----+--------+------+
>| 1  | -1     | 1    |
 | 2  |  1     | 2    |
 | 3  |  1     | 1    |
>| 4  | -1     | 2    |
 | 5  |  4     | 2    |
 | 6  |  4     | 1    |
>| 7  | -1     | 1    |
 | 8  |  7     | 2    |
>| 9  | -1     | 2    |
>| 10 | -1     | 1    |
>| 11 | -1     | 2    |
 +----+--------+------+
 > = original comment
  • The entries with answer = -1 are the original comments.
  • The entries with answer != -1 are answers to the comment with the respective id.
  • Furthermore there are types of comments (in this case 1 or 2).

Now I want to retrieve the original comments (for a specified type and a specified limit) and their answers (type and limit do not matter). Further it would be great to group the result by the two types (again, only the original comment's type matters for grouping).

A query result for type=1 and limit=2 should look like that (not grouped yet):

 +----+--------+------+
 | id | answer | type |
 +----+--------+------+
>| 1  | -1     | 1    |
 | 2  |  1     | 2    |
 | 3  |  1     | 1    |
>| 7  | -1     | 1    |
 | 8  |  7     | 2    |
 +----+--------+------+

I have tried several kinds of queries for hours now, can I implement it without a procedure? What would the procedure look like?

I would appreciate your help a lot.

2个回答

    最佳答案
  1. Store it as a separate column or even a different table if you are worried about too much data duplication. You are overthinking it.

  2. 参考答案2
  3. Use a subquery to get 2 original comments of type-1 first, then join the table again to get both original comments and responses.

    select b.*
    from (
      select id
      from answers
      where type = 1 and answer = -1
      limit 2) a
    join answers b on a.id = b.id or a.id = b.answer;
    

    fiddle

    To select 2 original comments of each type, you'll need to use user-defined-variable to index each original comment. The following code indexes the first original comment as 1, the second one as 2, and so on, then resets to 1 when the type changes. Finally in the final where statement filter out indices greater than 2.

    select b.*, a.idx
    from (
      select id, @idx:=if(@type=a.type,@idx+1,1) idx, @type:=a.type
      from (select id, type from answers2 where answer is null order by type) a
      join (select @type:=null, @idx:=null) b) a
    join answers2 b on a.id = b.id or a.id = b.answer
    where a.idx <= 2;
    

    fiddle