t-sql在两列之间选择最大值，或col两列为null时

Question

这对我来说并不容易描述标题(请原谅我(，但这是我的问题：

假设您有下表：

CREATE TABLE Subscriptions (product char(3), start_date datetime, end_date datetime);
INSERT INTO @Subscriptions 
VALUES('ABC', '2015-01-28 00:00:00', '2016-02-15 00:00:00'),
    ('ABC', '2016-02-04 12:08:00', NULL),
    ('DEF', '2013-04-15 00:00:00', '2013-06-10 00:00:00'),
    ('GHI', '2013-01-11 00:00:00', '2013-04-08 00:00:00');

现在，我想找出订阅已有多长时间或被动的时间。因此，我需要选择按产品分组的最新end_dates，但是如果end_date为null，那么我想要start_date。

所以 - 我有：

product start_date          end_date
ABC     28-01-2015 00:00    15-02-2016 00:00
ABC     04-02-2016 12:08    NULL
DEF     15-04-2013 00:00    10-06-2013 00:00
GHI     11-01-2013 00:00    08-04-2013 00:00

我想在查询中找到的内容：

product relevant_date
ABC 04-02-2016 12:08
DEF 10-06-2013 00:00
GHI 08-04-2013 00:00

我尝试使用联盟，这似乎很奏效，但是这很慢，我的问题是：是否有一种更有效的方法来解决此问题(我正在使用MS SQL Server 2012(：

SELECT [product] 
    ,MAX([start_date]) AS start_date
    ,NULL AS [end_date]
    ,MAX([start_date]) AS relevant_date
FROM Subscriptions  
where end_date IS NULL
GROUP BY product
UNION 
SELECT [product] 
    ,NULL
    ,MAX([end_date])
    ,MAX([end_date])
FROM Subscriptions  
where end_date IS not NULL and product not in (SELECT product FROM Subscriptions  
where end_date IS NULL)
GROUP BY product

(如果您对我的问题的另一个标题有建议，我也是所有的耳朵！(

Answer 1

2012年版本或更高版本，您可以使用distinct，first_value和isnull的组合，例如：

SELECT  DISTINCT 
        product, 
        FIRST_VALUE(ISNULL(end_date,start_date)) 
            OVER(PARTITION BY product 
                 ORDER BY ISNULL(end_date, '9999-12-31') DESC) AS EndDate
FROM Subscriptions

结果：

product EndDate
ABC     04.02.2016 12:08:00
DEF     10.06.2013 00:00:00
GHI     08.04.2013 00:00:00

对于2008年至2012年之间的版本，您可以使用带有row_number的cte获得相同的效果：

;WITH CTE AS
(
    SELECT  product,
            ISNULL(end_date,start_date) As relevant_date,
            ROW_NUMBER() OVER(PARTITION BY product ORDER BY ISNULL(end_date, '9999-12-31') DESC) As rn
    FROM Subscriptions
)
SELECT  product, 
        relevant_date
FROM CTE
WHERE rn = 1

请参阅Rextester上的实时演示。

Answer 2

如果第二个ABC行显示不正确的start_date，则此查询应起作用

SELECT S.product
     , relevant_date = MAX(ISNULL(S.end_date,S.start_date))
FROM dbo.Subscriptions S
GROUP BY S.product

Answer 3

这应该做到：

    select s1.product,MAX(case when useStartDate=1 then s1.startDate else s1.endDate end) 'SubscriptionDate'
    from @Subscriptions s1
    join (select s2s1.product, max(case when s2s1.endDate is null then 1 else 0 end) 'useStartDate' from @Subscriptions s2s1 group by s2s1.product) s2 on s1.product=s2.product
    group by s1.product