选择不同的非空值，除非空值是该记录组合的唯一值 (tsql)

Question

我有一张包含学生 ID、服务和提供商的表。我想显示每个服务的不同提供程序，但仅在该服务和 ID 没有其他提供程序时才显示 NULL 提供程序。

换句话说，如果学生有某个提供者和服务，我不想选择提供者为 NULL 的地方，除非该特定学生和提供者没有另一个非 NULL 提供者，在这种情况下我确实想选择NULL 提供者行。我也不希望非空值重复。

这是一个示例表：

ID  Service Provider  
1   SL      Joe  
1   SL      NULL  
2   Sped    Mary  
2   Sped    Jim  
2   Sped    NULL  
2   Sped    Mary  
3   SL      Larry  
3   OT      NULL  
3   SL      NULL  

我选择的结果是：

ID  Service Provider  
1   SL      Joe  
2   Sped    Mary  
2   Sped    Jim  
3   SL      Larry  
3   OT      NULL  

因此，例如，学生 1 有一个非 NULL 和一个 NULL 服务提供者“SL”，所以我只想显示非 NULL 提供者，乔。学生 2 有四个“Sped”提供者：Mary（两次）、Jim 和 NULL，因此我只想显示 Mary（一次）和 Jim。学生 3 有两次服务“SL”，分别为 Larry 和 NULL，因此我只想显示 Larry。但是，学生 3 的“OT”为 NULL，并且由于该学生/提供者组合没有非 NULL 值，因此我想显示该行的 NULL 值。

该报告旨在向服务提供商展示他们在哪些地方为学生提供了提供商（一件好事），以及哪些学生在没有任何提供商的情况下获得了服务（一件坏事）。我的用户很容易感到困惑，因此我需要将其呈现出来方式。感谢您的帮助！

Answer 1

试试这个（在OP说他们在SQL Server 2000之前）：

--ONLY WORKS ON SQl Server 2005 and up
DECLARE @YourTable table (ID int, Service varchar(5), provider varchar(5))
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,'SL'  ,'Joe')
INSERT INTO @YourTable VALUES (1,'SL'  ,NULL)
INSERT INTO @YourTable VALUES (2,'Sped','Mary')
INSERT INTO @YourTable VALUES (2,'Sped','Jim')
INSERT INTO @YourTable VALUES (2,'Sped',NULL)
INSERT INTO @YourTable VALUES (2,'Sped','Mary')
INSERT INTO @YourTable VALUES (3,'SL'  ,'Larry ')
INSERT INTO @YourTable VALUES (3,'OT'  ,NULL)
INSERT INTO @YourTable VALUES (3,'SL'  ,NULL)
SET NOCOUNT OFF

SELECT DISTINCT
    ID,Service,provider
    FROM (SELECT
              ID,Service,provider,ROW_NUMBER() OVER(PARTITION BY ID,Service ORDER BY ID,Service,Provider desc) AS Rank
              FROM @YourTable
         ) dt
    WHERE dt.provider IS NOT NULL OR dt.Rank=1
    ORDER BY ID,Service,provider

输出：

ID          Service provider
----------- ------- --------
1           SL      Joe
2           Sped    Jim
2           Sped    Mary
3           OT      NULL
3           SL      Larry

(5 row(s) affected)

OP说SQL Server 2000之后的编辑版本：

CREATE TABLE #YourTable (ID int, Service varchar(5), provider varchar(5))
SET NOCOUNT ON
INSERT INTO #YourTable VALUES (1,'SL'  ,'Joe')
INSERT INTO #YourTable VALUES (1,'SL'  ,NULL)
INSERT INTO #YourTable VALUES (2,'Sped','Mary')
INSERT INTO #YourTable VALUES (2,'Sped','Jim')
INSERT INTO #YourTable VALUES (2,'Sped',NULL)
INSERT INTO #YourTable VALUES (2,'Sped','Mary')
INSERT INTO #YourTable VALUES (3,'SL'  ,'Larry ')
INSERT INTO #YourTable VALUES (3,'OT'  ,NULL)
INSERT INTO #YourTable VALUES (3,'SL'  ,NULL)
SET NOCOUNT OFF


SELECT
    y.ID,y.Service,y.provider
    FROM #YourTable y
        INNER JOIN (SELECT
                        ID,Service,MAX(provider) AS MaxProvider
                        FROM #YourTable
                        GROUP BY ID,Service
                        HAVING MAX(provider) IS NOT NULL
                   ) dt ON y.ID=dt.ID AND y.Service=dt.Service
    WHERE provider IS NOT NULL
UNION
SELECT
    ID,Service,MAX(provider) AS MaxProvider
    FROM #YourTable
    GROUP BY ID,Service
    HAVING MAX(provider) IS  NULL
    ORDER BY ID,Service,provider

输出：

ID          Service provider
----------- ------- --------
1           SL      Joe
2           Sped    Jim
2           Sped    Mary
3           OT      NULL
3           SL      Larry
Warning: Null value is eliminated by an aggregate or other SET operation.

(5 row(s) affected)