| 12345678910111213141516171819202122232425262728293031 |
- set @yearAndMonth='2025';
- set @tenantId = '882990';
- SELECT
- xm.*, tmp.xm_stage,tmp.gov_bonus,
- 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
- FROM
- (
- SELECT
- xm.id as xm_id,xm.tenant_id,
- 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,
- xm.XMLY,xm.XMKZXS as kzxs,xm.YQCGXS as cgxs,xm.XMJSJJMB as jsjjmb,
- xm.XMKSSJ as kssj,
- 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
- FROM kd_xm xm
- ) xm
- LEFT JOIN kd_107_1 tmp on tmp.xm_id = xm.xm_id and tmp.year_and_month = @yearAndMonth
- WHERE xm.tenant_id = @tenantId and left(xm.jssj,4) >= @yearAndMonth
-
-
-
-
-
-
|