如何在带多个AND条件的SELECT语句中实现多计数查询

问题根源 #

• 参数绑定顺序错误：PDO的参数绑定是按占位符在SQL中出现的先后顺序匹配数组元素，但你提供的绑定数组顺序和SQL中占位符出现顺序完全不匹配，导致参数绑定失败，查询直接返回false。
• 主查询可能无结果：主查询FROM table AS a WHERE a.quem = :id5如果没有匹配的记录，整个查询结果为空，fetch()会返回false。
• 子查询冗余：多个子查询重复扫描同一张表，效率低下。

修复与优化方案 #

1. 修正参数绑定顺序（基础修复） #

按SQL中占位符出现的顺序重新排列绑定数组，推荐用关联数组绑定，彻底避免顺序错误：

public function get(int $id, int $count)
{
    $id5 = $id4 = $id3 = $id2 = $id1 = $id;
    $count4 = $count3 = $count2 = $count1 = $count;
    $sql = "SELECT a.id AS rib_id, 
    (SELECT COUNT(id) FROM table WHERE table.quem = :id AND table.count = :count) AS Tot,
    (SELECT COUNT(id) FROM table WHERE table.quem = :id1 AND (table.status = 'Ag' AND table.count = :count1)) AS Tot1,
    (SELECT COUNT(id) FROM table WHERE table.quem = :id2 AND (table.status = 'Ap' AND table.count = :count2)) AS Tot2 ,
    (SELECT COUNT(id)   FROM table WHERE table.quem = :id3 AND (table.status = 'ComD' AND table.count = :count3)) AS Tot3,
    (SELECT COUNT(id)   FROM table WHERE table.quem = :id4 AND (table.status = 'Re' AND table.count = :count4)) AS Tot4 
    FROM table AS a
    WHERE a.quem = :id5 ";  

    // 用关联数组绑定参数，完全匹配占位符名称
    $params = [
        ':id' => $id,
        ':count' => $count,
        ':id1' => $id1,
        ':count1' => $count1,
        ':id2' => $id2,
        ':count2' => $count2,
        ':id3' => $id3,
        ':count3' => $count3,
        ':id4' => $id4,
        ':count4' => $count4,
        ':id5' => $id5
    ];
    
    $result = $this->db->runSQL($sql, $params)->fetch();
    // 确保返回数组而非false
    return $result ?: [];
}

2. 避免主查询无结果的情况 #

如果不需要从table获取rib_id，可以直接用固定值替代主查询的表查询，确保始终有一条结果：

SELECT 1 AS rib_id,
(SELECT COUNT(id) FROM table WHERE table.quem = :id AND table.count = :count) AS Tot,
-- 其他子查询...

3. 条件聚合优化（推荐方案） #

把多个子查询合并为一次表扫描，大幅提升效率，同时避免重复参数：

public function get(int $id, int $count)
{
    $sql = "SELECT 
        1 AS rib_id,
        COUNT(CASE WHEN quem = :id AND count = :count THEN id END) AS Tot,
        COUNT(CASE WHEN quem = :id AND status = 'Ag' AND count = :count THEN id END) AS Tot1,
        COUNT(CASE WHEN quem = :id AND status = 'Ap' AND count = :count THEN id END) AS Tot2,
        COUNT(CASE WHEN quem = :id AND status = 'ComD' AND count = :count THEN id END) AS Tot3,
        COUNT(CASE WHEN quem = :id AND status = 'Re' AND count = :count THEN id END) AS Tot4
        FROM table";

    $params = [':id' => $id, ':count' => $count];
    $result = $this->db->runSQL($sql, $params)->fetch();
    
    return $result ?: [];
}

内容的提问来源于stack exchange，提问作者Jeferson