如何在SQL Server中避免多个组

The table contains below type records, want to get the total-client on specific product year

该表包含以下类型记录，希望获得特定产品年份的总客户

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

Table content:

表内容：

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

Suppose:

假设：

• product=4 has total client=1 in year=2016
• product = 4总客户= 1年= 2016年
• product=4 has total client=1 in year=2018
• product = 4总客户= 1年= 2018年
• product=4 has total client=17 in year=2017
• product = 4的总客户数= 17年= 2017年

This SQL query returns the desired result, but it needs to group multiple times. Is there any another way to avoid multiple groups?

此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

Query output is:

查询输出是：

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

1 个解决方案

#1

If I'm understanding your question correctly, it looks like you just want to count the number of clients for each year for each product. If so, then this should be all you need:

如果我正确理解您的问题，您似乎只想计算每个产品每年的客户数量。如果是这样，那么这应该是您所需要的：

select year(contentdate), productid, count(clientid)
from contectTable
group by year(contentdate), productid