set @row=0;
set @mid=0;
set @cid=0;
update hotmcontent_ranking_20190304_20190310 inner join(
SELECT id, apid, acid, readcount,
case when @mid = apid and @cid=acid then @row:=@row+1 else @row:=1 end ranking,
@mid:=apid,
@cid:=acid
FROM hotmcontent_ranking_20190304_20190310
where acid<>0
order by apid,acid,readcount desc
) c on hotmcontent_ranking_20190304_20190310.id = c.id set hotmcontent_ranking_20190304_20190310.ranking = c.ranking;
现在报错:An error occured in multi-table update
表里面有近300万数据。
我要给数据做先分组再排名。
排名字段ranking。搞不定了。在线等挺急的
set @mid=0;
set @cid=0;
update hotmcontent_ranking_20190304_20190310 inner join(
SELECT id, apid, acid, readcount,
case when @mid = apid and @cid=acid then @row:=@row+1 else @row:=1 end ranking,
@mid:=apid,
@cid:=acid
FROM hotmcontent_ranking_20190304_20190310
where acid<>0
order by apid,acid,readcount desc
) c on hotmcontent_ranking_20190304_20190310.id = c.id set hotmcontent_ranking_20190304_20190310.ranking = c.ranking;
现在报错:An error occured in multi-table update
表里面有近300万数据。
我要给数据做先分组再排名。
排名字段ranking。搞不定了。在线等挺急的
如果慢,增加索引apid,acid,readcount。
UPDATE
hotmcontent_ranking_20190304_20190310 t
SET
t.ranking =
(SELECT
COUNT(0)
FROM
(SELECT
t.apid,
t.acid,
t.readcount
FROM
hotmcontent_ranking_20190304_20190310 t) tt
WHERE tt.apid = t.apid
AND tt.acid = t.acid
AND tt.readcount <= t.readcount);
谢啦。我是要先分组然后再计算排名ranking
你这个是求总数。?