;with cte as(
  select  x.a1 as 市 , n.a3 as 状态  from  新闻表 n inner join 用户表 u on n.a2=u.a1 
  inner join 市级表 x on x.a3=u.a2
 ),
 cte2 as(
  select  x.a1 as 县 , n.a3 as 状态  from  新闻表 n inner join 用户表 u on n.a2=u.a1 
  inner join 县级表 x on x.a4=u.a2
),
cte3 as(
select 
  单位=a2,
  发文总数=(select count(1) from cte where c.a1= 市),
  采纳=(select count(1) from cte where c.a1= 市 and 状态=1),
  c.a1 a1
  from 市级表 c
  union all
  select 
  单位=a2,
  发文总数=(select count(1) from cte2 where c.a1= 县),
  采纳=(select count(1) from cte2 where c.a1= 县 and 状态=1),
  c.a1  a1
  from 县级表 c
)
select * from cte3 order by  a1

------解决方案--------------------
楼主可参考

------解决方案--------------------
楼上的又让我学到了个新语法。。cte 表达式。。这个看起来蛮好用的。。哈哈。。感谢