SQL Server 2008 中的 Count (Distinct ([value)) OVER (Partition by) [英] Count (Distinct ([value)) OVER (Partition by) in SQL Server 2008

问题描述

我写了这个并在Oracle中成功执行

I have written this and successfully executed in Oracle

COUNT (DISTINCT APEC.COURSE_CODE) OVER (
                                            PARTITION BY s.REGISTRATION_NUMBER
                                            ,APEC.APE_ID
                                            ,COV.ACADEMIC_SESSION
                                            ) APE_COURSES_PER_ACADEMIC_YEAR

我正在尝试在 SQL Server 中实现相同的结果(我们的源数据库使用 Oracle，但我们的仓库使用 SQL Server).

I'm trying to achieve the same result in SQL Server (our source database uses Oracle but our warehouse uses SQL Server).

我知道 SQL Server 2008 中的窗口函数不支持 distinct - 任何人都可以提出替代方案吗?

I know the distinct isn't supported with window functions in SQL Server 2008 - can anyone suggest an alternative?

推荐答案

这是我最近遇到的.我从这个 post 得到它.到目前为止，它对我来说效果很好.

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

这篇关于SQL Server 2008 中的 Count (Distinct ([value)) OVER (Partition by)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！