MySQL错误输出表中的左联接

Question

我做了一个左连接来获得我想要的数据。然而，输出表有4列，其中前3列有正确的数据，但最后一列与其他3列不匹配。因此，具体地说，在一个表中我有名称，在另一个表中我有日期、用户名和“注释”。使它变得困难的是，我只需要在一个每天更改的表中包含最近的日期。这是我的问题：

SELECT CONCAT(ur.USR_FIRSTNAME, ur.USR_LASTNAME) AS 'NAME',
       aav.USR AS 'USERNAME',
       IF(aav.TIME_STAMP > NOW() + INTERVAL -4 HOUR,
       aav.AVAILABLE_DATE,MAX(aav.AVAILABLE_DATE)) AS 'DATE_AVAILABLE',
       aav.COMMENTS AS 'COMMENTS'   
FROM wi_workflow.USERS ur LEFT JOIN fii_tables.available_log aav ON    ur.USR_USERNAME = aav.USR and ur.USR_UID = aav.USR_UID
LEFT JOIN fii_tables.available_log aav2 ON(aav.COMMENT = aav2.COMMENT)
WHERE ur.USR_POSITION = 'EDE'
AND ur.USR_STATUS = 'ACTIVE'
AND aav.TIME_STAMP > NOW() + INTERVAL - 45 DAY
GROUP BY 1;

谁能给我一些提示，如何让最后一列与另外两列相匹配？谢谢。

Answer 1

我解决了我的问题，这是我使用的查询：

SELECT aev.ede_name,
           aav.available_date, 
           aav.COMMENT AS 'COMMENT',
           IF(aav.AVAILABLE_DATE < NOW() + INTERVAL 5 DAY, "AVAILABLE THIS WEEK",'NOT AVAILABLE')  AS 'AVAILABILITY'
    FROM fii_tables.available_log aav
    INNER JOIN (SELECT aav.`USR` AS usr,max(aav.TIME_STAMP) AS `TIME_STAMP` from fii_tables.available_log aav group by aav.USR;) lts ON ( lts.usr = aav.usr AND lts.time_stamp = aav.time_stamp )
    LEFT JOIN wf_workflow.actual_ede_v aev ON(aav.usr = aev.usr_username)
    where aev.ede_name is not null
    GROUP BY aav.usr, aav.time_stamp;