select t1.学科号,t1.学科名, sum(case when t2.奖种号 = 1 then 1 else 0 end), sum(case when t2.奖种号 = 2 then 1 else 0 end), sum(case when t2.奖种号 = 3 then 1 else 0 end) from t_xm t1, t_xk t2 where t1.学科号 = t2.学科号 group by t1.学科号, t1.t1.学科名;
t1.t1.学科名 -> t1.学科名
select a.学科号, count(case when b.奖种号=1 then 1 end)奖种1项目数量, count(case when b.奖种号=2 then 1 end)奖种2项目数量, count(case when b.奖种号=3 then 1 end)奖种3项目数量 from 学科表 a left join 项目表 b on a.学科号=b.学科号 group by a.学科号
sum(case when t2.奖种号 = 2 then 1 else 0 end),
sum(case when t2.奖种号 = 3 then 1 else 0 end)
from t_xm t1, t_xk t2
where t1.学科号 = t2.学科号
group by t1.学科号, t1.t1.学科名;
count(case when b.奖种号=1 then 1 end)奖种1项目数量,
count(case when b.奖种号=2 then 1 end)奖种2项目数量,
count(case when b.奖种号=3 then 1 end)奖种3项目数量
from
学科表 a left join 项目表 b
on a.学科号=b.学科号
group by a.学科号