SQL不同条件分类统计某个字段数值之和，以及所占百分比

最新推荐文章于 2025-03-28 20:22:11 发布

积极流年

最新推荐文章于 2025-03-28 20:22:11 发布

阅读量5.4k

点赞数 1

分类专栏： mysql 文章标签： mysql sql

本文链接：https://blog.youkuaiyun.com/lanlianhua_luffy/article/details/105282497

版权

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了如何使用SQL查询来实现按照不同条件分类统计某个字段的数值之和，并计算出各自所占的百分比。以表格T1为例，通过示例数据展示了如何获取FLAG为0和1时WEIGHT字段的总和及其百分比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

按照不同条件分类统计想求一条sql 语句，统计某个字段数值之和，以及所占百分比，比如有一张表T1，
PID FLAG WEIGHT
a 1 20
b 0 30
c 0 0
d 1 150
希望结果是
FLAG0 PER0 FLAG1 PER1
30 15% 85% 170

CREATE TABLE `test_cal_percent` (
  `PID` char(5) COLLATE utf8_bin NOT NULL,
  `FLAG` tinyint(3) NOT NULL,
  `WEIGHT` int(9) DEFAULT NULL,
  PRIMARY KEY (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `lls_test`.`test_cal_percent`(`PID`, `FLAG`, `WEIGHT`) VALUES ('a', 1, 20);
INSERT INTO `lls_test`.`test_cal_percent`(`PID`, `FLAG`, `WEIGHT`) VALUES ('b', 0, 30);
INSERT INTO `lls_test`.`test_cal_percent`(`PID`, `FLAG`, `WEIGHT`) VALUES ('c', 0, 0);
INSERT INTO `lls_test`.`test_cal_percent`(`PID`, `FLAG`, `WEIGHT`) VALUES ('d', 1, 150);

一、直接统计

SELECT
	sum( CASE WHEN flag = '0' THEN weight ELSE 0 END ) AS FLAG0,
	sum( CASE WHEN flag = '0' THEN weight ELSE 0 END ) / sum( weight ) AS PER0,
	sum( CASE WHEN flag = '1' THEN weight ELSE 0 END ) AS FLAG1,
	sum( CASE WHEN flag = '1' THEN weight ELSE 0 END ) / sum( weight ) AS PER1 
FROM
	test_cal_percent;

二、使用with 子查询

with t2 as (select flag, sum(weight) sum from test_cal_percent group by flag)
SELECT
	sum( CASE WHEN flag = 0 THEN sum END ) flag0,
	sum( CASE WHEN flag = 0 THEN sum END ) / ( SELECT sum( sum ) FROM t2 ) per0,
	sum( CASE WHEN flag = 1 THEN sum END ) flag1,
	sum( CASE WHEN flag = 1 THEN sum END ) / ( SELECT sum( sum ) FROM t2 ) per1 
FROM
	t2