mysql - 来自另一个表的SQL COUNT行

正在尝试计算与来自另一个表的特定行关联的行数

SELECT *
  FROM
     (
       (SELECT COUNT(*) totalusers
          FROM mox_admin
             , caspartition
         WHERE mox_admin.partitionid = caspartition.id
       )
     ) tita
     , caspartition
 ORDER
    BY caspartition.id DESC
 LIMIT 0,5

但是查询一直返回“moz_admin”表中的行总数
以下是我所做的，尽管我认为这不是最有效的方法

"SELECT * FROM caspartition ORDER BY caspartition.id DESC LIMIT 0, 5";
//execute the query then loop through
    while($partition_data = mysqli_fetch_array($result)) {
        $partition_id = $partition_data['id'];
    $subquery_sql = "SELECT COUNT(*) AS totalusers FROM moz_admin WHERE partitionid = '$partition_id'";
        $subquery_sql = mysqli_fetch_array(mysqli_query($conn, $subquery_sql));
        $no_of_users = $subquery_sql[totalusers];
    }

整个要点是使它成为一个单独的SQL查询，而不是为我循环通过的每一行查询表。
提前谢谢。

最佳答案

您应该将这些组合成一个查询。问题是：

SELECT cp.id, COUNT(a.partitionid) as cnt
FROM (SELECT *
      FROM caspartition
      ORDER BY caspartition.id DESC
      LIMIT 0, 5
     ) cp LEFT JOIN
     moz_admin a
     ON a.partitionid = cp.id
GROUP BY cp.id;

关于mysql - 来自另一个表的SQL COUNT行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44132545/