由于语法错误，联合选择在zend-db中失败

Question

我在使用zend-db构造UNION SELECT时遇到问题。我想将三个选择组合在一起：

( SELECT `id1` AS `id` FROM `table1` ) UNION ( SELECT `id2` AS `id2` FROM `table2` ) UNION ( SELECT `id3` AS `id` FROM `table3` )

ID字段的计数、顺序和数据类型是相等的，查询执行时没有任何错误。

然后，我尝试通过zend-db构建以下查询：

$select1 = new Select();
$select1->from('table1');
$select1->columns(['id1']);

$select2 = new Select();
$select2->from('table2');
$select2->columns(['id2']);

$select3 = new Select();
$select3->from('table3');
$select3->columns(['id3']);

$select2->combine($select1, Select::COMBINE_UNION);
$select3->combine($select2, Select::COMBINE_UNION);

这将导致以下查询(取自日志)：

( SELECT `id1` AS `id` FROM `table1` ) UNION (( SELECT `id2` AS `id2` FROM `table2` ) UNION ( SELECT `id3` AS `id` FROM `table3` ))

和SQL语法错误，因为第二个UNION是错误的。

确切的错误消息如下所示：

"exception": {
    "class": "Zend\\Db\\Adapter\\Exception\\InvalidQueryException",
    "code": 0,
    "message": "Statement could not be executed (42000 - 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ( SELECT `id3` AS `id`' at line 1)"
}

我还尝试以这种方式组合选择：

$select1->combine($select2, Select::COMBINE_UNION)->combine($select3, Select::COMBINE_UNION);

但随后我得到了以下异常：

"exception": {
    "class": "Zend\\Db\\Sql\\Exception\\InvalidArgumentException",
    "code": 0,
    "message": "This Select object is already combined and cannot be combined with multiple Selects objects"
}

Answer 1

实际的错误消息是什么？

不确定这是否会有任何效果，但请尝试显式地CASTing "top“查询中返回的数据类型：

( 
  SELECT CAST(`id1` AS <data_type>) AS `id` FROM `table1` 
) 
UNION 
(
  ( SELECT CAST(`id2` AS <data_type>) AS `id2` FROM `table2` ) 
  UNION 
  ( SELECT `id3` AS `id` FROM `table3` )
)