select a.mid,a.name
,[type=0的记录数]=sum(case b.type when 0 then 1 else 0 end)
,[type=1的记录数]=sum(case b.type when 1 then 1 else 0 end)
,[type=2的记录数]=sum(case b.type when 2 then 1 else 0 end)
from table1 a join table2 b on a.mid=b.mid
group by a.mid,a.name
,[type=0的记录数]=sum(case b.type when 0 then 1 else 0 end)
,[type=1的记录数]=sum(case b.type when 1 then 1 else 0 end)
,[type=2的记录数]=sum(case b.type when 2 then 1 else 0 end)
from table1 a join table2 b on a.mid=b.mid
group by a.mid,a.name
isnull(sum(case when b.type=0 then 1 else 0 end),0) as [type=0的记录数],
isnull(sum(case when b.type=1 then 1 else 0 end),0) as [type=1的记录数],
isnull(sum(case when b.type=2 then 1 else 0 end),0) as [type=2的记录数]
from table1 a left join table2 b
on a.mid=b.mid
isnull(sum(case when b.type=0 then 1 else 0 end),0) as [type=0的记录数],
isnull(sum(case when b.type=1 then 1 else 0 end),0) as [type=1的记录数],
isnull(sum(case when b.type=2 then 1 else 0 end),0) as [type=2的记录数]
from table1 a left join table2 b
on a.mid=b.mid
group by a.mid,a.name