选择不同的、非空的，除非空是该记录组合的唯一值 (tsql) [英] Selecting distinct, non-null, unless null is the only value for that record combination (tsql)

问题描述

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

I have a table with Student ID, Service, and Provider. I want to show the DISTINCT Providers for each Service, but ONLY show NULL Providers if there is no other Provider for that Service and ID.

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

In other words, if a Student has a certain Provider and Service, I don't want to select where the Provider is NULL, unless that specific Student and Provider do not have another non-NULL Provider, in which case I DO want to select the NULL Provider row. I also don't want duplicates for the non-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  

我希望通过选择得到的结果是:

And what I'd like to get as a result of my select is:

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.但是，学生 3 的OT"为 NULL，并且由于该学生/提供者组合没有非 NULL 值，因此我想显示该行的 NULL 值.

So, for example, Student 1 has a non-NULL and a NULL provider for Service "SL," so I just want to show the non-NULL provider, Joe. Student 2 has four "Sped" providers: Mary (twice), Jim, and NULL, so I only want to show Mary (once) and Jim. Student 3 has Service "SL" twice, with Larry and NULL, so I only want to show Larry. However, Student 3 has a NULL for "OT," and since there are no non-NULL values for that Student/Provider combination, I want to show the NULL value for that row.

此报告旨在向服务提供商展示他们在哪些方面为学生提供了提供商(一件好事)，以及学生在哪些地方获得了没有任何提供商的服务(一件坏事).我的用户很容易混淆，所以我需要以这种方式呈现.感谢您的帮助！

This report is to show service providers where they've given the student a provider (a good thing) but also where students have a service without any provider (a bad thing.) My users are easily confused, so I need to present it this way. Thank you for any help!

推荐答案

试试这个(之前 OP 说他们在 SQL Server 2000 上):

try this (before OP said they were on 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)

EDIT OP 说 SQL Server 2000 之后的版本:

EDIT version after OP said 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)

这篇关于选择不同的、非空的，除非空是该记录组合的唯一值 (tsql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！