sql - Count Distinct over partition by sql

Question

I have a table like

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 

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

So something like

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

but it doesn't work, I receive an error

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

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.

Thank You for help.

标签： sqlsql-servertsqlcountdistinct

Answer 1

尝试这个：

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