艹逼国产,亚洲婷婷精品国产,av在线青青草,国产精品久久久久久久久毛毛,最近中文字幕免费mv第一季歌词在线观看,一区二区三区999,中文字幕亚洲日本欧美 ,欧美精品videos

點擊上方藍字關注「網(wǎng)優(yōu)小兵玩Python」

◆?◆?◆

編寫一個 SQL 查詢來實現(xiàn)分數(shù)排名。如果兩個分數(shù)相同，則兩個分數(shù)排名（Rank）相同。請注意，平分后的下一個名次應該是下一個連續(xù)的整數(shù)值。換句話說，名次之間不應該有“間隔”。

創(chuàng)建score表：

CREATE TABLE score ( Id INT ( 4 ) NOT NULL, Score DOUBLE NOT NULL );
INSERT INTO score VALUES(1,3.50);INSERT INTO score VALUES(2,3.65);INSERT INTO score VALUES(3,4.00);INSERT INTO score VALUES(4,3.85);INSERT INTO score VALUES(5,4.00);INSERT INTO score VALUES(6,3.65);

根據(jù)上述給定的?Scores?表，你的查詢應該返回（按分數(shù)從高到低排列）：

-- 解法1 --SELECT  Score,  ( SELECT count( DISTINCT Score ) FROM score WHERE Score >= s.Score ) AS 'Rank'FROM  score AS s ORDER BY  Score DESC;

-- 解法2：根據(jù)Score分組后排降序，再按序增加1個Rank字段，得出來的結果與原score表進行關聯(lián)查詢后按照Rank值排升序 --SET @Rank = 0;SELECT  score.Score,  b.Rank FROM  (  SELECT    @Rank := @Rank + 1 AS 'Rank',    a.Score   FROM    ( SELECT Score FROM score GROUP BY Score ORDER BY Score DESC ) AS a   ) AS b,  score WHERE  score.Score = b.Score ORDER BY  b.Rank;

實現(xiàn)排名功能，但是排名需要是非連續(xù)的：

-- 先統(tǒng)計比每個分數(shù)高的有幾個，然后再加1 --SELECT  Score,  ( SELECT COUNT( Score ) FROM score AS s2 WHERE s2.Score > s1.Score ) + 1 AS 'RANK' FROM  score AS s1 ORDER BY  Score DESC;