mysql - SQL 间歇性地不返回任何数据来响应 LEFT JOIN

Question

我正在尝试查找 SQL 查询中的错误，但似乎无法深入了解它。查询如下所示:

SELECT
    DATE(BT.DateCheckedIn) AS X,
    DAYOFWEEK(BT.DateCheckedIn) AS DayX,
    SUM(IR.QtyCheckedIn) AS C,
    AU.AdminUsername,
    AU.AdminFirstName,
    AU.AdminLastName,
    IF(BTE.ProdLogID IS NULL, 'No', 'Yes') AS Exclude
FROM
    buying_issuesreceived IR
    JOIN buying_transactions BT ON IR.TransactionID = BT.TransactionID
    JOIN adminusers AU ON BT.CheckedInByAdminUserID = AU.AdminUserID
    LEFT JOIN log_production_bt BTE
        ON DATE(BT.DateCheckedIn) = DATE(BTE.ProductionDate)
        AND BTE.ProductionSection = 'wtransactions'
        AND AU.AdminUsername = BTE.ProductionUsername
WHERE
    DATE(BT.DateCheckedIn) BETWEEN DATE  '2018-09-24' AND DATE  '2018-09-30'
GROUP BY
    DATE(BT.DateCheckedIn),
    AU.AdminUsername

运行此查询时，它有大约 50/50 的机会返回正确的数据或根本不返回任何数据。没有错误消息。我知道，或者更确切地说应该说，我非常确定 LEFT JOIN 是罪魁祸首，因为当我从代码中删除它时，我不再获得空表，但我一生都无法弄清楚为什么这个查询会首先返回不一致的结果。

Answer 1

这听起来像是 MySQL 的错误。作为解决方法，您可以将 EXISTS 与相关子查询结合使用。

SELECT
    DATE(BT.DateCheckedIn) AS X,
    DAYOFWEEK(BT.DateCheckedIn) AS DayX,
    SUM(IR.QtyCheckedIn) AS C,
    AU.AdminUsername,
    AU.AdminFirstName,
    AU.AdminLastName,    
    IF(EXISTS(
        SELECT 1
        FROM log_production_bt BTE
        WHERE 
            DATE(BT.DateCheckedIn) = DATE(BTE.ProductionDate)
            AND BTE.ProductionSection = 'wtransactions'
            AND AU.AdminUsername = BTE.ProductionUsername), 'NO', 'YES') AS Exclude
FROM
    buying_issuesreceived IR
    JOIN buying_transactions BT ON IR.TransactionID = BT.TransactionID
    JOIN adminusers AU ON BT.CheckedInByAdminUserID = AU.AdminUserID
WHERE
    DATE(BT.DateCheckedIn) BETWEEN DATE  '2018-09-24' AND DATE  '2018-09-30'
GROUP BY
    DATE(BT.DateCheckedIn),
    AU.AdminUsername