mysql join：为每种类型获取包含count的部分

Question

I have two tables:

1) parts

part_id  |  part_name

2) posts

post_id | part_id | type | title | content

in posts table the post has one of three types, so in type column I put value (1 or 2 or 3)

I want to get table (with count and join) like this

part_id | part_name | count_posts_type1 | count_posts_type2 | count_posts_type3

how to do this with mysql?

example:

Answer 1

using sum with case when is a rather common way to do this if you have a limited list of types.

Then a left join to have result for parts without post (and a coalesce to have 0 instead of null on sums), and here we go.

select 
pa.part_id, pa.part_name,
coalesce(sum(case when po.type = 1 then 1 else 0 end), 0) as count_posts_type1,
coalesce(sum(case when po.type = 2 then 1 else 0 end), 0) as count_posts_type2,
coalesce(sum(case when po.type = 3 then 1 else 0 end), 0) as count_posts_type3
from parts pa
left join posts po on po.part_id = pa.part_id
group by pa.part_id, pa.part_name