无法在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屋!