时间戳

首页 > 解决方案 > 带条件的内部连接 ​​- Mysql

mysql - 带条件的内部连接 ​​- Mysql

问题描述

如果条件为真但它不起作用，我正在尝试进行内部连接，我尝试了以下两种方法：

IF chat.tipo = 'vitima' THEN
   INNER JOIN vitima ON vitima.id_vit = chat.id_tipo

ELSE
   INNER JOIN terceiro ON terceiro.id_ter = chat.id_tipo

或者

IF(chat.tipo = 'vitima', 
        INNER JOIN vitima ON vitima.id_vit = chat.id_tipo,
        INNER JOIN terceiro ON terceiro.id_ter = chat.id_tipo)

但是两者都给出错误，我想要的是如果类型等于“vitima”，它会在一个表中执行内部 noin，否​​则在另一个表中。

完整查询：

SELECT ocorrencia.id_oco, 
        (SELECT GROUP_CONCAT(c.id_oco ORDER BY c.id_oco DESC) FROM ocorrencia as c WHERE c.id_sup_oco = ocorrencia.id_sup_oco) as grouped_ids, 
        ocorrencia.id_sup_oco, 
        chat.id_tipo,
        suporte_oco.data_sup,
        suporte_oco.placa_sup,
        suporte_oco.sinistro_sup,
        suporte_oco.prefixo_sup,
        ocorrencia.id_emp_oco,
        IF(chat.tipo = 'vitima', vitima.nome_vit, terceiro.nome_ter) as nome,
        chat.tipo

        FROM chat 
        INNER JOIN ocorrencia ON ocorrencia.id_oco = chat.id_oco_cha 
        INNER JOIN suporte_oco ON suporte_oco.id_sup = ocorrencia.id_sup_oco

        IF chat.tipo = 'vitima'
            INNER JOIN vitima ON vitima.id_vit = chat.id_tipo
        ELSE
            INNER JOIN terceiro ON terceiro.id_ter = chat.id_tipo

        WHERE chat.id_user = '20' OR chat.id_user_req = '20' GROUP BY (chat.id_oco_cha) ORDER BY chat.data DESC

标签： mysqlinner-join

解决方案

这不准确，但我认为这将非常接近你想要做的事情：

LEFT JOIN vitima ON vitima.id_vit = chat.id_tipo AND chat.tipo = 'vitima'
LEFT JOIN terceiro ON terceiro.id_ter = chat.id_tipo AND chat.tipo != 'vitima'
...
WHERE (chat.tipo = 'vitima' AND vitima.id_vit IS NOT NULL)
    OR (chat.tipo != 'vitima' AND terceiro.id_ter IS NOT NULL)

条件强制执行您的LEFT JOIN规则，并且WHERE条件模拟，INNER JOIN因为它需要这些记录存在。

使用您发布的完整查询，它看起来像这样：

SELECT ocorrencia.id_oco, 
    (SELECT GROUP_CONCAT(c.id_oco ORDER BY c.id_oco DESC) FROM ocorrencia as c WHERE c.id_sup_oco = ocorrencia.id_sup_oco) as grouped_ids, 
    ocorrencia.id_sup_oco, 
    chat.id_tipo,
    suporte_oco.data_sup,
    suporte_oco.placa_sup,
    suporte_oco.sinistro_sup,
    suporte_oco.prefixo_sup,
    ocorrencia.id_emp_oco,
    IF(chat.tipo = 'vitima', vitima.nome_vit, terceiro.nome_ter) as nome,
    chat.tipo

FROM chat 
    INNER JOIN ocorrencia ON ocorrencia.id_oco = chat.id_oco_cha 
    INNER JOIN suporte_oco ON suporte_oco.id_sup = ocorrencia.id_sup_oco
    LEFT JOIN vitima ON vitima.id_vit = chat.id_tipo AND chat.tipo = 'vitima'
    LEFT JOIN terceiro ON terceiro.id_ter = chat.id_tipo AND chat.tipo != 'vitima'

WHERE chat.id_user = '20' OR chat.id_user_req = '20'
    AND (
        (chat.tipo = 'vitima' AND vitima.id_vit IS NOT NULL)
        OR (chat.tipo != 'vitima' AND terceiro.id_ter IS NOT NULL)
    )
GROUP BY (chat.id_oco_cha) ORDER BY chat.data DESC

推荐阅读