mysql group by与limit_mysql – GROUP BY中的ORDER BY和LIMIT

这是一个难题,但是这个怎么样：

SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items

FROM wb_user_book_current_item T

WHERE NOT EXISTS

(

SELECT 1

FROM wb_user_book_current_item T2

WHERE T2.user_id = T.user_id

ORDER BY T2.`timestamp` DESC

LIMIT 10,1

)

OR T.`timestamp` > (

SELECT T2.`timestamp`

FROM wb_user_book_current_item T2

WHERE T2.user_id = T.user_id

ORDER BY T2.`timestamp` DESC

LIMIT 10,1

)

GROUP BY user_id

当然,这假设您不会为同一用户创建具有相同时间戳的两行.

如果您的时间戳字段始终为正整数,您还可以用COALESCE替换NOT EXISTS … OR：

SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items

FROM wb_user_book_current_item T

WHERE T.`timestamp` > COALESCE((

SELECT T2.`timestamp`

F