我的桌子是:

id   uid  art_id
---|-----|--------
1  | 10  |  11   |
2  | 11  |  23   |
5  | 10  |  16   |
3  | 10  |  13   |
4  | 11  |  59   |
6  | 11  |  36   |
7  | 11  |  55   |
8  | 13  |  68   |


我需要从表中从每个uid中选择两个结果art_id,但按行顺序最多选择两个。

更新:我有一个CCA 19m行的大型数据库,需要从每个用户中选择最后两篇文章(每个SQL请求限制用户200个)。按uid排序

结果将是:

id   uid  art_id
---|-----|--------
1  | 10  |  11   |
5  | 10  |  16   |
2  | 11  |  23   |
4  | 11  |  59   |
8  | 13  |  68   |
3  | 10  |  13   |
6  | 11  |  36   |
7  | 11  |  55   |

最佳答案

SELECT MOD (ROW_NUM, 2) + TRUNC (ROW_NUM, 2) AS GROUPBY2,
     ROW_NUM,
     ID,
     UID,
     ART_ID
FROM (SELECT ROW_NUMBER ()
                OVER (PARTITION BY UID ORDER BY UID DESC)
                ROW_NUM,
             ID,
             UID,
             ART_ID
        FROM TABLE )
ORDER BY GROUPBY2

10-07 19:10
查看更多