现有2个表:
A表:
id c_name
1 a客户
2 b客户
3 c客户
====================
B表(customer_id就是a表的id):
id customer_id project_type
1 1 JH
2 1 HT
3 1 FW
4 1 HT
5 2 JH
......
====================================
现需要做联合查询,得出类似与下面的数据列
客户名称 JH HT FW 合计
a客户 1个 2个 1个 4个
b客户 1个 0个 0个 1个
=======================================
也就是说不单单是要统计客户的 project_type 数量,而且还要得出每个类型(已固定只有三种类型)的具体数量 ,这个sql应该怎么写??请大大门赐教啊~~~~~~~~~~~~~~~~~~~~~~~~
A表:
id c_name
1 a客户
2 b客户
3 c客户
====================
B表(customer_id就是a表的id):
id customer_id project_type
1 1 JH
2 1 HT
3 1 FW
4 1 HT
5 2 JH
......
====================================
现需要做联合查询,得出类似与下面的数据列
客户名称 JH HT FW 合计
a客户 1个 2个 1个 4个
b客户 1个 0个 0个 1个
=======================================
也就是说不单单是要统计客户的 project_type 数量,而且还要得出每个类型(已固定只有三种类型)的具体数量 ,这个sql应该怎么写??请大大门赐教啊~~~~~~~~~~~~~~~~~~~~~~~~
sum(if(project_type='JH',1,0)) AS JH,
sum(if(project_type='HT',1,0)) AS HT,
sum(if(project_type='FW',1,0)) AS FW,
COUNT(*) AS 合计
from a inner join b on a.id=b.customer_id
group by a.c_name
sum(if(project_type='JH',1,0)) AS JH,
sum(if(project_type='HT',1,0)) AS HT,
sum(if(project_type='FW',1,0)) AS FW,
COUNT(*) AS 合计
from a inner join b on a.id=b.customer_id
group by a.c_name
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html
中我的回答
sum(if(project_type='JH',1,0)) as JH,
sum(if(project_type='HT',1,0)) as HT,
sum(if(project_type='FW',1,0)) as FW,
count(*) as 合计
from a , b
where a.id=b.customer_id
group by a.c_name
MySQL交叉表
case when project_type='JH' then 1 else 0 end as JH,
case when project_type='HT' then 1 else 0 end as HT,
case when project_type='FW' then 1 else 0 end as FW,
count(*) as 合计
from a , b
where a.id=b.customer_id
group by a.c_name