SQL Server 2008 中的 Count (Distinct ([value)) OVER (Partition by)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27566568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count (Distinct ([value)) OVER (Partition by) in SQL Server 2008
提问by james.mullan
I have written this and successfully executed in Oracle
我写了这个并在Oracle中成功执行
COUNT (DISTINCT APEC.COURSE_CODE) OVER (
PARTITION BY s.REGISTRATION_NUMBER
,APEC.APE_ID
,COV.ACADEMIC_SESSION
) APE_COURSES_PER_ACADEMIC_YEAR
I'm trying to achieve the same result in SQL Server (our source database uses Oracle but our warehouse uses SQL Server).
我试图在 SQL Server 中实现相同的结果（我们的源数据库使用 Oracle，但我们的仓库使用 SQL Server）。
I know the distinct isn't supported with window functions in SQL Server 2008 - can anyone suggest an alternative?
我知道 SQL Server 2008 中的窗口函数不支持 distinct - 任何人都可以提出替代方案吗？
回答by Gordon Linoff
Alas, you cannot do count(distinct) overin SQL Server. You can do this with a subquery. The idea is to enumerate the values within each course code (and subject to the other partitioning conditions). Then, just count up the values where the sequence number is 1:
唉，你不能count(distinct) over在 SQL Server 中做。您可以使用子查询执行此操作。这个想法是枚举每个课程代码中的值（并受其他分区条件的约束）。然后，只需计算序列号为 1 的值：
select sum(case when cc_seqnum = 1 then 1 else 0 end) as APE_COURSES_PER_ACADEMIC_YEAR
from (select . . . ,
row_number () OVER (PARTITION BY s.REGISTRATION_NUMBER, APEC.APE_ID,
COV.ACADEMIC_SESSION,
APEC.COURSE_CODE
ORDER BY (SELECT NULL)
) as cc_seqnum
from . . .
) t
You have a complex query. I would suggest that you replace the count(distinct)with the row_number()and make your current query a subquery or CTE for the final query.
您有一个复杂的查询。我会建议你更换count(distinct)用row_number()，使您的当前查询的最终查询子查询或CTE。
回答by JoeFletch
Here's what I recently came across. I got it from this post. So far it works really well for me.
这是我最近遇到的。我从这篇文章中得到了它。到目前为止，它对我来说非常有效。
DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields ASC) +
DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields DESC) - 1 AS DistinctCount
