sql-server - 带有 Over() 的 SQL 轮数

Question

我有一个非常基本的 sql 查询查询，它正在计算从分组结果集中返回的记录总数。

 SELECT   vco.OutcomeName,
          vco.VersionCompareOutcomeID,
          COUNT(t.OutcomeName) AS Total,
          ROUND(COUNT(*) * 100.0 / sum(count(*)), 1) over() AS Percentage
FROM map.VersionCompareOutcome AS vco
LEFT JOIN @temp AS t
ON vco.VersionCompareOutcomeID = t.VersionCompareOutcomeID
GROUP BY vco.OutcomeName, vco.VersionCompareOutcomeID

当我尝试使用 round 函数时，出现以下错误:The function 'ROUND' is not a valid windowing function, and cannot be used with the OVER clause.

没有 round 函数，我得到了百分比，但它不像我试图实现的那样四舍五入。

我想要的结果是小数点后两位:例如 87.95%。

Answer 1

您不能将 ROUND 与 OVER 一起使用, 只有聚合、排名和分析函数可以与 over 一起使用。

但是，您可以在子查询或 CTE 中进行聚合，然后在外部查询中进行舍入:

WITH CTE
AS
(

    SELECT   vco.OutcomeName,
              vco.VersionCompareOutcomeID,
              COUNT(t.OutcomeName) AS Total
    FROM map.VersionCompareOutcome AS vco
    LEFT JOIN @temp AS t ON vco.VersionCompareOutcomeID = t.VersionCompareOutcomeID
    GROUP BY vco.OutcomeName, vco.VersionCompareOutcomeID
)
SELECT
  OutcomeName,
  VersionCompareOutcomeID,
  Total,
  CASE WHEN (SELECT COUNT(*) FROM @temp) = 0 THEN 0 ELSE ROUND(Total * 100.0 / (SELECT COUNT(*) FROM @temp), 1) END AS Percentage
FROM CTE