mysql - 获取其他表中的4个计数器(MYSQL)

Question

在我的时间表中有 1 , 2 , 3 ,4 计数器，我的问题是计数器 2 , 3 , 4 没有结果，但在我的计数器 1 中有一个输出(我这里有一些图片。)这是输出

和

这是我的另一张 table

SELECT id,effectiveDate,      
      if(note = 'REGULAR LOGGED' and counter='1',log,'') as am1,
      if(note = 'REGULAR LOGGED' and counter='2',log,'') as am2,
      if(note = 'REGULAR LOGGED' and counter='3',log,'') as am3,
      if(note = 'REGULAR LOGGED' and counter='4',log,'') as am4
    FROM schedules as sch
    LEFT JOIN (SELECT empid,counter,note,log,id,date FROM timesheet) tim ON sch.empid = tim.empid and sch.effectiveDate = tim.date WHERE sch.empid='16'
    AND YEAR(effectiveDate) = YEAR(CURDATE()) and MONTH(effectiveDate) = MONTH(CURDATE())
GROUP BY effectiveDate

Answer 1

使用聚合函数:

SELECT effectiveDate,      
       max(case note = 'REGULAR LOGGED' and counter = 1 then log end) as am1,
       max(case note = 'REGULAR LOGGED' and counter = 2 then log end) as am2,
       max(case note = 'REGULAR LOGGED' and counter = 3 then log end) as am3,
       max(case note = 'REGULAR LOGGED' and counter = 4 then log end) as am4
FROM schedules s LEFT JOIN
     timesheet t
     ON s.empid = t.empid and s.effectiveDate = t.date
WHERE s.empid = 16 AND
      YEAR(effectiveDate) = YEAR(CURDATE()) AND
      MONTH(effectiveDate) = MONTH(CURDATE())
GROUP BY effectiveDate