MySQL查询与SQL_MODE=only_full_group_by不兼容

Question

查询：

SELECT
    Employee.Id as Id,
    Employee.FingerId as FingerId,
    Employee.Name as Name,
    Departement.Departement as Departement,
    EmployeeShift.Shift as Shift,
    ScanIn
FROM
    HrEmployee as Employee
    LEFT JOIN HrEmployeeShift as EmployeeShift 
        ON Employee.ShiftId = EmployeeShift.Id
    LEFT JOIN CmDept as Departement 
        ON Employee.DeptId = Departement.Id
    LEFT JOIN (
        SELECT
            TableEmployee.FingerId,
            ScanIn
        FROM
            HrEmployee as TableEmployee,
            HrAttLogsFormatted
        WHERE
            TableEmployee.FingerId = HrAttLogsFormatted.FingerId
            AND DateIn = '2019-11-04'
        GROUP BY HrAttLogsFormatted.FingerId
    ) AS HrJoinLogs 
        ON Employee.FingerId = HrJoinLogs.FingerId
WHERE
    Employee.Status = 1
    AND Employee.Flag = 1
    AND Employee.ShiftId = 1
    AND ScanIn is NULL
GROUP BY
    Employee.Name
ORDER BY
    Employee.Name ASC

有人有解决办法吗？

Answer 1

首先，阅读MySQL对GROUP by的臭名昭著的非标准处理。这里。

其次，您似乎在使用group by来消除结果集中的重复项。这个程序是有问题的。在我看来，这是不能接受的，当你的查询涉及别人的钱。您可能希望改用select distinct。但是，最好的方法是找出查询的预期逻辑，并重写它以更显式地交付该逻辑。

如果您仍然认为需要groupby，请查看查询工作时的结果集，然后再升级到更现代的MySQL版本。您将看到结果集的ScanIn列的所有行都为NULL，因为您的查询显示而ScanIn是NULL。