现有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
......
====================================
C表(customer_id就是a表的id)
id customer_id other
1 1 xxxx
2 1 yyy
3 2 zzzzzz
=================================
D表(customer_id就是a表的id)
id customer_id other
1 1 xxxx
2 1 yyy
3 2 zzzzzz
4 2 wwww
=================================
现需要做联合查询,得出类似与下面的数据列
客户名称 JH HT FW C表统计数 D表统计数
a客户 1个 2个 1个 2个 2个
b客户 1个 0个 0个 1个 2个
=======================================
也就是说不单单是要统计客户的 project_type 数量,而且还要得出每个类型(已固定只有三种类型)的具体数量 ,以及C表和D表中的统计数,这个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
......
====================================
C表(customer_id就是a表的id)
id customer_id other
1 1 xxxx
2 1 yyy
3 2 zzzzzz
=================================
D表(customer_id就是a表的id)
id customer_id other
1 1 xxxx
2 1 yyy
3 2 zzzzzz
4 2 wwww
=================================
现需要做联合查询,得出类似与下面的数据列
客户名称 JH HT FW C表统计数 D表统计数
a客户 1个 2个 1个 2个 2个
b客户 1个 0个 0个 1个 2个
=======================================
也就是说不单单是要统计客户的 project_type 数量,而且还要得出每个类型(已固定只有三种类型)的具体数量 ,以及C表和D表中的统计数,这个sql应该怎么写??请大大门赐教啊~~~~~~~~~~~~~~~~~~~~~~~~
解决方案 »
- linux下mysql负载测试问题
- mysql登入时出错ERROR 2003:Can't connect to MySQL server on 'localhost'<10061>
- mysql blob 字段下载下来后都是40K
- mysql 在Windows下的数据文件目录中那里
- 4.95这样的数值应该用什么类型啊
- 请教一个奇怪的mysql问题,大家帮忙看看是不是bug
- mysql中更新语句如何实现增加一
- mysqlimport命令执行错误时,如何把错误信息写入到文件中
- 弱智问题,怎么把一个文件(内容是创建数据库的语句)导入mysql中?
- 设置外键问题
- mysql 外键 设置了级联删除和更新,还是不能插入
- 无法建立外键,请大哥们指点
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 合计,
(SELECT COUNT(*) FROM C WHERE a.id=customeR_ID) AS C表统计数,
(SELECT COUNT(*) FROM D WHERE a.id=customeR_ID) AS D表统计数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,
c1.cnt,
d1.cnt
from a inner join b on a.id=b.customer_id
left join (select customer_id,count(*) as cnt from C group by customer_id) c1 on a.id=c1.customer_id
left join (select customer_id,count(*) as cnt from D group by customer_id) d1 on a.id=d1.customer_id
group by a.c_name