php - mysql查询具有联合和限制的多表

Question

我需要修复一个查询，但不知道如何做。实际上查询是这样的

(SELECT qrliststgo.ordenser,qrliststgo.razon,qrliststgo.emision,qrliststgo.despacho,hitos.fecha FROM `qrliststgo`,`hitos` WHERE `qrliststgo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrliststgo`.`sigla` AND `hitos`.`guia`=`qrliststgo`.`guia`) UNION (SELECT qrlistvalpo.ordenser,qrlistvalpo.razon,qrlistvalpo.emision,qrlistvalpo.despacho,hitos.fecha FROM `qrlistvalpo`,`hitos` WHERE `tipotransporte`='I' AND `hitos`.`idplace`=`qrlistvalpo`.`sigla` AND `hitos`.`guia`=`qrlistvalpo`.`guia`) UNION (SELECT qrlistsananto.ordenser,qrlistsananto.razon,qrlistsananto.emision,qrlistsananto.despacho,hitos.fecha FROM `qrlistsananto`,`hitos` WHERE `qrlistsananto`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistsananto`.`sigla` AND `hitos`.`guia`=`qrlistsananto`.`guia`) UNION (SELECT qrlistlocalstgo.ordenser,qrlistlocalstgo.razon,qrlistlocalstgo.emision,qrlistlocalstgo.despacho,hitos.fecha FROM `qrlistlocalstgo`,`hitos` WHERE `qrlistlocalstgo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistlocalstgo`.`sigla` AND `hitos`.`guia`=`qrlistlocalstgo`.`guia`) UNION (SELECT qrlistlocalvalpo.ordenser,qrlistlocalvalpo.razon,qrlistlocalvalpo.emision,qrlistlocalvalpo.despacho,hitos.fecha FROM `qrlistlocalvalpo`,`hitos` WHERE `qrlistlocalvalpo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistlocalvalpo`.`sigla` AND `hitos`.`guia`=`qrlistlocalvalpo`.`guia`) UNION (SELECT qrlistlocalsananto.ordenser,qrlistlocalsananto.razon,qrlistlocalsananto.emision,qrlistlocalsananto.despacho,hitos.fecha FROM `qrlistlocalsananto`,`hitos` WHERE `qrlistlocalsananto`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrlistlocalsananto`.`sigla` AND `hitos`.`guia`=`qrlistlocalsananto`.`guia`)

是的，我知道它很长，基本上，我需要从“qrliststgo”中找到所有匹配它的 2 列和 2 列“hitos”的行。

(SELECT qrliststgo.ordenser,qrliststgo.razon,qrliststgo.emision,qrliststgo.despacho,hitos.fecha FROM `qrliststgo`,`hitos` WHERE `qrliststgo`.`tipotransporte`='I' AND `hitos`.`idplace`=`qrliststgo`.`sigla` AND `hitos`.`guia`=`qrliststgo`.`guia`)

然后使用 UNION 再次对另一个名为“qrlistvalpo”和“hitos”的表执行相同的操作。

我遇到的问题是，在“hitos”中，有 3 行匹配，其中一行来自“qrliststgo”，因此它被“复制”了......除了最后一行改变之外，3 行具有相同的信息因为它在“hitos”中找到了 3 个结果。

我该怎么做？

(我需要这个 php 才能导出)

更新:

我需要的是显示 qrlist 中至少有一行 qrlist.sigla = hitos.idplace 和 qrlist.guia=hitos.guia 的所有行。

如果 hitos 中有多个结果匹配，则显示第一个并跳过其他结果，然后检查 qrlist 中的另一个结果。

不知道我说的对不对

Answer 1

看看以下是否能满足您的需求:

      (SELECT ordenser, razon, emision, despacho, fecha FROM qrliststgo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistvalpo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistsananto
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistlocalstgo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistlocalvalpo
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos))
UNION (SELECT ordenser, razon, emision, despacho, fecha FROM qrlistlocalsananto
       WHERE (sigla, guia) IN (SELECT DISTINCT idplace, guia FROM hitos));