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

Question

我有一张包含学生 ID、服务和提供者的表格。我想显示每个服务的 DISTINCT 提供者，但如果该服务和 ID 没有其他提供者，则仅显示 NULL 提供者。

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

这是一个示例表:

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 服务提供者和一个服务“SL”的 NULL 提供者，所以我只想展示非 NULL 提供者 Joe。学生 2 有四个“Sped”提供者:Mary(两次)、Jim 和 NULL，所以我只想显示 Mary(一次)和 Jim。学生 3 有两次 Service "SL"，分别是 Larry 和 NULL，所以我只想展示 Larry。但是，Student 3 的“OT”为 NULL，并且由于该 Student/Provider 组合没有非 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)