select x.name, y.c from (select distinct name from table ) x, left join ( select name, count(*) as c from table where id = 2 group by name ) y on x.name=y.name
公布答案SELECT a.name, count( DISTINCT ( concat( b.id, b.name ) ) ) FROM test AS a LEFT JOIN test AS b ON a.id = b.id AND a.name = b.name AND b.id =2 GROUP BY a.name其实还有一个唯一字段v 所以上面还可以简化为SELECT a.name, count( b.v ) FROM test AS a LEFT JOIN test AS b ON a.v = b.v AND b.id =2 GROUP BY a.name
select x.name, y.c
from (select distinct name from table ) x,
left join ( select name, count(*) as c from table where id = 2 group by name ) y
on x.name=y.name
concat( b.id, b.name ) )
)
FROM test AS a
LEFT JOIN test AS b ON a.id = b.id
AND a.name = b.name
AND b.id =2
GROUP BY a.name其实还有一个唯一字段v
所以上面还可以简化为SELECT a.name, count( b.v )
FROM test AS a
LEFT JOIN test AS b ON a.v = b.v
AND b.id =2
GROUP BY a.name