SQL查询结果匹配需求：调整第二查询以展示5个办公区数据

第一个查询（正常工作版本） #

该查询可按对应办公区获取跟踪号数据，能展示5个办公区：City Engineers、City Budget、City Administrator、General Service、City Accountant。

$sql = "SELECT Status FROM citydoc2023.status WHERE EmailNotifierFlag = 1";
$record = $database->query($sql);
$statuses = '';

while ($data = $database->fetch_array($record)) {
    $status1 = $data['Status'];
    $statuses .= ",'" . $status1 . "'";
}

$query = "SELECT a.TrackingNumber, a.Office, 
IF(a.trackingType = 'PR', 'Purchase Request', IF(a.trackingType = 'PO', 'Purchase Order', a.DocumentType)) AS DocumentType, 
a.Status AS DocumentStatus, 
a.DateModified, 
a.DelayedDays,
b.Email, b.OfficeCode, b.Office AS SenderOffice, 
e.Name AS OfficeName, a.Year, 
IF(a.TrackingType = 'PO', IF(a.totalAmountMultiple > 0, a.totalAmountMultiple, a.PO_Amount), IF(a.totalAmountMultiple > 0, a.totalAmountMultiple, a.Amount) ) AS Amount, 
b.EmailNotifierFlag 
FROM ( 
        SELECT TrackingNumber, Status, Office, DocumentType, DateModified, Year, TrackingType, Amount, PO_Amount, totalAmountMultiple, DATEDIFF(SUBSTR(CURDATE(), 1, 10), SUBSTR(DateModified, 1, 10)) AS DelayedDays 
        FROM citydoc2023.vouchercurrent 
        WHERE Status IN (".substr($statuses, 1).") 
        GROUP BY TrackingNumber
        UNION ALL

     ) a 
LEFT JOIN citydoc2023.status b ON a.Status = b.Status 
LEFT JOIN office e ON a.Office = e.Code 
WHERE a.DelayedDays >= 3 
GROUP BY a.TrackingNumber ORDER BY Email, a.DelayedDays DESC";

第二个查询（存在问题版本） #

该查询合并了2023和2024年的数据，但仅能展示4个办公区，缺失的办公区数据被合并至其他办公区中。

SELECT 
    a.TrackingNumber, 
    a.Office, 
    IF(a.trackingType = 'PR', 'Purchase Request', IF(a.trackingType = 'PO', 'Purchase Order', a.DocumentType)) AS DocumentType, 
    a.Status AS DocumentStatus, 
    a.DateModified, 
    a.DelayedDays, 
    b.Email, 
    b.OfficeCode, 
    b.Office AS SenderOffice, 
    e2023.Name AS OfficeName, 
    a.Year, 
    IF(a.TrackingType = 'PO', IF(a.totalAmountMultiple > 0, a.totalAmountMultiple, a.PO_Amount), IF(a.totalAmountMultiple > 0, a.totalAmountMultiple, a.Amount) ) AS Amount, 
    b.EmailNotifierFlag 
FROM ( 
    SELECT *, '2023' AS DynamicYear FROM ( 
        SELECT 
            TrackingNumber, 
            Status, 
            Office, 
            DocumentType, 
            DateModified, 
            Year, 
            TrackingType, 
            Amount, 
            PO_Amount, 
            totalAmountMultiple, 
            DATEDIFF(SUBSTR(CURDATE(), 1, 10), SUBSTR(DateModified, 1, 10)) AS DelayedDays 
        FROM citydoc2023.vouchercurrent 
        WHERE Status IN ('For Inspection','Pending at CBO','CAO Received ','Pending at CAO ','Pending at Admin ','Pending at CBO','GSO Received','Pending at GSO','Admin Received','Pending at Admin','GSO Received','Pending at GSO','Admin Received','Pending at Admin','Serve to Supplier','Supplier Conformed','Fund Control','Pending at CBO','For Inspection','Inventory','Pending at GSO - Inventory','CAO Received','Pending at CAO','On Evaluation - Accounting','Evaluated - Accounting','Forwarded to Admin - Administration') 
        GROUP BY TrackingNumber 
    ) AS subquery2023 
    UNION ALL 
    SELECT *, '2024' AS DynamicYear FROM ( 
        SELECT 
            TrackingNumber, 
            Status, 
            Office, 
            DocumentType, 
            DateModified, 
            Year, 
            TrackingType, 
            Amount, 
            PO_Amount, 
            totalAmountMultiple, 
            DATEDIFF(SUBSTR(CURDATE(), 1, 10), SUBSTR(DateModified, 1, 10)) AS DelayedDays 
        FROM citydoc2024.vouchercurrent 
        WHERE Status IN ('For Inspection','Pending at CBO','CAO Received ','Pending at CAO ','Pending at Admin ','Pending at CBO','GSO Received','Pending at GSO','Admin Received','Pending at Admin','GSO Received','Pending at GSO','Admin Received','Pending at Admin','Serve to Supplier','Supplier Conformed','Fund Control','Pending at CBO','For Inspection','Inventory','Pending at GSO - Inventory','CAO Received','Pending at CAO','On Evaluation - Accounting','Evaluated - Accounting','Forwarded to Admin - Administration') 
        GROUP BY TrackingNumber 
    ) AS subquery2024 
) a 
LEFT JOIN citydoc2023.status b ON a.Status = b.Status 
LEFT JOIN office e2023 ON a.Office = e2023.Code 
LEFT JOIN citydoc2024.status c ON a.Status = c.Status 
LEFT JOIN office e2024 ON a.Office = e2024.Code 
WHERE a.DelayedDays >= 3 
GROUP BY a.TrackingNumber 
ORDER BY Email, a.DelayedDays DESC

