如何在SQL Server中避免多个Group By

该表包含以下类型记录,要获取 total-client 特定产品年份

CREATE TABLE [dbo].[contectTable]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ContentDate] [datetime] NULL,
    [ProductId] [int] NULL,
    [ClientId] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[contectTable] 
    ADD CONSTRAINT [DF_contectTable_ContentDate] 
        DEFAULT (getdate()) FOR [ContentDate]
GO

表内容:

Id  ContentDate            ProductId    ClientId
------------------------------------------------
1   2018-06-23 00:24:58.870        1    1
2   2018-06-23 00:25:14.593        1    1
3   2018-06-23 00:25:21.460        2    3
4   2018-06-23 00:25:28.730        3    8
5   2018-06-23 00:25:36.813        4    1
6   2017-06-23 00:32:42.883        4    2
7   2016-06-23 00:32:51.680        4    3
8   2017-06-23 00:32:59.917        4    4

假设:

• 产品=4的总客户数=1年=2016年
• 产品=4,总客户=1年=2018年
• 产品=4年总客户=17年=2017年

此SQL查询返回所需的结果,但需要多次分组。有没有其他方法可以避免多个组?

SELECT
    y.*
FROM 
    (SELECT
         x.ContentDate,
         x.ProductId,
         SUM(x.countClientId) 'TotalClient'
     FROM 
         (SELECT
              YEAR(ContentDate) ContentDate,
              ProductId,
              ClientId,
              COUNT(ClientId) countClientId
          FROM 
              contectTable
          GROUP BY 
              YEAR(ContentDate), ProductId, ClientId
          HAVING 
              COUNT(ClientId) = 1) x
    GROUP BY 
        x.ContentDate, x.ProductId

    UNION

    SELECT
        x.ContentDate,
        x.ProductId,
        SUM(x.countClientId) 'TotalClient'
    FROM 
        (SELECT
             YEAR(ContentDate) ContentDate,
             ProductId,
             ClientId,
             COUNT(ClientId) countClientId
         FROM 
             contectTable
         GROUP BY 
             YEAR(ContentDate), ProductId, ClientId
         HAVING 
             COUNT(ClientId) > 1) x
    GROUP BY 
        x.ContentDate, x.ProductId) y

查询输出为:

ContentDate ProductId   TotalClient
-----------------------------------
2016                4   1
2017                4   2
2018                1   2
2018                2   1
2018                3   1
2018                4   1