SQL练习笔记二：分数排名

每个节点都是以下三种类型中的一种：

• Root: 如果节点是根节点。

• Leaf: 如果节点是叶子节点。

• Inner: 如果节点既不是根节点也不是叶子节点。

对于tree表，id是树节点的标识，p_id是其父节点的id。

• 
  
• 
  

CREATE TABLE tree(  id int(4) not null,  p_id int(4));
insert INTO tree (id) VALUES(1);insert INTO tree VALUES(2,1);insert INTO tree VALUES(3,1);insert INTO tree VALUES(4,2);insert INTO tree VALUES(5,2);

• 
  
• 
  

-- 根节点查询 --SELECT  id,  @Type := 'Root' AS Type FROM  tree WHERE  p_id IS NULL;
-- 内部节点查询 --SELECT  id,  @Type := 'Inner' AS Type FROM  tree WHERE  id IN ( SELECT DISTINCT p_id FROM tree WHERE p_id IS NOT NULL )   AND p_id IN ( SELECT DISTINCT id FROM tree WHERE id IS NOT NULL );
-- 叶子节点查询 --SELECT  id,  @Type := 'Leaf' AS TypeFROM  treeWHERE  id not in(SELECT DISTINCT p_id FROM tree WHERE p_id is not null);

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

创建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);

根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

• 
  
• 
  

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

• 
  
• 
  
• 
  

-- 解法2：根据Score分组后排降序，再按序增加1个Rank字段，得出来的结果与原score表进行关联查询后按照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;

实现排名功能，但是排名需要是非连续的：

• 
  
• 
  
• 
  
• 
  
• 
  
• 
  
• 
  
• 
  

-- 先统计比每个分数高的有几个，然后再加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;