with t as (select 物料号,物料类型 from 物料清单表
join 面料信息表 using(物料号)
where 设计师 = 1
group by 物料号,物料类型)

select 物料类型,count(物料号) as 面料数 from t
group by 物料类型
having 物料类型 = '面料' or 物料类型 = '辅料'
order by 面料数 desc