mysql - 用自定义字符串替换 Mysql 空字段

Question

我正在使用 Mysql 开发基于 Laravel 的应用程序。我有下面的 Mysql 查询确实按预期工作。我不是 Mysql 专家，但基本上我想做的是，对于每个 AptStatus = 3，我都有一个用于 Operatory 的 Null 字段。我想将该 Null 字段转换为该位置中每个 Operatory 的 Operatory 名称。 (希望这是有道理的)。

我将其作为变量存储在 php 中以存储我的位置列表。

$locs =(在 Eloquent 查询下方)

位置查询:

select
    op.Location, op.Operatory 
from rpt_officeschedules as op 
left join apptwidget_locations al on op.Location = al.location
where 
    (op.Operatory LIKE '%NP%' or op.Operatory LIKE '%OPEN%') and
    al.isOfficeClosed = 0
group by Location, Operatory;

查询结果:

Location | Operatory | 
---------  ----------
Addison.    2 NP
Addison.    3 NP/TX

MySql 查询:

select
    Location,
    AptDate,
    IF(AptStatus = 3, '08:00:00', AptTime) AptTime,
    IF(AptStatus = 3, 540, AptLength) AptLength,
    AptStatus,
    OperatoryNum,
    Operatory
from rpt_officeschedules
where
    AptTime between '07:59:00' and '17:30:00' and 
    AptDate between '2020-11-07' and '2020-11-08' and 
    (
        Operatory LIKE '%NP%' or 
        Operatory LIKE '%OPEN%' or 
        Operatory is null
    )
order by Location, AptDate, OperatoryNum, AptTime, Operatory;

查询结果

Location | AptDate   | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- ---------   --------- ----------- ----------- ----------- --------------
Addison.  2020-11-07   08:00:00.  540        null            3           0
Addison   2020-11-07   08:00:00.  540        null            3           0
Addison.  2020-11-08   08:00:00.  540        null            3           0
Addison   2020-11-08   08:00:00.  540        null            3           0

id 喜欢做的是当一个位置显示 AptStatus = 3 时，为每个 Operatory 名称($locs)转换该空字段

预期结果:

Location | AptDate   | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- ---------   --------- ----------- ----------- ----------- --------------
Addison.  2020-11-07   08:00:00.  540        2 NP            3           0
Addison   2020-11-07   08:00:00.  540        3 NP/TX         3           0
Addison.  2020-11-08   08:00:00.  540        2 NP            3           0
Addison   2020-11-08   08:00:00.  540        3 NP/TX         3           0

有什么想法吗？

Answer 1

如果我的输入正确的话，会是这样的:

select
    os.Location,
    os.AptDate,
    IF(os.AptStatus = 3, '08:00:00', os.AptTime) AptTime,
    IF(os.AptStatus = 3, 540, os.AptLength) AptLength,
    os.AptStatus,
    os.OperatoryNum,
    COALESCE(os.Operatory, op.Operatory)
from rpt_officeschedules os
left join apptwidget_locations al on al.location = os.Location and al.isOfficeClosed = 0
left join rpt_officeschedules op on op.Location = os.Location and (op.Operatory LIKE '%NP%' or op.Operatory LIKE '%OPEN%')
where
    os.AptTime between '07:59:00' and '17:30:00' and 
    os.AptDate between '2020-11-07' and '2020-11-08' and 
    (
        os.Operatory LIKE '%NP%' or 
        os.Operatory LIKE '%OPEN%' or 
        os.Operatory is null
    )
order by os.Location, os.AptDate, os.OperatoryNum, os.AptTime, COALESCE(os.Operatory, op.Operatory);