SELECT a.xmcdbm, a.gql, a.ysl, a.fwl, b.sq, b.ysp, b.ygbFROM ( SELECT xmcdbm, bgsj, sum( CASE gqbg WHEN '是' THEN 1 ELSE 0 END ) gql, sum( CASE ysbg WHEN '是' THEN 1 ELSE 0 END ) ysl, sum( CASE fwnrbg WHEN '是' THEN 1 ELSE 0 END ) fwl FROM ta_005_2 GROUP BY xmcdbm ) a, ( SELECT xmcdbm, bgsj, sum( CASE xmzt WHEN '申请' THEN 1 ELSE 0 END ) sq, sum( CASE xmzt WHEN '审批中' THEN '1' ELSE '0' END ) ysp, sum( CASE xmzt WHEN '已关闭' THEN '1' ELSE '0' END ) ygb FROM ta_005_2 WHERE gqbg = '是' GROUP BY xmcdbm ) b WHERE a.xmcdbm = b.xmcdbm