问题分析与修改方案 #

核心问题

1. 状态筛选逻辑不一致：第一个查询动态从citydoc2023.status获取EmailNotifierFlag=1的状态，第二个查询硬编码状态列表，可能导致数据筛选范围偏差。
2. 办公区名称关联错误：仅取e2023.Name作为办公区名称，若2024年办公区数据不在对应表中，会导致部分办公区名称无法匹配，进而被合并。
3. 跨年份关联无区分：关联status和office表时未按年份筛选，导致数据匹配混乱。

修改后的查询代码

// 沿用第一个查询的动态状态获取逻辑，确保筛选范围一致
$sql = "SELECT Status FROM citydoc2023.status WHERE EmailNotifierFlag = 1";
$record = $database->query($sql);
$statuses = '';

while ($data = $database->fetch_array($record)) {
    $status1 = $data['Status'];
    $statuses .= ",'" . $status1 . "'";
}

// 调整后的主查询
$query = "SELECT a.TrackingNumber, a.Office, 
IF(a.trackingType = 'PR', 'Purchase Request', IF(a.trackingType = 'PO', 'Purchase Order', a.DocumentType)) AS DocumentType, 
a.Status AS DocumentStatus, 
a.DateModified, 
a.DelayedDays,
COALESCE(b.Email, c.Email) AS Email,
COALESCE(b.OfficeCode, c.OfficeCode) AS OfficeCode,
COALESCE(b.Office, c.Office) AS SenderOffice,
COALESCE(e2023.Name, e2024.Name) AS OfficeName, 
a.Year, 
IF(a.TrackingType = 'PO', IF(a.totalAmountMultiple > 0, a.totalAmountMultiple, a.PO_Amount), IF(a.totalAmountMultiple > 0, a.totalAmountMultiple, a.Amount) ) AS Amount, 
COALESCE(b.EmailNotifierFlag, c.EmailNotifierFlag) AS EmailNotifierFlag 
FROM ( 
    SELECT 
        TrackingNumber, Status, Office, DocumentType, DateModified, Year, TrackingType, Amount, PO_Amount, totalAmountMultiple, 
        DATEDIFF(SUBSTR(CURDATE(), 1, 10), SUBSTR(DateModified, 1, 10)) AS DelayedDays 
    FROM citydoc2023.vouchercurrent 
    WHERE Status IN (".substr($statuses, 1).") 
    GROUP BY TrackingNumber
    UNION ALL
    SELECT 
        TrackingNumber, Status, Office, DocumentType, DateModified, Year, TrackingType, Amount, PO_Amount, totalAmountMultiple, 
        DATEDIFF(SUBSTR(CURDATE(), 1, 10), SUBSTR(DateModified, 1, 10)) AS DelayedDays 
    FROM citydoc2024.vouchercurrent 
    WHERE Status IN (".substr($statuses, 1).") 
    GROUP BY TrackingNumber
) a 
LEFT JOIN citydoc2023.status b ON a.Status = b.Status AND a.Year = '2023'
LEFT JOIN citydoc2024.status c ON a.Status = c.Status AND a.Year = '2024'
LEFT JOIN office e2023 ON a.Office = e2023.Code AND a.Year = '2023'
LEFT JOIN office e2024 ON a.Office = e2024.Code AND a.Year = '2024'
WHERE a.DelayedDays >= 3 
GROUP BY a.TrackingNumber 
ORDER BY COALESCE(b.Email, c.Email), a.DelayedDays DESC";

修改说明

1. 统一状态筛选：复用第一个查询的动态状态获取逻辑，确保2023、2024年数据的筛选范围完全匹配。
2. 按年份精准关联：给status和office表的关联添加Year条件，避免跨年份匹配导致的数据混乱。
3. COALESCE字段兜底：对Email、OfficeName等字段使用COALESCE，优先取对应年份的数据，确保即使某年份表无匹配项，也能保留有效数据，避免办公区信息丢失。
4. 简化冗余逻辑：移除无用的DynamicYear字段，优化子查询结构，保持逻辑清晰。

内容的提问来源于stack exchange，提问作者user18176756