gpt4 book ai didi

php - UNION 与带有大 WHERE 子句的两个查询

转载 作者:行者123 更新时间:2023-11-29 11:55:57 26 4
gpt4 key购买 nike

我有两个几乎相同的查询 - 一个连接表 gz_life_groups 和 gz_life_groups2,另一个连接 gz_life_floaters 和 gz_life_floaters2。我想使用 UNION 将两个查询合并为一个查询,但我无法跳过所有的障碍。

表格功能的简要说明:想象一下导航到 URL MySite/life/ursus-maritimus。 Ursus-maritimus 与数据库表字段 Taxon 中的值匹配。因此,如果 Ursus maritimus(北极熊)已被放置在名为“White Mammals”的组中,它将匹配表 gz_life_groups 中字段 Title(“White Mammals”)和 URL(“white-mammals”)中的值。链接字段仅包含相关页面的链接。

查询 1

$Groups = $pdo->prepare("SELECT G2.URL, G2.Taxon, G1.URL, G1.Title, G1.Links
FROM gz_life_groups2 AS G2
LEFT JOIN gz_life_groups G1 ON G1.URL = G2.URL
WHERE Taxon = :Kingdom AND G2.Live = '1'
OR Taxon = :Phylum AND G2.Live = '1'
OR Taxon = :Class AND G2.Live = '1'
OR Taxon = :Order AND G2.Live = '1'
OR Taxon = :Family AND G2.Live = '1'
OR Taxon = :Genus AND G2.Live = '1'
OR Taxon = :MyURL AND G2.Live = '1'
GROUP BY G1.URL
Order By G2.N");
$Groups->execute(array(
'Kingdom'=>$Kingdom,
'Phylum'=>$Phylum,
'Class'=>$Class,
'Order'=>$Order,
'Family'=>$Family,
'Genus'=>$Genus,
'MyURL'=>$MyURL
));

查询 2

$Names = $pdo->prepare("SELECT F2.URL, F2.Taxon, F1.URL, F1.Name, F1.Parent
FROM gz_life_floaters2 AS F2
LEFT JOIN gz_life_floaters F1 ON F1.URL = F2.URL
WHERE Taxon = :Kingdom AND F2.Live = '1'
OR Taxon = :Phylum AND F2.Live = '1'
OR Taxon = :Class AND F2.Live = '1'
OR Taxon = :Order AND F2.Live = '1'
OR Taxon = :Family AND F2.Live = '1'
OR Taxon = :Genus AND F2.Live = '1'
OR Taxon = :MyURL AND F2.Live = '1'
GROUP BY F1.URL
Order By F2.N");
$Names->execute(array(
'Kingdom'=>$Kingdom,
'Phylum'=>$Phylum,
'Class'=>$Class,
'Order'=>$Order,
'Family'=>$Family,
'Genus'=>$Genus,
'MyURL'=>$MyURL
));

这就是我对 UNION 的有限了解。我不知道如何添加联接或 WHERE 子句...

$stm = $pdo->prepare("SELECT *
FROM (
SELECT G2.URL, G2.Taxon, G1.URL, G1.Title, NULL AS Parent, G1.Links
FROM gz_life_groups2 AS G2
UNION ALL
SELECT F2.URL, F2.Taxon, F1.URL, F1.Name AS Title, F1.Parent, NULL AS Links
FROM gz_life_floaters2 AS F2
) AS Combined
WHERE Combined.URL LIKE :MyURL");
$stm->execute(array(
'MyURL'=>$MyURL
));

最佳答案

你可以尝试这样写:

SELECT G2.URL, G2.Taxon, G1.URL, G1.Title, G1.Links
FROM gz_life_groups2 AS G2
LEFT JOIN gz_life_groups G1 ON G1.URL = G2.URL
WHERE Taxon = :Kingdom AND G2.Live = '1'
OR Taxon = :Phylum AND G2.Live = '1'
OR Taxon = :Class AND G2.Live = '1'
OR Taxon = :Order AND G2.Live = '1'
OR Taxon = :Family AND G2.Live = '1'
OR Taxon = :Genus AND G2.Live = '1'
OR Taxon = :MyURL AND G2.Live = '1'
GROUP BY G1.URL

UNION

SELECT F2.URL, F2.Taxon, F1.URL, F1.Name, F1.Parent
FROM gz_life_floaters2 AS F2
LEFT JOIN gz_life_floaters F1 ON F1.URL = F2.URL
WHERE Taxon = :Kingdom AND F2.Live = '1'
OR Taxon = :Phylum AND F2.Live = '1'
OR Taxon = :Class AND F2.Live = '1'
OR Taxon = :Order AND F2.Live = '1'
OR Taxon = :Family AND F2.Live = '1'
OR Taxon = :Genus AND F2.Live = '1'
OR Taxon = :MyURL AND F2.Live = '1'
GROUP BY F1.URL

例如,如果 UNION 的第一部分和第二部分的 :Phylum 值不同,请将第二个 :Phylum 命名为 :PylumBelowUnion 或类似的名称,并传递一个数组两个值:Phylum :PhylumBelowUnion。

我还建议您在 andor 中使用括号,如下所示:

(Taxon = :Kingdom AND F2.Live = '1') OR
(Taxon = :MyURL AND F2.Live = '1') ...

我假设这就是你想要的。如果没有,请忽略上面的建议。

关于php - UNION 与带有大 WHERE 子句的两个查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33193610/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com