SQL - 从另一个字段中选择一个字段中具有最高记录的不同记录 [英] SQL - select distinct records in one field with highest records from another field

问题描述

在我有这样一张桌子的情况下:

In a scenario where I have a table like so:

int id (PK)
int staff_id
int skill_id
bit mainskill

我只想为每个工作人员选择一条记录(由 staff_id 表示)列出他们的主要技能，如 mainskill 中的 (1) 表示.如果没有主要技能，我想返回该员工的任何技能记录.例如:

I want to select only ONE record for each staff member (represented by staff_id) listing their main skill as represented by a (1) in mainskill. If no main skill is present, I want to return any of the skill records for that staff member. For example:

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 等的各种组合，但无法获得我想要的输出.任何帮助表示赞赏.

I've tried various combinations of grouping, DISTINCT etc but can't get the output I'm after. Any help appreciated.

推荐答案

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.

Both use ROW_NUMBER, which is only available since SQL Server 2005.

这篇关于SQL - 从另一个字段中选择一个字段中具有最高记录的不同记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！