数据库分组查询sql

白送你

于 2023-04-17 11:42:08 发布

阅读量322

点赞数

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/song_nan92/article/details/130196658

版权

数据库分层查询sql

将连续的数据，进行分组查询汇总；

数据结构

建表：

drop table if exists testdb.test123;
CREATE TABLE testdb.test123
(
"id" INT,
"num" INT
);

插入数据：

insert into test123 ("id","num") values (0, 447289);
insert into test123 ("id","num") values (1, 35924);
insert into test123 ("id","num") values (2, 9458);
insert into test123 ("id","num") values (3, 6097);
insert into test123 ("id","num") values (4, 3255);
insert into test123 ("id","num") values (5, 1356);
insert into test123 ("id","num") values (6, 1106);
insert into test123 ("id","num") values (7, 535);
insert into test123 ("id","num") values (8, 178);
insert into test123 ("id","num") values (9, 135);
insert into test123 ("id","num") values (10, 79);
insert into test123 ("id","num") values (11, 281);
insert into test123 ("id","num") values (12, 89);
insert into test123 ("id","num") values (13, 91);

分组SQL：

SELECT		
	CASE 
	WHEN id<=1              THEN '0-1' 
	WHEN id<=3 AND id>1  	THEN '2-3' 
	WHEN id<=5 AND id>3  	THEN '4-5' 
	WHEN id<=7 AND id>5  	THEN '6-7'
	ELSE '其他' 	END AS num_range,
	SUM(num) AS all_num
FROM
	test123
GROUP BY 
	CASE 
	WHEN id<=1              THEN '0-1' 
	WHEN id<=3 AND id>1  	THEN '2-3' 
	WHEN id<=5 AND id>3  	THEN '4-5' 
	WHEN id<=7 AND id>5  	THEN '6-7'
	ELSE '其他' END

结果：