group by 详解
select name from test group by name -- ok
select * from test group by name --error
select name,sum(number) from test group by name -- ok
统计查询查询效率
- 使用GROUP BY 【70ms】
- 使用嵌套的SELECT 【100~110ms】
- 使用CASE WHEN: 【60ms】
--总行数 130721:连连71255、支付宝59396、手工70
--groupBy
SELECT payment_type AS '还款方式',COUNT(payment_type) AS '数量' FROM receive GROUP BY payment_type;
--嵌套子查询
SELECT (SELECT COUNT(*) FROM `receive` WHERE `payment_type` =1) AS `连连`,
(SELECT COUNT(*) FROM `receive` WHERE `payment_type` =2) AS `支付宝`,
(SELECT COUNT(*) FROM `receive` WHERE `payment_type` =3) AS `手工`
FROM receive WHERE ;
--case when
SELECT
COUNT( CASE WHEN `payment_type` =1 THEN 1 ELSE NULL END ) AS `连连`,
COUNT( CASE WHEN `payment_type` =2 THEN 1 ELSE NULL END ) AS `支付宝`,
COUNT( CASE WHEN `payment_type` =3 THEN 1 ELSE NULL END ) AS `手工`
FROM `receive`