MySQL实用查询

1. 查询各个分段的总数

SELECT
	count( CASE WHEN rating_score BETWEEN 90 AND 100 THEN 1 END ) AS AAA,
	count( CASE WHEN rating_score BETWEEN 85 AND 89 THEN 1 END ) AS AA_jia,
	count( CASE WHEN rating_score BETWEEN 80 AND 84 THEN 1 END ) AS AA,
	count( CASE WHEN rating_score BETWEEN 75 AND 79 THEN 1 END ) AS AA_jian,
	count( CASE WHEN rating_score BETWEEN 70 AND 74 THEN 1 END ) AS A_jia,
	count( CASE WHEN rating_score BETWEEN 65 AND 69 THEN 1 END ) AS A,
	count( CASE WHEN rating_score BETWEEN 60 AND 64 THEN 1 END ) AS A_jian,
	count( CASE WHEN rating_score BETWEEN 55 AND 59 THEN 1 END ) AS BBB,
	count( CASE WHEN rating_score < 55 THEN 1 END ) AS BB_jixia,
	count( CASE WHEN rating_status = 0 THEN 1 END ) AS no_rating 
FROM
	rcd_rating;

2. 查询最近七天的总数

SELECT
	count(*) count 
FROM
	( SELECT * FROM rcd_rating WHERE DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( create_time )) as r;

3. 查询最近七天每天的总数（按照天数分组）

SELECT
	DATE_FORMAT( r.create_time, '%Y-%m-%d' ) days,
	count(*) count 
FROM
	(SELECT * FROM rcd_rating WHERE DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( create_time )) AS r
GROUP BY days;