php - 在使用 foreach 循环构建 sql 查询时删除最后一个 "UNION ALL"

Question

我想使用 foreach 循环构建以下 SQL 查询字符串

SELECT 'name' AS `field`. GROUP_CONCAT(DISTINCT `name`  ORDER BY `name`) AS `value`
FROM product
UNION ALL
SELECT 'category' AS `field`. GROUP_CONCAT(DISTINCT `category` ORDER BY `category`) AS `value`
FROM product
UNION ALL
SELECT 'year' AS `field`. GROUP_CONCAT(DISTINCT `year` ORDER BY `year`) AS `value`
FROM product

棘手的部分是以下循环会在 year 的最后一个 SELECT 语句中导致不必要的“UNION ALL”。

$selects = array("name"=>"Name","category"=>"Category","year"=>"Year");

$sql="";

foreach($selects as $select => $title)
{
  $sql .= "SELECT '".$select."' AS `field` , GROUP_CONCAT( DISTINCT `".$select."`ORDER BY `".$select."` SEPARATOR '|' ) AS `value` FROM `product` UNION ALL";
}

谁能告诉我如何从数组中的最后一个元素(在本例中为 Year)中删除最后一个 UNION ALL？

Answer 1

在变量中使用UNION ALL。将其初始化为空字符串。

在每次迭代前加上它。然后将 UNION ALL 分配给变量。

$selects = array("name"=>"Name","category"=>"Category","year"=>"Year");

$sql="";
$union_all="";

foreach($selects as $select => $title)
{
  $sql .= $union_all . "SELECT '".$select."' AS `field` , GROUP_CONCAT( DISTINCT `".$select."`ORDER BY `".$select."` SEPARATOR '|' ) AS `value` FROM `product`";
  $union_all=" UNION ALL ";
}

试一试!!!