SQL-在一个字段中选择不同的记录，而在另一个字段中选择最高的记录

Question

在我有一个像这样的表的情况下：

int id (PK)
int staff_id
int skill_id
bit mainskill

我只想为每个工作人员（用staff_id表示）选择一个记录，列出他们的主要技能，用mainskill中的（1）表示。如果没有主要技能，我想退还该工作人员的任何技能记录。例如：

id   staff_id   skill_id   mainskill
1    1          24         1
2    1          55         0
3    1          7          0
4    4          24         0
5    4          18         0
6    6          3          0
7    6          18         1

查询应返回：

id   staff_id   skill_id   mainskill
1    1          24         1
4    4          24         0
7    6          18         1

我尝试了分组，DISTINCT等的各种组合，但无法获得我想要的输出。任何帮助表示赞赏。

Answer 1

SQL Server 2005+，使用CTE：

WITH rows AS (
  SELECT t.id,
         t.staff_id,
         t.skill_id,
         t.mainskill,
         ROW_NUMBER() OVER (PARTITION BY t.staff_id ORDER BY t.mainskill DESC) AS rank
    FROM TABLE t)
  SELECT r.id,
         r.staff_id,
         r.skill_id,
         r.mainskill
    FROM rows r
   WHERE r.rank = 1
ORDER BY r.staff_id

SQL Server 2005+，等效于非CTE：

  SELECT r.id,
         r.staff_id,
         r.skill_id,
         r.mainskill
    FROM (SELECT t.id,
                 t.staff_id,
                 t.skill_id,
                 t.mainskill,
                 ROW_NUMBER() OVER (PARTITION BY t.staff_id ORDER BY t.mainskill DESC) AS rank
            FROM TABLE t) r
   WHERE r.rank = 1
ORDER BY r.staff_id

两者都使用ROW_NUMBER，仅自SQL
Server 2005起可用。