MySQL -联合应用条件

Question

我正在进行以下选择：

(SELECT id, chave, 'L' as tipo, codigo, clienteID, dataSaida, dataChegada, veiculo, situacao FROM locacoes WHERE chave = '1111111111111' && situacao='C')
    UNION
(SELECT id, chave, 'C' as tipo, codigo, clienteID, dataIni as dataSaida, dataFim as dataChegada,veiculo, status as situacao FROM contratos WHERE chave = '1111111111111' && status='F' )

结果我有：

​

​

现在我需要对结果应用一个条件.

WHERE dataSaida >= '2020-05-06 11:26:00' AND dataChegada <= '2020-05-06 11:26:00'

我试过这样做，但行不通。

(SELECT id, chave, 'L' as tipo, codigo, clienteID, dataSaida, dataChegada, veiculo, situacao FROM locacoes WHERE chave = '1111111111111' && situacao='C')
    UNION
(SELECT id, chave, 'C' as tipo, codigo, clienteID, dataIni as dataSaida, dataFim as dataChegada,veiculo, status as situacao FROM contratos WHERE chave = '1111111111111' && status='F' )
WHERE dataSaida >= '2020-05-06 11:26:00' AND dataChegada <= '2020-05-06 11:26:00'

我该怎么办？

谢谢。

图谋

企图2

(SELECT id, chave, 'L' as tipo, codigo, clienteID, dataSaida, dataChegada, veiculo, situacao FROM locacoes WHERE chave = '1111111111111' && situacao='C' && dataSaida >= '2020-05-06 11:26:00' AND dataChegada <= '2020-05-06 11:26:00')
        UNION
(SELECT id, chave, 'C' as tipo, codigo, clienteID, dataIni as dataSaida, dataFim as dataChegada, veiculo, status as situacao FROM contratos WHERE chave = '1111111111111' && status='F' && dataIni >= '2020-05-06 11:26:00' AND dataFim <= '2020-05-06 11:26:00')

尝试3

SELECT * FROM
(
    SELECT id, chave, 'L' as tipo, codigo, clienteID, dataSaida, dataChegada, veiculo, situacao FROM locacoes WHERE chave = '1111111111111' && situacao='C'
        UNION
    SELECT id, chave, 'C' as tipo, codigo, clienteID, dataIni as dataSaida, dataFim as dataChegada, veiculo, status as situacao FROM contratos WHERE chave = '1111111111111' && status='F' 
)
AS sub
WHERE sub.dataSaida >= '2020-05-06 11:26:00' AND sub.dataChegada <= '2020-05-06 11:26:00'

Answer 1

可以将所有结果收集到子查询中并转换为derived_table，然后应用筛选器对派生表进行筛选。

https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html

之后，您应该更改比较操作符，这样您就可以说(假设api日期是星期三下午3点)，给我所有的记录

exitDate(dataSaida)在周三下午3点之前，arrivalDate(dataChegada)在>=周三下午3点之前。

这应该是从派生表获得结果后的修正。

SELECT * FROM
    (    
        SELECT id, chave, 'L' as tipo, codigo, clienteID, dataSaida, dataChegada, veiculo, situacao FROM locacoes WHERE chave = '1111111111111' && situacao='C'
        UNION
        SELECT id, chave, 'C' as tipo, codigo, clienteID, dataIni as dataSaida, dataFim as dataChegada,veiculo, status as situacao FROM contratos WHERE chave = '1111111111111' && status='F'
    ) as derived_table
WHERE dataSaida <= '2020-05-06 11:26:00' AND dataChegada >= '2020-05-06 11:26:00'