mysql 分组查询（根据年龄范围间隔查询）

### 间隔查询，
SELECT elt(interval (TIMESTAMPDIFF(YEAR, b.birthday, now()),0,36, 46, 56, 66, 76,86),'35以下','36-45','46-55','56-65','66-75','76-85','86以上') AS ageRange, count(*) AS nums
    FROM doctor d, doctor_patient a, `user` b, patient c
    WHERE  b.birthday is not null  AND a.deleted = 0 AND b.deleted = 0 AND c.deleted = 0 AND d.deleted = 0
    <if test="doctorUserId != null and doctorUserId !=''">
      AND d.user_id = #{doctorUserId}
    </if>
    <if test="hospitalId != null and hospitalId != ''">
      AND d.hospital_id = #{hospitalId}
    </if>
      AND d.user_id = a.doctor_user_id AND a.patient_user_id = b.id AND a.patient_user_id = c.user_id AND c.disease = #{disease} group by ageRange ORDER BY ageRange ASC

### 年龄

image.png

### 年龄小于36，年龄介于36-45 介于46-55 介于56-65 介于66-75 大于86 

image.png

###不同年龄间隔起别名

image.png

### 根据ageRange 分组，

###升序排列

image.png

###查询结果如下

image.png

### 分组查询- 统计不同性别的用户数量
SELECT gender,COUNT(*) AS nums FROM doctor_patient a,`user`b,patient c WHERE a.doctor_user_id = 
'90a6e69239af45a1aab9e8756bcd3035' AND a.patient_user_id = b.id AND a.patient_user_id = c.user_id 
AND c.disease = 0 AND b.deleted = 0 AND c.deleted = 0 AND a.deleted = 3 GROUP BY gender;

### 分组查询-统计不同疾病的用户数量
SELECT disease,COUNT(*) AS nums FROM doctor_patient a,patient b WHERE a.doctor_user_id = 
'90a6e69239af45a1aab9e8756bcd3035' AND a.patient_user_id = b.user_id AND a.deleted = 0 AND b.deleted = 0 GROUP BY disease;