(select distinct 类别 from 表) a
left join
(select count(类别) as 总数,类别 from 表 WHERE 状态 = 1
GROUP BY 类别
) b
where a.类别=b.类别
ORDER BY a.类别
left join
(select count(类别) as 总数,类别 from 表 WHERE 状态 = 1
GROUP BY 类别
) b
where a.类别=b.类别
ORDER BY a.类别
select a.kind,isnull(总数,0) as sumcount from(
(select distinct kind from kindtab) a left join
(select count(kind) as 总数,kind from kindtab WHERE state = 1 GROUP BY kind) b
on a.kind=b.kind
)