无法在Sql Server中运行分析功能

Question

在Oracle中运行良好。对于Oracle查询如下

SELECT TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID, TERR_ID, MERGE_KEY, count(distinct TERR_ID) over (partition by TEAM_ID,LEVEL_ID,FF_ID,MODULE_ID)||' of '||count(distinct TERR_ID) over (partition by TEAM_ID,LEVEL_ID,FF_ID) as MISMATCH_TERR_COUNT_IN_FF FROM SCN7BBFE80210E04E2F88653A.PA83FB9BD57E044618B7AC86A; 

但对于SQL Server中，我得到一个错误

我创建表列名作为TEAM_ID，LEVEL_ID，FF_ID，MODULE_ID，TERR_ID，MERGE_KEY然后插入值代入表...

insert into PA83FB9BD57E044618B7AC86A values('3',1,'YH','PERF','3XBDA3',1) 
insert into PA83FB9BD57E044618B7AC86A values('3',1,'YH','PERF','3XAJA3',1) 

这是SQL查询...

SELECT TEAM_ID , 
     LEVEL_ID , 
     FF_ID , 
     MODULE_ID , 
     TERR_ID , 
     MERGE_KEY , 
     ISNULL(CAST(COUNT(DISTINCT TERR_ID) OVER (PARTITION BY TEAM_ID, 
           LEVEL_ID, FF_ID, MODULE_ID) AS NVARCHAR(MAX)), 
       '') + ' of ' 
     + ISNULL(CAST(COUNT(DISTINCT TERR_ID) OVER (PARTITION BY TEAM_ID, 
           LEVEL_ID, FF_ID) AS NVARCHAR(MAX)), 
       '') AS MISMATCH_TERR_COUNT_IN_FF 
FROM dbo.PA83FB9BD57E044618B7AC86A 

以上阙ry运行时出现错误：'distinct'附近的语法不正确。

使查询小我试图

SELECT TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID, TERR_ID, MERGE_KEY, cast(count(distinct TERR_ID) OVER(PARTITION BY TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID) AS MISMATCH_TERR_COUNT_IN_FF FROM dbo.PA83FB9BD57E044618B7AC86A 

错误：附近有语法错误“独立的”

我试图通过消除铸件很好，但同样的错误再次出现ň再次

任何人可以帮助.......

Answer 1

您不能在over子句中使用count(distinct col)。你必须做一个子查询，count(distinct col)与老式的group by做你想做的事情。