数据表的数据如上,我现在想查出zhezhang表中的所有数据,以及有用id结尾的名字,特殊情况是后面两个id(不是每一行都有id的)!
开始使用的SQL语句如下:
SELECT
mes_op_step.`name`,
mes_machinetype.name as mechineTypeName,
mes_op.name as opName,
mes_engineering.name as engineeringName,
mes_op_processes.name as opProcessesName,
mes_processes.name as processesName,
mes_step.name as stepName
FROM mes_op_step
LEFT JOIN mes_machinetype ON mes_op_step.machineType_id = mes_machinetype.ID
LEFT JOIN mes_op ON mes_op_step.op_id = mes_op.ID
LEFT JOIN mes_engineering ON mes_op_step.engineering_id = mes_engineering.ID
LEFT JOIN mes_op_processes ON mes_op_step.op_processes_id = mes_op_processes.ID
LEFT JOIN mes_processes ON mes_op_step.processes_id = mes_processes.ID
LEFT JOIN mes_step ON mes_op_step.step_id = mes_step.ID
WHERE mes_machinetype.abandon=0 and mes_op.abandon=0
and mes_op_processes.abandon=0 and mes_engineering.abandon=0
and mes_op_step.abandon=0 and mes_processes.abandon=0
and mes_step.abandon=0
ORDER BY mes_op_step.sequence
但是最终的结果不是我想要的数据结果:主表中没有后面两个ID的数据就没有出来,只显示了三行数据!!!!!!
正确的SQL语句如下:
SELECT
mes_op_step.`name`,
mes_machinetype.name as mechineTypeName,
mes_op.name as opName,
mes_engineering.name as engineeringName,
mes_op_processes.name as opProcessesName,
(SELECT mes_processes.name from mes_processes where mes_processes.id = mes_op_step.processes_id AND mes_op_step.abandon =0)as processesName,
(SELECT mes_step.name from mes_step where mes_step.id = mes_op_step.step_id AND mes_op_step.abandon =0)as stepName
FROM mes_op_step
LEFT JOIN mes_machinetype ON mes_op_step.machineType_id = mes_machinetype.ID
LEFT JOIN mes_op ON mes_op_step.op_id = mes_op.ID
LEFT JOIN mes_engineering ON mes_op_step.engineering_id = mes_engineering.ID
LEFT JOIN mes_op_processes ON mes_op_step.op_processes_id = mes_op_processes.ID
WHERE mes_machinetype.abandon=0 and mes_op.abandon=0
and mes_op_processes.abandon=0 and mes_engineering.abandon=0
and mes_op_step.abandon=0
ORDER BY mes_op_step.sequence
效果: