将嵌套/多查询与单个查询连接 - Join nested / multi query with a single query

Join nested / multi query with a single query

本文关键字：查询连接单个嵌套 | 更新日期: 2023-09-27

如何将这个多查询与单个mysql查询连接起来？

$query = $mysql->query("select rate from postages where city=$city");
if($query->num_rows == 0)
{
    $query = $mysql->query("select rate from postages where subdistrict=$subdistrict");
    if($query->num_rows == 0)
    {
        $query = $mysql->query("select rate from postages where district=$district");           
    }
}

谢谢你的帮助。

您可以将查询联合在一起，但您需要在结果中包含一些内容，以便判断它是城市、地区还是街道。下面这样的东西可能会起作用，尽管我显然还没能测试它

(
    SELECT 
        city AS city,
        NULL AS district, 
        NULL AS subdistrict,  
        rate AS rate 
    FROM postages 
    WHERE city=$city
) UNION (
    SELECT 
        NULL,
        district, 
        NULL,
        rate
    FROM postages 
    WHERE city=$city
    AND district=$district
) UNION (
    SELECT 
        NULL, 
        NULL, 
        subdistrict,  
        rate
    FROM postages 
    WHERE city=$city
    AND district=$district
    AND subdistrict=$subdistrict
)

如果结果集至少包含一行city不为null，一行district不为null和一行substrict不为null的行，那么您就得到了一个有效的结果集。

如果你想得到与你的例子完全相同的结果，你可以这样做：

SELECT IFNULL(c.rate, IFNULL(d.rate, s.rate)) as rate FROM (
    (SELECT rate, count(*) FROM postages where city = '$city') as c,
    (SELECT rate, count(*) FROM postages where district = '$district') as d,
    (SELECT rate, count(*) FROM postages where subdistrict = '$subdistrict') as s
)

你也可以用另一种方式。。。如果城市查询返回0行，则仅运行地区查询；如果地区查询返回0行将仅运行分区调用：

SELECT IFNULL(
        (SELECT rate FROM postages where city = '$city'),
     IFNULL(
       (SELECT rate FROM postages where district = '$district' limit 1),
       (SELECT rate FROM postages where subdistrict = '$subdistrict' limit 1)
     )
) as rate