[SQL ] mysql greatest N per group?
Mysql版本 5.1
table rows 2億8千萬筆
table name :log
schema
`time`: datetime
`dom`: vchar
`src`: vchar
`dep`:vchar
`acc`:vchar
需求:想要找到 dep='a' 下 group by dom ,src
每個group中 time最新的十筆
sql:
SELECT time, dom, src,dep,acc
FROM
(SELECT time, dom, src,dep,acc ,
@country_rank := IF(@current_dom = dom
and @current_src = src, @country_rank + 1, 1) AS country_rank,
@current_dom := dom ,
@current_src := src
FROM log where dep = 'a' and time >date('2013-01-10')
ORDER BY dom asc, time DESC
) ranked
WHERE country_rank <= 10
現在的問題是SQL跑不完 直接time out
請問各位先進有什麼地方可以修改的嗎
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 60.251.42.235