mysql - Sql 将 4 个表列与 1 个连接

Question

仅当值匹配时才可以使用联接或进行联接吗？

就像我有 GO 表，其中有 4 个员工 ID:

equi_func1_mat
equi_func2_mat
equi_func3_mat
equi_func4_mat

我的员工表只是用户的 ID

matricula_qt

我正在尝试做类似的事情

JOIN tb_funcionario func ON go.equi_func1_mat =  func.matricula_qt
OR JOIN tb_funcionario func ON go.equi_func2_mat =  func.matricula_qt
OR JOIN tb_funcionario func ON go.equi_func3_mat =  func.matricula_qt
OR JOIN tb_funcionario func ON go.equi_func4_mat =  func.matricula_qt

我也尝试过这个:

JOIN tb_funcionario func ON go.equi_func1_mat =  func.matricula_qt
OR go.equi_func2_mat OR go.equi_func3_mat OR go.equi_func4_mat

仅适用于一位雇主的完整查询:

SELECT  SEC_TO_TIME( SUM(TIME_TO_SEC(temp.tempoexposicao))) AS soma_tudo,
        temp.cod_go,
    temp.data,
    temp.item,
    temp.equi_func1_mat
FROM(
    SELECT
          prog.cod_go,
          prog.data,
          prog.item,
          prog.tempoexposicao,
          go.equi_func1_mat
    FROM tb_programacao prog
    JOIN tb_go go ON go.cod_go = prog.cod_go
    JOIN tb_funcionario func ON go.equi_func1_mat =  func.matricula_qt
    JOIN tb_horario hora ON func.cod = hora.cod_funcionario
    WHERE go.cod_go = prog.cod_go
    AND prog.data = hora.data
        GROUP BY  prog.data,prog.item) AS temp

我在 mysql 文档或网络中没有找到任何帮助，有人知道是否可以这样做吗？

谢谢!

Answer 1

可能是这样的:-

SELECT
      prog.cod_go,
      prog.data,
      prog.item,
      SEC_TO_TIME( SUM(TIME_TO_SEC(temp.tempoexposicao))) AS soma_tudo,
      CASE
        WHEN prog.data = hora1.data
        THEN go.equi_func1_mat
        WHEN prog.data = hora2.data
        THEN go.equi_func2_mat
        WHEN prog.data = hora3.data
        THEN go.equi_func3_mat
        ELSE go.equi_func4_mat
    END AS aFunc
FROM tb_programacao prog
JOIN tb_go go ON go.cod_go = prog.cod_go
LEFT OUTER JOIN tb_funcionario func1 ON go.equi_func1_mat =  func1.matricula_qt
LEFT OUTER JOIN tb_horario hora1 ON func1.cod = hora1.cod_funcionario
LEFT OUTER JOIN tb_funcionario func2 ON go.equi_func2_mat =  func2.matricula_qt
LEFT OUTER JOIN tb_horario hora2 ON func2.cod = hora2.cod_funcionario
LEFT OUTER JOIN tb_funcionario func3 ON go.equi_func3_mat =  func3.matricula_qt
LEFT OUTER JOIN tb_horario hora3 ON func3.cod = hora3.cod_funcionario
LEFT OUTER JOIN tb_funcionario func4 ON go.equi_func4_mat =  func4.matricula_qt
LEFT OUTER JOIN tb_horario hora4 ON func4.cod = hora4.cod_funcionario
WHERE  prog.data = hora1.data
OR  prog.data = hora2.data
OR  prog.data = hora3.data
OR  prog.data = hora4.data
GROUP BY  prog.cod_go, prog.data, prog.item, aFunc

请注意，这是未经测试的最佳猜测，因为我不知道表格之间的布局和实际关系