---

title: @rowno用法 date: 2021-06-19 17:07:29 permalink: /pages/8c8f64/ categories:

• backend
• 数据库知识与设计
• mysql tags:

---

MYSQL 使用@rowNo获取排名

#首先，看看@rowNo的用法：
select  @rowNo := @rowNo as rank FROM (select @rowNo :=1) q

在看具体引用，如何排名：
SELECT * FROM (select  @rowNo := @rowNo + 1 as rank, ppr.uid from poem_program_read ppr LEFT JOIN poem_user pus ON pus.id = ppr.uid,
  (select @rowNo :=0) q  WHERE ppr.pid = #{pid}
order by ppr.likeCount DESC,ppr.create_time) t
WHERE t.uid = #{uid}

删除重复数据

delete from old_gamerole where id in (SELECT id from (select id,role from old_gamerole group by role having count(role) > 0) tmp1)

主要通过having count(role) > 0查询出重复的数据

查询数据的rowno

SELECT @rowno:=@rowno+1 as rowno,r.* from guaguale_canyu r,(select @rowno:=0) t  where r.ggId=210
sum条件统计
select sum(if(type=1,point,0)) from user group by userId;