知道每个人的消费金额,想查出消费金额在0-10元,10-20元,20-50元以及50元以上区间的人数和总金额数
建表并插入数据
DROP TABLE
IF
EXISTS consumption;
CREATE TABLE consumption (
id INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
consumer VARCHAR ( 50 ) DEFAULT NULL COMMENT '消费者',
money DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '消费金额',
PRIMARY KEY ( id )
) DEFAULT CHARSET = utf8 COMMENT = '消费表';
INSERT INTO consumption ( consumer, money )
VALUES
( '小红', 5 ),( '小明', 56 ),( '小花', 500 ),( '小华', 23 ),( '小军', 45 ),( '小怜', 34 ),( '小菲', 16 );
查询语句
SELECT
(
CASE
WHEN total >= 0
AND total <= 10 THEN "0-10元" WHEN total > 10
AND total <= 20 THEN "10-20元" WHEN total > 20
AND total <= 50 THEN
"20-50元" ELSE "50元以上"
END
) AS '消费区间',
count( consumer ) '人数',
sum( total ) '消费金额'
FROM
( SELECT consumer, sum( money ) total FROM consumption GROUP BY consumer ) t1
GROUP BY
(
CASE
WHEN total >= 0
AND total <= 10 THEN "0-10元" WHEN total > 10
AND total <= 20 THEN "10-20元" WHEN total > 20
AND total <= 50 THEN
"20-50元" ELSE "50元以上"
END
)
ORDER BY total
查询结果: