无法在Sql Server中运行分析函数 [英] Unable to run analytical function in Sql Server

问题描述

在Oracle中，它运行良好。查询oracle是否遵循

In Oracle it works well. Query for oracle is As Follows

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，我收到一个错误

But for SQL Server, I'm getting an error

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

I created table with columnname as TEAM_ID, LEVEL_ID, FF_ID, MODULE_ID, TERR_ID, MERGE_KEY then inserted values into the table ...

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

这是SQL查询...

This is the SQL query ...

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

上面的查询运行时给出ERROR：'distinct'附近的语法错误。

Above query when run give ERROR:Incorrect syntax near '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 

错误：'distinct'附近的语法不正确

ERROR:Incorrect syntax near 'distinct'

我尝试删除Cast零件，但同样的错误再次出现

i tried by removing Cast part as well but same error appear again n again

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

Can anybody help .......

推荐答案

在超过子句中的 count（distinct col）。你必须做一个子查询，通过的组用计数（distinct col）做你想做的事。

You can't use a count(distinct col) in an over clause. You'll have to do a subquery that does the count(distinct col) with an old-fashioned group by to do what you want to do.

这篇关于无法在Sql Server中运行分析函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！