从每个类别原则查询生成器获取 2 条记录

Question

假设我有下表

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

如何为每个user_id选择两行？所以我的结果将是：

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | awsome        |
| 2         | thats cool    |
| 3         | i hate this   |
| 3         | okey          |
| 6         | this is weird |
| 6         | hello?        |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

这

可以通过单个有效查询来实现吗？还是有必要进行子选择？

Answer 1

SELECT user_id,
       comment
FROM
(
SELECT user_id,
       comment,
       CASE WHEN @user_id = T.user_id THEN 
       @ROW:=@ROW+1
       ELSE
       @ROW:=1
       END ROW,
       @user_id:=T.user_id
FROM 
Table1 T
,(SELECT @ROW=1,@user_id=null) R)
AS T1 WHERE ROW <=2

输出

user_id comment
2       thats cool
2       awesome
3       i hate this
3       okay
6       this is weird
6       hello?
9       how are you
16      too slow
16      yes
17      alrighty

现场演示

http://sqlfiddle.com/#!9/c31356/4