仅显示除NULL之外的每个组的第一行

I have a table of patients. Patients may be single or married. I want to list all of the patients in alphabetical order, but I want to show only female patient for married couples (ignore the husband).
If multiple patients have same CaseNo, I consider they are couples.
Genders: 0 is female, 1 is male.

我有一张病人表。患者可以单身或结婚。我想按字母顺序列出所有患者,但我想只为已婚夫妇展示女性患者(忽略丈夫)。如果多个患者有相同的CaseNo,我认为他们是夫妻。性别:0是女性,1是男性。

PatientID  CaseNo  Gender  Name
1          1       1       Mike
2          NULL    0       Pamela
3          2       0       Mary
4          2       1       John
5          NULL    1       Dave

Expected output is:

预期产出是:

PatientID  CaseNo  Gender  Name
5          NULL    1       Dave
3          2       0       Mary
1          1       1       Mike
2          NULL    0       Pamela

John is excluded because his wife is on the list.

约翰被排除在外,因为他的妻子在名单上。

I tried one of the answers in this question, but it excludes the records which have CaseNo as NULL.

我尝试了这个问题中的一个答案,但它排除了CaseNo为NULL的记录。

WITH cte 
     AS (SELECT PatientID, 
                CaseNo, 
                Gender, 
                Name, 
                Row_number() 
                  OVER ( 
                    partition BY CaseNo
                    ORDER BY Gender DESC) rn 
         FROM Patients) 
SELECT PatientID, 
       CaseNo, 
       Gender, 
       Name

FROM   cte 
WHERE  rn = 1 
ORDER BY Name

Is it possible to achieve this?

是否有可能实现这一目标?

2 个解决方案

#1

You can try it this way

你可以这样试试

SELECT PatientID, CaseNo, Gender, Name
  FROM 
(
  SELECT PatientID, CaseNo, Gender, Name, 
         ROW_NUMBER() OVER (PARTITION BY CaseNo ORDER BY Gender) rnum,
         COUNT(*) OVER (PARTITION BY CaseNo) rcnt
    FROM Patients
) q
 WHERE CaseNo IS NULL 
    OR rcnt = 1
    OR (rcnt > 1 AND rnum = 1)
 ORDER  BY Name

Output:

| PATIENTID | CASENO | GENDER |   NAME |
|-----------|--------|--------|--------|
|         5 | (null) |      1 |   Dave |
|         3 |      2 |      0 |   Mary |
|         1 |      1 |      1 |   Mike |
|         2 | (null) |      0 | Pamela |

Here is SQLFiddle demo

这是SQLFiddle演示

#2

Query:

SQLFIDDLEExample

  WITH cte 
     AS (SELECT PatientID, 
                CaseNo, 
                Gender, 
                Name, 
               CASE WHEN CaseNo is null
               THEN 1 
               ELSE  Row_number() 
                  OVER ( 
                    partition BY CaseNo
                    ORDER BY Gender ASC) END  rn 
         FROM Patients) 
SELECT PatientID, 
       CaseNo, 
       Gender, 
       Name

FROM   cte 
WHERE  rn = 1 
ORDER BY Name asc

Result:

| PATIENTID | CASENO | GENDER |   NAME |
|-----------|--------|--------|--------|
|         5 | (null) |      1 |   Dave |
|         3 |      2 |      0 |   Mary |
|         1 |      1 |      1 |   Mike |
|         2 | (null) |      0 | Pamela |

#1