mySQL常用查询语句

场景：查询每月，同一种物料的合格数

SELECT aa.source_name as name,aa.operate_date as months,SUM(aa.batch_num) as monthTotal,aa.results
FROM (
select (case a.source_name when '热塑POE' then 'POE' when 'POE' then 'POE' when 'EVA' then 'EVA' else '其他' end) as source_name,
  b.material_name,b.material_code,DATE_FORMAT(c.operate_date, '%m') AS operate_date,
  c.status,(case c.final_result when '0' then '合格' when '1' then '不合格' else '其他' end) results,d.batch_num
from mdm_material_attribute a 
  join mdm_material b on b.source_name_id = a.id and b.flag_deleted=0
  join qms_incoming_inspect c on c.material_code = b.material_code and c.flag_deleted=0
  left join qms_incoming_material d on d.serial_code = c.serial_code and d.flag_deleted=0
where (a.id = '4104584' or a.id in ('4104587','4104589'))
and a.flag_deleted=0
and c.status = 2
and YEAR(c.operate_date) = :year
) AS aa
GROUP BY name,months,aa.results

场景：本月每周

select source_name as name,weeks,results,sum(batch_num) as weekTotal from
(select (case a.source_name when '热塑POE' then 'POE' when 'POE' then 'POE' when 'EVA' then 'EVA' else '其他' end) as source_name,
  b.material_name,b.material_code,WEEK(c.operate_date, 1) -  WEEK(DATE_SUB(c.operate_date, INTERVAL DAYOFMONTH(c.operate_date) - 1 DAY),1)as weeks,
  DATE_FORMAT(c.operate_date, '%u') AS year_week,DATE_FORMAT(c.operate_date, '%Y-%m') AS mm,c.operate_date,
  c.status,(case c.final_result when '0' then '合格' when '1' then '不合格' else '其他' end) results,d.batch_num
from mdm_material_attribute a 
  join mdm_material b on b.source_name_id = a.id and b.flag_deleted=0
  join qms_incoming_inspect c on c.material_code = b.material_code and c.flag_deleted=0
  left join qms_incoming_material d on d.serial_code = c.serial_code and d.flag_deleted=0
where (a.id = '4104584' or a.id in ('4104587','4104589'))
and a.flag_deleted=0
and c.status = 2
and DATE_FORMAT(c.operate_date, '%Y-%m') = :month
and YEAR(c.operate_date) = :year
  ) as aa
group by weeks,name,results