时间戳

首页 > 解决方案 > 一个 LEFT JOIN 的 4 表查询

mysql - 一个 LEFT JOIN 的 4 表查询

问题描述

我正在开发一个可以帮助我平衡加班的应用程序。我需要检索在给定时间段 (OT_Shift.OT_PeriodID=4) 内满足特定条件 (Staff.OT_Eligibility=1) 的所有员工的列表，以及在该期间提供的轮班总数. 以下查询提取正确的结果，但不包括提供总轮班次数为 0 的任何员工。

我遇到的问题是我需要在查询中返回的列由两个中间表分隔。对于 OT 的每个班次，有多个符合条件的工作人员，每个有资格参加该班次的工作人员可能有 7 个不同的响应代码之一（每个响应代码分配了不同的值来计算均衡）。这会强制从 Staff->OT_Shift->OT_Shift_Roster->OT_Response 建立关系。

我已经尝试在每个 JOIN 语句中包含“LEFT JOIN”，希望它可能只是以相反的顺序评估并且不会改变结果集。

SELECT 
    s.Staff_ID,
    s.LastName,
    sum(ResponseValue) AS Shift_Count
FROM Staff s
LEFT JOIN OT_Shift_Roster sr
    ON s.Staff_ID=sr.RosterID
JOIN OT_Response otr
    ON sr.Response=otr.OT_Response_ID
JOIN OT_Shift ots
    ON sr.ShiftID=ots.Shift_ID
WHERE
    s.OT_Eligibility = 1 AND ots.OT_PeriodID=4
GROUP BY s.Staff_ID

我得到以下信息，但我缺少大约 20 名在“Shift_Count”字段中有 0 的工作人员。

# Staff_ID, LastName, Shift_Count
3, Kantner, 1
10, Wells, 2
12, Moore, 1
46, Denomy, 1
48, Costanzo, 1
49, George, 2
50, Schusterman, 2
51, O'Connor, 1
55, McLeod, 2
59, Potter, 1
69, Tristan, 1
75, Gardull, 1
77, Weis, 1
106, Thomasson, 1
107, Lehmann, 1

标签： mysql

解决方案

您需要将所有JOIN依赖于LEFT JOINed 表的 s 转为LEFT JOINse 的ON子句JOIN。COALESCE()（或您的 RDBMS 上的类似内容）可用于返回0，而不是NULL在LEFT JOIN.

SELECT 
    s.Staff_ID,
    s.LastName,
    COALESCE(SUM(ResponseValue), 0) AS Shift_Count
FROM Staff s
LEFT JOIN OT_Shift_Roster sr
    ON s.Staff_ID=sr.RosterID
LEFT JOIN OT_Response otr
    ON sr.Response=otr.OT_Response_ID
LEFT JOIN OT_Shift ots
    ON sr.ShiftID=ots.Shift_ID AND ots.OT_PeriodID=4
WHERE
    s.OT_Eligibility = 1 
GROUP BY s.Staff_ID

基本原理：每条记录都必须满足WHERE子句中的条件。但是当出现空时，条件不能满足，违规记录将从结果集中逐出。换句话说，将条件放在子句中的ed 表上会隐式地将其转换为. 相同的逻辑适用于依赖于ed 表的 an。LEFT JOINLEFT JOINWHEREINNER JOININNER JOINLEFT JOIN

推荐阅读