MySQL分组查询（统计为空的分组默认为0）

心已赠人 2022-10-09 03:13 583阅读 0赞

**目录**

0.知识点:MySQL CASE函数

1.简单的 CASE 语句

2.可搜索的 CASE 语句

--------------------

### 0.知识点:MySQL CASE函数 ###

> 除了IF函数,MySQL还提供了一个替代的条件语句 CASE。 MySQL CASE 语句使代码更加可读和高效。CASE 语句有两种形式：简单的和可搜索 CASE 语句。

### 1.简单的 CASE 语句 ###

**语法**

CASE  <表达式>
       WHEN <值1> THEN <操作>
       WHEN <值2> THEN <操作>
       ...
       ELSE <操作>
    END CASE;

**小栗子：**

SELECT
    	CASE WEEKDAY(NOW())
    WHEN 0 THEN
    	'星期一'
    WHEN 1 THEN
    	'星期二'
    WHEN 2 THEN
    	'星期三'
    WHEN 3 THEN
    	'星期四'
    WHEN 4 THEN
    	'星期五'
    WHEN 5 THEN
    	'星期六'
    ELSE
    	'星期天'
    END AS COLUMN1,
     NOW(),
     WEEKDAY(NOW()),
     DAYNAME(NOW());

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4MjQzNjk4_size_16_color_FFFFFF_t_70][]

### 2.可搜索的 CASE 语句 ###

**语法**

CASE
        WHEN <条件1> THEN <命令>
        WHEN <条件2> THEN <命令>
        ...
        ELSE commands
    END CASE;

**实例**

> 现有一个用户表user，表字段id，age,name。需求：分年龄段进行查询，并且年龄段为空时默认统计为0。

SELECT
    	temp.ageSegment,
    	temp.countAge 
    FROM
    	(
    			SELECT
    					CASE WHEN u.age > 0 AND u.age <= 10 THEN '0-10岁'
    							 WHEN u.age > 10 AND u.age <= 20 THEN '10-20岁'
    							 WHEN u.age > 20 AND u.age <= 30 THEN '20-30岁'
    							 WHEN u.age > 30 AND u.age <= 40 THEN '30-40岁'
    					ELSE
    							'40岁以上'
    					END AS ageSegment,COUNT(*) countAge
    			FROM  user u
    			GROUP BY ageSegment
    			UNION SELECT '0-10岁' as ageSegment,'0' as countAge
    			UNION SELECT '10-20岁' as ageSegment,'0' as countAge
    			UNION SELECT '20-30岁' as ageSegment,'0' as countAge
    			UNION SELECT '30-40岁' as ageSegment,'0' as countAge
    			UNION SELECT '40岁以上' as ageSegment,'0' as countAge) as temp
    GROUP BY ageSegment

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4MjQzNjk4_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4MjQzNjk4_size_16_color_FFFFFF_t_70]: /images/20221005/1693127f144141828d79ae046fdbe2dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N4MjQzNjk4_size_16_color_FFFFFF_t_70 1]: /images/20221005/304ff3eb4b964dcaa80fa75b1179f234.png