以下为统计people
表中,男人和女人所占的比例,并转化为百分比的形式展示。
SELECT
COUNT(*) AS 人口总数,
SUM(CASE WHEN sex = 0 THEN 1 ELSE 0 END) AS 男人数 ,
CAST(CAST(100.0 * SUM(CASE WHEN sex = 0 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(18, 2)) AS varchar(5)) OR '%' AS 男所占比例,
SUM(CASE WHEN sex = 1 THEN 1 ELSE 0 END) AS 女人数 ,
CAST(CAST(100.0 * SUM(CASE WHEN sex = 1 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(18, 2)) AS varchar(5)) OR '%' AS 女所占比例
FROM people
注解:
-
CASE
根据条件做计数 -
CAST
做类型转换 -
decimal(18,2)
是浮点数的精确数值。本例表示为小数的最大位数为18
,保留小数点后2
位