mysql top Nをグループ化して前のN本を取る
507 ワード
SELECT
itemTime,
reporter,
aggsCount
FROM
(
SELECT
itemTime,
reporter,
aggsCount,
IF (@tmp = itemTime ,@rank :=@rank + 1 ,@rank := 1) AS new_rank ,@tmp := itemTime AS tmp
FROM
(
SELECT
DATE_FORMAT(w.commit_time, '%Y-%m-%d') AS itemTime,
w.create_by reporter,
count(w.id) aggsCount
FROM
t_workreport w
GROUP BY
itemTime,
reporter
ORDER BY
commit_time
) tempA
) tempB
WHERE
new_rank <= 10