count和distinct应该如何一起使用?

Question

在SQL Server中，我有一个数据库mydb，和一个表dbo。

我想看看如果表的三个列(Contract_Number, Payment_Number, Task_Number)可以形成一个候选键，通过https://stackoverflow.com/a/34468508/156458

SELECT count (DISTINCT [Contract_Number], [Payment_Number], [Task_Number])
FROM [mydb].[dbo].[mytable]

但是执行时出现错误:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

我想知道出了什么问题?

不计数，它可以正常工作。count和distinct应该如何一起使用?

我也试过:

SELECT count(*)
FROM (SELECT DISTINCT [Contract_Number], [Payment_Number], [Task_Number]
FROM [PAD_Dev].[dbo].[Line_Level_Custom])

但是执行时出现错误:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

谢谢。

Answer 1

你为什么不和Row_Number玩呢?

;with CTE as
(
SELECT row_number()over(partition  by [Contract_Number] order by [Contract_Number]) rownum
,[Contract_Number], [Payment_Number], [Task_Number])
FROM [mydb].[dbo].[mytable]
)
select * from CTE where rownum=1

ORCount function with partition

SELECT
COUNT(*) OVER (PARTITION BY [Contract_Number]) [Contract_Number],
[Payment_Number], [Task_Number]
FROM [mydb].[dbo].[mytable]

*查询未被测试