SQL查询如何不删除行仅当is_primary_mo=1时显示f表指定字段

核心逻辑为使用条件判断语句替换原SQL中直接读取f表两个字段的逻辑，仅当is_primary_mo值为'1'时返回对应字段内容，否则返回空值，完全不会过滤原有员工行。

修改后完整SQL（兼容所有主流数据库） #

SELECT 
    a.last_name, 
    a.first_name, 
    a.id_number, 
    a.start_date AS 'Hire Date', 
    e.program_name, 
    a.dob, 
    a.mobile_phone AS Cell_phone, 
    a.email_staff,
    c.description AS Role, 
    -- 仅is_primary_mo为1时返回值，否则返回空
    CASE WHEN f.is_primary_mo = '1' THEN f.is_primary_mo ELSE NULL END AS 'Primary office',
    CASE WHEN f.is_primary_mo = '1' THEN f.street_address_1 ELSE NULL END AS street_address_1
FROM staff_view a
INNER JOIN staff_worker_role_link b
ON a.staff_id = b.staff_id
INNER JOIN worker_role c
ON b.worker_role_id = c.worker_role_id
INNER JOIN staff_program_link d
ON a.staff_id = d.staff_id
INNER JOIN program_info e
ON d.program_info_id = e.program_info_id
INNER JOIN staff_office_demographics_byprimary_view f
ON a.staff_id = f.staff_id
WHERE a.end_date IS NULL
ORDER BY a.last_name, a.first_name

MySQL环境简化写法 #

如果使用MySQL数据库，两个条件字段可以简化为IF函数写法：

IF(f.is_primary_mo = '1', f.is_primary_mo, NULL) AS 'Primary office',
IF(f.is_primary_mo = '1', f.street_address_1, NULL) AS street_address_1

修改后is_primary_mo为非1值时，两个字段会返回空值，既保留了所有员工行，也实现了指定内容的隐藏需求，原有查询的过滤、排序逻辑无需调整。

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