sql获取一个表的字段，同时对这个字段在另一个表中重复值进行计数

具体表述为A表的字段id在B表的字段field_id中有重复，提取id字段和计数值字段

方法1：

select A.id, count(B.field_id) num from A
left join B on B.field_id=A.id
group by A.id,B.code

方法2：

select a.id,b.code,IFNULL(b.num,0) 
as num from A as a
left join (select count(*) as num,field_id from B group by field_id) as b on a.id=b.field_id

不使用join完成这个功能(更简单）

select A.id,(select count(B.field_id) from B where A.id=B.field_id) num from A