我在SQL中有一个相当复杂的查询，它在两个表之间执行计数。查询应该如下所示：
SELECT
((SELECT COUNT(DISTINCT(pages.id)) AS `count`
FROM `pages`
INNER JOIN `pageRegions` ON pageRegions.pageId = pages.id
WHERE (MATCH (pages.name, pageRegions.contents) AGAINST ('+keyword*' IN BOOLEAN MODE))) +
(SELECT COUNT(documents.id) AS `count`
FROM `documents`
INNER JOIN `files` ON files.id = documents.file
WHERE (MATCH (documents.name,
files.name,
files.extracted_text) AGAINST ('+keyword*' IN BOOLEAN MODE)))) AS count
不幸的是，当我使用Zend_Db获得以下代码时，查询包含了大量额外的列，因此添加两个查询显然都失败了：
$total_count_select = $PagesTable->getAdapter()
->query('((' . $pages_total_count_select . ') +
(' . $legal_resources_total_count_select . ')
) AS count');
其中$pages_total_count_select和$legal_resources_total_count_select都是Zend_Db_Select对象。
我尝试使用columns()方法在每个选择对象上指定我想要的列，如下所示：
$legal_resources_total_count_select->columns('COUNT(documents.id) AS count');
但这似乎只是附加到选择查询上，而不是只返回指定的列。
所以Zend生成的查询最终看起来是这样的：
SELECT ((SELECT `pages`.*,
((1.3 * (MATCH(pages.name) AGAINST ('+keyword*' IN BOOLEAN MODE))) +
(0.8 * (MATCH(pageRegions.contents) AGAINST ('+keyword*' IN BOOLEAN MODE)))) AS `score`,
`pages`.`name` AS `page_name`,
`pages`.`id` AS `page_id`,
`pageRegions`.*,
COUNT(DISTINCT(pages.id)) AS `count`
FROM `pages`
INNER JOIN `pageRegions` ON pageRegions.pageId = pages.id
WHERE (MATCH (pages.name, pageRegions.contents) AGAINST ('+keyword*' IN BOOLEAN MODE))
ORDER BY `score` DESC)
+
(SELECT `documents`.*,
((1.3 * (MATCH(documents.title) AGAINST ('+keyword*' IN BOOLEAN MODE))) +
(0.8 * (MATCH(documents.short_description, files.NAME, files.extracted_text) AGAINST ('+keyword*' IN BOOLEAN MODE)))) AS `score`,
`files`.*,
COUNT(documents.id) AS `count`
FROM `documents`
INNER JOIN `files` ON files.id = documents.file
WHERE (MATCH (documents.title, documents.short_description, files.name, files.extracted_text) AGAINST ('+keyword*' IN BOOLEAN MODE))
ORDER BY `score` DESC)
) AS COUNT
如何删除它选择的所有额外列？
您忘记了代码的一部分，即构造$pages_total_count_select和$legal_resources_total_count_select的部分。
在构建这些列的过程中，您错过了一个参数，那就是您想要的列数组，如果您不给它它，它就会占用表。*.
因此，只需检查创建select的方式，然后添加一个带有空数组的参数。
更新：所以你做了：
$select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
在那之后，用Zend_Db_Table（此处为$this）重做->from()调用，您可以在此处指定列，因此可以指定一个空数组或唯一需要的数组：
$select = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->from($this, array(new Zend_Db_Expr('COUNT(documents.id) AS count')));