sql Group by 的使用

来源：百度知道 编辑：UC知道 时间：2024/04/28 04:58:28
现有表 a
字段 pro (省份) , isApp(1 愿意 2 不愿意 3 观望)
实现按省份统计,愿意的人数 ,不愿意的人数和观望的人数的一条sql语句.
IsApp 的值包括3种情况
1 愿意
2 不愿意
3 观望
然后分别统计出3种情况的人数,例如:
省份 | 总人数 | 愿意人数 | 不愿意人数 | 观望人数

广东 25 15 5 5

select t.pro,count(*) as 总人数,(select count(*) from test where t.pro = pro and isapp=1) as 愿意人数,
(select count(*) from test where t.pro = pro and isapp=2) as 不愿意人数,(select count(*) from test where t.pro = pro and isapp=3) as 观望人数
from test t
group by t.pro

select * from a order by pro,isApp;

select table1.pro,table2.总人数,table3.愿意人数,table4.观望人数 from
(select pro,count(*) as 总人数 from a group by pro) table1,
(select pro,count(*) as 愿意人数 from a where IsApp='1' group by pro) table2,
(select pro,count(*) as 不愿意人数 from a where IsApp='2' group by pro) table3,
(select pro,count(*) as 观望人数 from a where IsApp='3' group by pro) table4
where table1.pro=table2.pro and
table2.pro=table3.pro and
table3.pro=table4.pro
and table4.pro=table1.pro;

select pro,isApp,count(isApp) from a group by pro,isApp