如何使用单条查询统计两表指定行数优化现有多查询PHP代码

API 探路者

2026-9-30

单SQL合并统计方案

核心SQL语句

可以通过条件聚合+子查询的方式，把4次查询合并为1次，两种常用写法均可：

写法1（条件聚合，性能更优）

仅扫描一次edev_useri表即可得到所有同表统计值：

SELECT 
  (SELECT COUNT(*) FROM edev_useri) AS useri,
  COUNT(CASE WHEN groupid = 6 THEN 1 END) AS dev,
  COUNT(CASE WHEN groupid = 7 THEN 1 END) AS ben,
  (SELECT COUNT(*) FROM edev_cereri) AS cereri
FROM edev_useri

写法2（全子查询，逻辑更直观）

无需依赖外层表，即使edev_useri是空表也能正常返回结果：

SELECT
  (SELECT COUNT(*) FROM edev_useri) AS useri,
  (SELECT COUNT(*) FROM edev_useri WHERE groupid = 6) AS dev,
  (SELECT COUNT(*) FROM edev_useri WHERE groupid = 7) AS ben,
  (SELECT COUNT(*) FROM edev_cereri) AS cereri

修改后的PHP代码

替换原有多查询逻辑即可，输出结构和原有逻辑完全一致：

// 选择上述任意一种SQL语句执行即可
$res = $db->query("
SELECT
  (SELECT COUNT(*) FROM edev_useri) AS useri,
  (SELECT COUNT(*) FROM edev_useri WHERE groupid = 6) AS dev,
  (SELECT COUNT(*) FROM edev_useri WHERE groupid = 7) AS ben,
  (SELECT COUNT(*) FROM edev_cereri) AS cereri
");
// 根据实际使用的数据库操作类调整取单行数据的方法，fetchAssoc/fetchRow等均可
$stats = $db->fetchAssoc($res);

$dashStats = [
    'useri' => (int)$stats['useri'],
    'dev' => (int)$stats['dev'],
    'ben' => (int)$stats['ben'],
    'cereri' => (int)$stats['cereri']
];
$json_data = ['dashStats' => [$dashStats]];
echo json_encode($json_data);