New 2.txt 1.1 KB

12345678910111213141516171819202122232425262728293031
  1. set @yearAndMonth='2025';
  2. set @tenantId = '882990';
  3. SELECT
  4. xm.*, tmp.xm_stage,tmp.gov_bonus,
  5. IFNULL((SELECT COUNT(DISTINCT unicode) FROM kd_xm_technician xt where xt.xm_id = xm.xm_id GROUP BY xt.year_and_month ORDER BY xt.year_and_month desc limit 1),0) as technicianCount
  6. FROM
  7. (
  8. SELECT
  9. xm.id as xm_id,xm.tenant_id,
  10. COALESCE( (SELECT bgrz.BGHDZ FROM kd_xm_bgrz bgrz WHERE bgrz.XM_ID = xm.id AND bgrz.ZDMC = 'XMMC' AND DATE_FORMAT(bgrz.BGSXSJ,'%Y') <= @yearAndMonth ORDER BY bgrz.BGSXSJ DESC LIMIT 1),XMMC) AS XMMC,
  11. xm.XMLY,xm.XMKZXS as kzxs,xm.YQCGXS as cgxs,xm.XMJSJJMB as jsjjmb,
  12. xm.XMKSSJ as kssj,
  13. COALESCE( (SELECT bgrz.BGHDZ FROM kd_xm_bgrz bgrz WHERE bgrz.XM_ID = xm.id AND bgrz.ZDMC = 'XMJSSJ' AND DATE_FORMAT(bgrz.BGSXSJ,'%Y') <= @yearAndMonth ORDER BY bgrz.BGSXSJ DESC LIMIT 1),XMJSSJ) AS jssj
  14. FROM kd_xm xm
  15. ) xm
  16. LEFT JOIN kd_107_1 tmp on tmp.xm_id = xm.xm_id and tmp.year_and_month = @yearAndMonth
  17. WHERE xm.tenant_id = @tenantId and left(xm.jssj,4) >= @yearAndMonth