更新表与多个表中的数据和按月分组 -

Question

我是初学者，需要一些帮助。试图更新多个表中的数据表，并在数月或数周

update monthly_report 
SET De_Ative = (SELECT IFNULL(sum(statuschange.NUM_STATUS_CHANGES),0) FROM statuschange 
WHERE month(STR_TO_DATE(statuschange.Evaluation_day,'%d.%m.%Y')) = monthly_report.Mon 
AND statuschange.STATUS_CHANGE ='CANCEL' OR statuschange.STATUS_CHANGE ='LOCK' 
group by month(STR_TO_DATE(statuschange.Evaluation_day,'%d.%m.%Y'))) 

[SQL] update monthly_report 
SET De_Ative = (SELECT IFNULL(sum(statuschange.NUM_STATUS_CHANGES),0) FROM statuschange 
WHERE month(STR_TO_DATE(statuschange.Evaluation_day,'%d.%m.%Y')) = monthly_report.Mon 
AND statuschange.STATUS_CHANGE ='CANCEL' OR statuschange.STATUS_CHANGE ='LOCK' 
group by month(STR_TO_DATE(statuschange.Evaluation_day,'%d.%m.%Y'))) 

-- group by monthly_report.Mon 

[Err] 1242 - Subquery returns more than 1 row 

Answer 1

试试这个将它们分组，

UPDATE monthly_report a 
     LEFT JOIN 
     (
      SELECT MONTH(STR_TO_DATE(a.Evaluation_day,'%d.%m.%Y')) mnt, 
        SUM(a.NUM_STATUS_CHANGES) totalSum 
      FROM statuschange a 
      WHERE a.STATUS_CHANGE IN ('CANCEL', 'LOCK') 
      GROUP BY MONTH(STR_TO_DATE(a.Evaluation_day,'%d.%m.%Y')) 
     ) b ON b.mnt = a.mon 
SET  a.De_Ative = IFNULL(b.totalSum, 0)