软件开发培训班 >> 编程开发 >> MySQL


Mysql教程分组排名实现示例详解

1.鏁版嵁婧?/h2>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

2.鏁版嵁鏁翠綋鎺掑悕

1锛夋櫘閫氭帓鍚?/h3>

浠?寮�濮嬶紝鎸夌収椤哄簭涓�娆″線涓嬫帓(鐩稿悓鐨勫�间篃鏄笉鍚岀殑鎺掑悕)銆?/p> 

set @rank =0;
select 
	city ,
	score, 
	@rank := @rank+1 rank 
from cs 
order by score desc;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

2锛夊苟鍒楁帓鍚?/h3>

鐩稿悓鐨勫�兼槸鐩稿悓鐨勬帓鍚?浣嗘槸涓嶇暀绌轰綅)銆?/p> 

set @rank=0,@price=null;
select cs.* ,
 case when @price = score then @rank 
 when @price := score then @rank := @rank+1 end rank  
 from cs order by score desc;
 -- 褰撴煡璇㈢殑score 鍊?= @price鏃讹紝杈撳嚭@rank,
 -- 褰撲笉绛夋椂锛屽皢score鍊艰祴缁橜price 锛屽苟杈撳嚭@rank := @rank+1
 
-- 鎴栬�?
set @rank=0,@price=null;
select 
	a.city,a.score,a.rank 
from 
(select cs.*,
	@rank := if(@p=score,@rank,@rank+1) rank,
	@p := score
from cs 
order by score desc) a;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

3锛夊苟鍒楁帓鍚?/h3>

鐩稿悓鐨勫�兼槸鐩稿悓鐨勬帓鍚?浣嗘槸鐣欑┖浣?銆?/p> 

set @rank=0,@price=null, @z=1;
select 
	a.city,a.score,a.rank 
from 
(select 
	cs.*,
	@rank := if(@p=score,@rank,@z) rank,
	@p := score,@z :=@z+1
from cs 
order by score desc) a;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

3.鏁版嵁鍒嗙粍鍚庣粍鍐呮帓鍚?/h2>

1锛夊垎缁勬櫘閫氭帓鍚?/h3>

浠?寮�濮嬶紝鎸夌収椤哄簭涓�娆″線涓嬫帓(鐩稿悓鐨勫�间篃鏄笉鍚岀殑鎺掑悕)銆?/p> 

set @rank=0,@c=null;
select 
	cs.city,cs.score,
	@rank := if(@c = city,@rank+1,1) rank,
	@c := city
from cs 
order by cs.city,cs.score;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

2锛夊垎缁勫悗骞跺垪鎺掑悕

缁勫唴鐩稿悓鏁板�兼帓鍚嶇浉鍚岋紝涓嶅崰绌轰綅銆?/p> 

set @rank=0,@c=null,@s=null;
select 
	cs.city,cs.score,
	@rank := if(@c=city,if(@s=score,@rank,@rank+1),1) rank ,
	@c := city,
	@s :=score 
from cs 
order by cs.city,cs.score;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

3锛夊垎缁勫悗骞跺垪鎺掑悕

缁勫唴鐩稿悓鏁板�兼帓鍚嶇浉鍚岋紝闇�瑕佸崰绌轰綅銆?/p> 

set @rank=0,@c=null,@s=null;
select 
	cs.city,cs.score,
	@rank := if(@c=city,if(@s=score,@rank,@rank+1),1) rank ,
	@c := city,
	@s :=score 
from cs 
order by cs.city,cs.score;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

4.鍒嗙粍鍚庡彇鍚勭粍鐨勫墠涓ゅ悕

鈶?鏂规硶涓�锛氭寜鐓у垎缁勬帓鍚嶇殑涓夌鏂瑰紡锛岀劧鍚庨檺瀹氭帓鍚嶇殑鍊?/p> 

set @rank=0,@z=0,@c=null,@s=null;
select a.city,a.score,a.rank from 
(select 
	cs.city city,cs.score score,
	@z := if(@c=city,@z+1,1),
	@rank := if(@c=city,if(@s=score,@rank,@z),1) rank,
	@c := city,
	@s :=score 
from cs 
order by cs.city,cs.score desc) a
where a.rank<=2;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

鈶?鍐呴儴鏌ヨ

SELECT * FROM cs c
WHERE (
    SELECT count(*) FROM cs
    WHERE c.city=cs.city AND c.score<cs.score )<2
ORDER BY city,score DESC;

缁撴灉濡備笅锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

涓婅堪浠ｇ爜鐨勬墽琛屽師鐞嗗涓嬪浘锛?/p>

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

浠ヤ笂灏辨槸Mysql鏁欑▼鍒嗙粍鎺掑悕瀹炵幇绀轰緥璇﹁В鐨勮缁嗗唴瀹癸紝鏇村鍏充簬Mysql鍒嗙粍鎺掑悕鐨勮祫鏂欒鍏虫敞鑴氭湰涔嬪鍏跺畠鐩稿叧鏂囩珷锛?/p>