SQL中以另外一张表的字段去统计本表的分组

第一种方法：

SELECT d.label AS name,d.value,
(SELECT count(1) FROM project.s_major_project p
  WHERE p.type = d.value) AS count
FROM westlake.z_dict d
where type='projectType'

第二种方法：　　

select name,value , if(aa.id != '',count(aa.name),0) from ( SELECT d.label AS name ,d.value ,p.id as id
FROM westlake.z_dict d
left join project.s_major_project p
on d.value = p.type where d.type='projectType' ) aa
group by name order by value

第三种方法：

SELECT d.label AS name,d.value,IFNULL(c.counts,0) AS count
FROM westlake.z_dict d LEFT JOIN
(
SELECT p.type,count(*) counts FROM project.s_major_project p GROUP BY p.type
) c
ON c.type=d.value where d.type='projectType'

第四种方法：

SELECT d.label AS name,d.value,SUM(CASE WHEN p.type=d.value THEN 1 else 0 END) AS count
FROM westlake.z_dict d LEFT JOIN project.s_major_project p
ON p.type = d.value WHERE d.type='projectType' GROUP BY d.value;