select sum(a) as A,
B1=SUM(CASE B when '长期' then 1 else 0 end),
B2=SUM(CASE B when '短期' then 1 else 0 end),
C
from t
group by C
B1=SUM(CASE B when '长期' then 1 else 0 end),
B2=SUM(CASE B when '短期' then 1 else 0 end),
C
from t
group by C
from ( select sum(a) as A,(case b when '长期' then count(b) else 0 end )as B1 ,(case b when '长期' then 0 else count(b) end )as B2 ,c
from table1
group by c,b)a
group by c