SQL Server中员工婚姻性别占比输出整数而非浮点数的解决方法

问题出在整数除法上——你的表达式里100、COUNT(*)都是整数类型，数据库执行整数除法时会直接截断小数部分，只返回整数结果。要得到带小数的百分比，只需要让除法运算变成浮点除法，以下是几种可行的修改方式：

方法1：将整数常量改为浮点型 #

把100改成100.0，触发浮点运算逻辑：

SELECT
    MaritalStatus, Gender, 
    COUNT(*) AS "count",
    100.0 * COUNT(*) / (SELECT COUNT(*) FROM HumanResources.Employee) AS percentage
FROM
    HumanResources.Employee he
GROUP BY
    MaritalStatus,Gender

方法2：显式转换计数结果为浮点型 #

通过CAST把COUNT(*)转成浮点类型：

SELECT
    MaritalStatus, Gender, 
    COUNT(*) AS "count",
    100 * CAST(COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM HumanResources.Employee) AS percentage
FROM
    HumanResources.Employee he
GROUP BY
    MaritalStatus,Gender

方法3：保留两位小数（匹配目标结果格式） #

如果需要像16.89这样固定两位小数的结果，可以用以下两种方式：

用ROUND函数 #

SELECT
    MaritalStatus, Gender, 
    COUNT(*) AS "count",
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM HumanResources.Employee), 2) AS percentage
FROM
    HumanResources.Employee he
GROUP BY
    MaritalStatus,Gender

用CAST指定精度 #

SELECT
    MaritalStatus, Gender, 
    COUNT(*) AS "count",
    CAST(100.0 * COUNT(*) / (SELECT COUNT(*) FROM HumanResources.Employee) AS DECIMAL(5,2)) AS percentage
FROM
    HumanResources.Employee he
GROUP BY
    MaritalStatus,Gender

内容的提问来源于stack exchange，提问作者beridzeg45