我正在尝试将查询从Oracle迁移到sql Server 2014.
这是我在Oracle中运行良好的查询：
select count(distinct A) over (partition by B) / count(*) over() as A_B from MyTable
这是我尝试在sql Server 2014中运行此查询后得到的错误.
Use of DISTINCT is not allowed with the OVER clause
解决方法
Anyone know what is the problem? Is such as kind of query possible in
sql Server?
不,目前尚未实施.请参阅以下连接项请求.
OVER clause enhancement request – DISTINCT clause for aggregate functions
另一种可能的变体是
SELECT M.A,M.B,T.A_B
FROM MyTable M
JOIN (SELECT CAST(COUNT(DISTINCT A) AS NUMERIC(18,8)) / SUM(COUNT(*)) OVER() AS A_B,B
FROM MyTable
GROUP BY B) T
ON EXISTS (SELECT M.B INTERSECT SELECT T.B)
对NUMERIC的强制转换是为了避免整数除法. join子句的原因是explained here.
如果优选,它可以用ON M.B = T.B OR(M.B IS NULL和T.B IS NULL)代替(或者如果B列不可为空则简单地ON ON M.B = T.B).
