通过SQL对分区进行计数 [英] Count Distinct over partition by sql

问题描述

我有一个像这样的表

col1ID  col2String Col3ID Col4String Col5Data
  1        xxx       20      abc     14-09-2018
  1        xxx       20      xyz     14-09-2018
  2        xxx       30      abc     14-09-2018
  2        xxx       30      abc     14-09-2018 

我想添加一列，该列按col1ID和col3ID计算col4String组中我有多少个不同的字符串。

I would like to add column which count how many different strings I have in col4String group by col1ID and col3ID.

所以类似

COUNT(DISTINCT (Col4String)) over (partition by col1ID, col3ID)

但是它不起作用，我收到一个错误消息

but it doesn't work, I receive an error

OVER子句不允许使用DISTINCT。

Msg 102，第15级，状态1，第23行。

Use of DISTINCT is not allowed with the OVER clause.
Msg 102, Level 15, State 1, Line 23.

我有更多列，例如col2String，col5Data但它们不应该受到影响，所以我不能在 SELECT 和 dense_rank（）

I have more columns like col2String, col5Data but they shouldn´t be affected, so I can't use distinct at the beginning of SELECT, and dense_rank() also doen´t seems to work in my case.

谢谢您的帮助。

推荐答案

尝试一下：

DECLARE @DataSource TABLE
(
    [col1ID] INT
   ,[col2String] VARCHAR(12) 
   ,[Col3ID]  INT
   ,[Col4String]  VARCHAR(12)
   ,[Col5Data] DATE
);

INSERT INTO @DataSource
VALUES (1, 'xxx', 20, 'abc', '2018-09-14')
      ,(1, 'xxx', 20, 'xyz', '2018-09-14')
      ,(2, 'xxx', 30, 'abc', '2018-09-14')
      ,(2, 'xxx', 30, 'abc', '2018-09-14');

SELECT *
     ,dense_rank() over (partition by col1ID, col3ID order by [Col4String])  + dense_rank() over (partition by col1ID, col3ID order by [Col4String] desc) - 1
FROM @DataSource

这篇关于通过SQL对分区进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！