有三个表疾病表 t_dis
-------------------------------
dis_id 疾病Id
dis_name 疾病名称用户表 t_user
-------------------------------
user_id 用户Id
user_name 姓名
user_sex 性别
user_age 年龄用户疾病对应表 t_dis_user
-------------------------------
dis_id 疾病Id
user_id 用户Id如何查询出以下结果(按性别,年龄段查出每种疾病的患病人数)
------------------------------------------------------
疾病名称 20-30岁男 20-30岁女 30-40岁男 30-40岁女
------------------------------------------------------
疾病1 1 2 1 0
------------------------------------------------------
疾病2 1 1 2 1
------------------------------------------------------
疾病3 0 1 1 0
------------------------------------------------------
......
------------------------------------------------------
疾病N
------------------------------------------------------
-------------------------------
dis_id 疾病Id
dis_name 疾病名称用户表 t_user
-------------------------------
user_id 用户Id
user_name 姓名
user_sex 性别
user_age 年龄用户疾病对应表 t_dis_user
-------------------------------
dis_id 疾病Id
user_id 用户Id如何查询出以下结果(按性别,年龄段查出每种疾病的患病人数)
------------------------------------------------------
疾病名称 20-30岁男 20-30岁女 30-40岁男 30-40岁女
------------------------------------------------------
疾病1 1 2 1 0
------------------------------------------------------
疾病2 1 1 2 1
------------------------------------------------------
疾病3 0 1 1 0
------------------------------------------------------
......
------------------------------------------------------
疾病N
------------------------------------------------------
[20-30岁男]=SUM(case when user_age between 20 and 30 and user_sex='男'then 1 Else 0 end ),
[20-30岁女]=SUM(case when user_age between 20 and 30 and user_sex='女'then 1 Else 0 end ),
[30-40岁男]=SUM(case when user_age > 30 and user_age<= 40 and user_sex='男'then 1 Else 0 end ),
[30-40岁女]=SUM(case when user_age > 30 and user_age<= 40 and user_sex='女'then 1 Else 0 end )
from t_dis a,t_user b,t_dis_user c
where a.dis_id=c.dis_id and c.user_id=b.user_id
case when c.user_age between 20 and 30 and c.user_sex = '女' then 1 else 0 end),
from t_dis a
left join t_dis_user b on a.dis_id = b.dis_id
left join t_user c on b.user_id = c.user_id
group by a.dis_name
select 疾病名称,
[20-30岁男]=sum(case when user_sex='男' and user_age>20 and user_age<30 then 1 else 0 end),
[20-30岁女]=sum(case when user_sex='女' and user_age>20 and user_age<30 then 1 else 0 end),
[30-40岁男]=sum(case when user_sex='男' and user_age>30 and user_age<40 then 1 else 0 end),
[30-40岁女]=sum(case when user_sex='女' and user_age>30 and user_age<40 then 1 else 0 end)
from t_dis A join t_dis_user B on A.sid_id=B.dis_id
join t_user C on C.user_id=B.user_id
group by 疾病名称
[20-30岁男]=SUM(case when user_age between 20 and 30 and user_sex='男'then 1 Else 0 end ),
[20-30岁女]=SUM(case when user_age between 20 and 30 and user_sex='女'then 1 Else 0 end ),
[30-40岁男]=SUM(case when user_age > 30 and user_age<= 40 and user_sex='男'then 1 Else 0 end ),
[30-40岁女]=SUM(case when user_age > 30 and user_age<= 40 and user_sex='女'then 1 Else 0 end )
from t_dis a,t_user b,t_dis_user c
where a.dis_id=c.dis_id and c.user_id=b.user_id
group by 疾病名称
select a.dis_name,sum(case when c.user_age between 20 and 30 and c.user_sex = '男' then 1 else 0 end),
sum(case when c.user_age between 20 and 30 and c.user_sex = '女' then 1 else 0 end),
from t_dis a
left join t_dis_user b on a.dis_id = b.dis_id
left join t_user c on b.user_id = c.user_id
group by a.dis_name