mysql - 我可以在单个 MySQL 命令中触发两个 select 语句并计算结果行数吗？

Question

这是我的代码，可以工作，但非常慢:

    $graded = R::getAll("SELECT posts.id, posts.discussion, rating.rating, rating.itemid
                             FROM uv_forum_posts posts
                             JOIN uv_rating rating ON ( posts.id = rating.itemid )
                             WHERE posts.discussion = :discussion_id
                             GROUP BY posts.userid",
                             array(':discussion_id' => $discussion['id']));

    $total = R::getAll("SELECT posts.userid
                        FROM uv_forum_posts posts
                        WHERE posts.discussion = :discussion_id
                        GROUP BY userid",
                        array(':discussion_id' => $discussion['id']));

    $percentages[] = count($graded) / count($total) * 100;

$graded 表示具有评级的所有行。

$total 代表所有用户参与，无论是否评分。

我只对结果集的数值感兴趣，我可以将这两个 MySQL 调用合并为一个返回两个数字 graded 和 total 的调用吗？

Answer 1

如果我正确理解您的要求，您可以尝试

SELECT p.userid,
       COUNT(*) total,
       COUNT(r.itemid) graded
  FROM uv_forum_posts p LEFT JOIN 
       uv_rating r ON p.id = r.itemid
 WHERE p.discussion = :discussion_id
 GROUP BY p.userid

输出:

| USERID | TOTAL | GRADED |
---------------------------
|      1 |     8 |      2 |
|      2 |     4 |      4 |

<强> SQLFiddle

更新:如果您只想要所有帖子和用户的总计，那么

SELECT COUNT(*) total,
       COUNT(r.itemid) graded
  FROM uv_forum_posts p LEFT JOIN 
       uv_rating r ON p.id = r.itemid
 WHERE p.discussion = 1

输出:

| TOTAL | GRADED |
------------------
|    12 |      6 |

<强> SQLFiddle