选择3个不同的列返回5列

SELECT DoctorId, Plan, StartDate, EndDate,Status
From DoctorContracts

In DoctorContracts Table I need Distinct doctorid, plan and StartDate but I need to get Status and Enddate

在DoctorContracts表中,我需要Distinct doctorid,计划和StartDate,但我需要获得状态和Enddate

However There is a Group column which is either CA or NH. I need the minimum StartDate between CA or NH.

但是,有一个Group列是CA或NH。我需要CA或NH之间的最小StartDate。

Example

DOCTORID.        PLAN.      STARTDATE.          ENDATE.          STATUS.     GROUP
9978.            Abc.       11/2/2010.          11/2/2015.       Z.          CA 
9978.            Abc.       11/2/2013.          11/2/2015.       N.          NH  
9978.            Xxx.       12/3/2011.          12/3/2015.       Z.          CA
9978.            Xxx.       10/3/2001.          12/6/2015.       Z.          NH

1 个解决方案

#1

I use the ROW_NUMBER function https://msdn.microsoft.com/en-us/library/ms186734.aspx:

我使用ROW_NUMBER函数https://msdn.microsoft.com/en-us/library/ms186734.aspx:

Here is what your SQL should look like:

这是您的SQL应该是什么样子:

SELECT DISTINCT d.[DoctorId]
      , d.[Plan]
      , d.[StartDate]
      , d.[EndDate]
      , d.[Status]
FROM DoctorContracts as d
    INNER JOIN 
        (SELECT [GROUP]
              , [STARTDATE]
              , ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY [STARTDATE] ASC) AS Row
         FROM DoctorContracts) as t
    ON d.[GROUP] = t.[GROUP]
        AND d.[STARTDATE] = t.[STARTDATE]
        AND t.[Row] = 1

You create a subset based on the min date partitioned by the group, and then filter to the earliest date.

您可以根据组划分的最小日期创建子集,然后过滤到最早的日期。

I have tested in SSMS, and it works as requested.

我已经在SSMS中测试过了,它可以按照要求运行。

~Cheers

#1