mysql - 计算大型 mysql 查询中的行数 (zend)

Question

我在 zend 中开发并且有一个相当大的 mysql 查询。查询工作正常，我得到了我期望的列表。我正在使用 Select->Where... 执行此操作。下面是查询。

SELECT DISTINCT `d`.* FROM `deliverable` AS `d` INNER JOIN `groups` AS `g1` ON d.id = g1.deliverable_id INNER JOIN `groupmembers` AS `gm1` ON g1.id = gm1.group_id LEFT JOIN `connection` AS `c` ON d.id = c.downstreamnode_id LEFT JOIN `deliverable` AS `d1` ON c.upstreamnode_id = d1.id INNER JOIN `deliverable` AS `d2` ON CASE WHEN d1.id IS NULL THEN d.id ELSE d1.id END = d2.id INNER JOIN `groups` AS `g` ON d2.id = g.deliverable_id INNER JOIN `groupmembers` AS `gm` ON g.id = gm.group_id WHERE (g1.group_type = 100) AND (gm1.member_id = 1) AND (c.downstreamnode_id IS NULL OR d.restrict_access = 1) AND (g.group_type = 100 OR g.group_type = 110) AND (gm.member_id = 1) AND (d.deliverable_type = 110 OR d.deliverable_type = 100) GROUP BY CASE WHEN c.downstreamnode_id IS NULL THEN d.id ELSE c.downstreamnode_id END

唯一的问题是，当我尝试对 mysql 查询中的行进行计数时，我只返回 1。下面是查询

SELECT DISTINCT count(*) AS `rowCount` FROM `deliverable` AS `d` INNER JOIN `groups` AS `g1` ON d.id = g1.deliverable_id INNER JOIN `groupmembers` AS `gm1` ON g1.id = gm1.group_id LEFT JOIN `connection` AS `c` ON d.id = c.downstreamnode_id LEFT JOIN `deliverable` AS `d1` ON c.upstreamnode_id = d1.id INNER JOIN `deliverable` AS `d2` ON CASE WHEN d1.id IS NULL THEN d.id ELSE d1.id END = d2.id INNER JOIN `groups` AS `g` ON d2.id = g.deliverable_id INNER JOIN `groupmembers` AS `gm` ON g.id = gm.group_id WHERE (g1.group_type = 100) AND (gm1.member_id = 1) AND (c.downstreamnode_id IS NULL OR d.restrict_access = 1) AND (g.group_type = 100 OR g.group_type = 110) AND (gm.member_id = 1) AND (d.deliverable_type = 110 OR d.deliverable_type = 100) GROUP BY CASE WHEN c.downstreamnode_id IS NULL THEN d.id ELSE c.downstreamnode_id END

我通过使用生成第一个查询的相同“选择”来生成此查询，但我重置了列并添加了计数。

$this->getAdapter()->setFetchMode(Zend_Db::FETCH_ASSOC);
    $select
        ->reset( Zend_Db_Select::COLUMNS)
        ->columns(array('count('.$column.') as rowCount'));
    $rowCount = $this->getAdapter()->fetchOne($select);

此方法适用于我所有其他查询，只有这个我遇到了问题。我怀疑它与我在那里的“CASE”有关，但这很奇怪，因为我在第一个查询中得到了正确的行。有任何想法吗。谢谢。

仅供引用，下面是我成功处理的两个查询。

SELECT DISTINCT `po`.* FROM `post` AS `po` INNER JOIN `postinfo` AS `p` ON po.postinfo_id = p.id WHERE (p.creator_id = 1) ORDER BY `p`.`date_created` DESC

SELECT DISTINCT count(*) AS `rowCount` FROM `post` AS `po` INNER JOIN `postinfo` AS `p` ON po.postinfo_id = p.id WHERE (p.creator_id = 1) ORDER BY `p`.`date_created` DESC

在这个查询中，我在第一个查询中返回了 4 行，在第二个查询中返回了“int 4”。有谁知道为什么它不适用于大查询？

Answer 1

移动你的DISTINCT。

SELECT COUNT(DISTINCT `po`.*) AS `rowCount` ...