查询排名的例子

查询排名的例子

create table jingxiaoshang (
id int (11),
fid int (11),
name varchar (765),
xx int (11)
);
insert into jingxiaoshang (id, fid, name, xx) values(‘1’,’0’,’第一级’,’1’);
insert into jingxiaoshang (id, fid, name, xx) values(‘2’,’1’,’第二级’,’2’);
insert into jingxiaoshang (id, fid, name, xx) values(‘3’,’2’,’第三级1’,’3’);
insert into jingxiaoshang (id, fid, name, xx) values(‘4’,’3’,’第四级1’,’4’);
insert into jingxiaoshang (id, fid, name, xx) values(‘5’,’3’,’第四级2’,’4’);
insert into jingxiaoshang (id, fid, name, xx) values(‘6’,’5’,’第五级’,’5’);
insert into jingxiaoshang (id, fid, name, xx) values(‘7’,’2’,’第三级2’,’3’);
insert into jingxiaoshang (id, fid, name, xx) values(‘8’,NULL,’没有级别’,NULL);
insert into jingxiaoshang (id, fid, name, xx) values(‘9’,’-1’,’wuji’,NULL);
MYSQL的排名算法
数据：

按fid从小到大排名后（重复的顺序排名）
排名结果：

查询语句：

— id fid上级id name经销商名称 pm排名
— fid int型 没有上级的fid<0 不参与排名
— 排名根据fid 重复的fid排名一样 group by fid order by fid asc
— 排名不按fid重复的排名一样的 group by id order by fid asc
SELECT FROM
( SELECT A.,@rank:=@rank+1 AS pm FROM
(
SELECT id,NAME,fid FROM jingxiaoshang WHERE fid IS NOT NULL AND fid>=0 GROUP BY id ORDER BY fid ASC
) A ,(SELECT @rank:=0) B
) M
ORDER BY M.pm

— 排名查询 经销商级别查询
SELECT j.,C.pm FROM jingxiaoshang j LEFT JOIN
(SELECT A.,@rank:=@rank+1 AS pm FROM
(
SELECT id,NAME,fid FROM jingxiaoshang WHERE fid IS NOT NULL AND fid>=0 GROUP BY fid ORDER BY fid ASC
) A ,(SELECT @rank:=0) B ORDER BY A.fid) C ON j.fid=C.fid
WHERE j.fid>=0
ORDER BY C.pm