MySQL - 如何计算主查询的行数，忽略子查询行？

发布于 2024-11-26 12:31:54 字数 1027 浏览 1 评论 0原文

我使用以下 MySQL 返回帖子列表及其相应的评论。

    SELECT  *
    FROM    forum_qa
            JOIN user_profiles
              ON user_id = forum_qa_author_id
            LEFT JOIN (SELECT forum_cm_id,
                              forum_cm_author_id,
                              forum_qa_id_fk,
                              forum_cm_text,
                        FROM  forum_cm
                        JOIN  user_profiles
                          ON  user_id = forum_cm_author_id) AS c
              ON forum_qa_id = c.forum_qa_id_fk
    WHERE   forum_qa_parent_id  = $forum_qa_id

如果我运行

    $data['num_answers'] = $query->num_rows();

这允许我获取返回的行数并将数组传递到我的控制器和视图。

但这会返回所有行（帖子+评论）。因此，如果 1 个帖子有 10 条评论，则返回 10。

我怎样才能让这个查询只计算帖子的数量（即返回1）而不包括子查询？

每个帖子都有一个唯一的 ID 保存在 forum_qa.forum_qa_id 中

每个评论都有一个唯一的 ID 保存在 forum_cm.forum_cm_id 中。

感谢您的帮助 - 如果需要，将发布更多代码。

原文

I use the following MySQL to return a list of posts and their corresponding comments.

    SELECT  *
    FROM    forum_qa
            JOIN user_profiles
              ON user_id = forum_qa_author_id
            LEFT JOIN (SELECT forum_cm_id,
                              forum_cm_author_id,
                              forum_qa_id_fk,
                              forum_cm_text,
                        FROM  forum_cm
                        JOIN  user_profiles
                          ON  user_id = forum_cm_author_id) AS c
              ON forum_qa_id = c.forum_qa_id_fk
    WHERE   forum_qa_parent_id  = $forum_qa_id

If I run

    $data['num_answers'] = $query->num_rows();

This allows me to get the number of returned rows and pass the array to my controller and view.

But this is returning all rows (posts + comments). So if 1 post has 10 comments, it returns 10.

How could I have this query count only the number of posts (ie, returning 1) not including the subquery?

Each post has a unique id saved in forum_qa.forum_qa_id

Each comment has a unique id saved in forum_cm.forum_cm_id.

Thanks for helping -- will post more code if needed.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦罢 2024-12-03 12:31:54

不是最快的，但您在使用 GROUP BY 时不受限制：

SELECT  *, 
  (SELECT COUNT(*) FROM forum_qa WHERE forum_qa_parent_id  = $forum_qa_id) Cnt
    FROM    forum_qa
            JOIN user_profiles
              ON user_id = forum_qa_author_id
            LEFT JOIN (SELECT forum_cm_id,
                              forum_cm_author_id,
                              forum_qa_id_fk,
                              forum_cm_text,
                        FROM  forum_cm
                        JOIN  user_profiles
                          ON  user_id = forum_cm_author_id) AS c
              ON forum_qa_id = c.forum_qa_id_fk
    WHERE   forum_qa_parent_id  = $forum_qa_id

Not the fastest, but you are not restricted in using GROUP BY:

SELECT  *, 
  (SELECT COUNT(*) FROM forum_qa WHERE forum_qa_parent_id  = $forum_qa_id) Cnt
    FROM    forum_qa
            JOIN user_profiles
              ON user_id = forum_qa_author_id
            LEFT JOIN (SELECT forum_cm_id,
                              forum_cm_author_id,
                              forum_qa_id_fk,
                              forum_cm_text,
                        FROM  forum_cm
                        JOIN  user_profiles
                          ON  user_id = forum_cm_author_id) AS c
              ON forum_qa_id = c.forum_qa_id_fk
    WHERE   forum_qa_parent_id  = $forum_qa_id

回复收藏 0 原文

一桥轻雨一伞开 2024-12-03 12:31:54

您可以运行另一个查询或在结果集中再添加一列（带有独立子查询）：

SELECT *
     , ( SELECT COUNT(*)
         FROM forum_qa
         WHERE forum_qa_parent_id  = $forum_qa_id
       ) AS cntPosts
FROM    forum_qa
        JOIN user_profiles
          ON user_id = forum_qa_author_id
        LEFT JOIN (SELECT forum_cm_id,
                          forum_cm_author_id,
                          forum_qa_id_fk,
                          forum_cm_text,
                    FROM  forum_cm
                    JOIN  user_profiles
                      ON  user_id = forum_cm_author_id) AS c
          ON forum_qa_id = c.forum_qa_id_fk
WHERE   forum_qa_parent_id  = $forum_qa_id

You can run another query or add one more column (with an independent subquery) in the result set:

SELECT *
     , ( SELECT COUNT(*)
         FROM forum_qa
         WHERE forum_qa_parent_id  = $forum_qa_id
       ) AS cntPosts
FROM    forum_qa
        JOIN user_profiles
          ON user_id = forum_qa_author_id
        LEFT JOIN (SELECT forum_cm_id,
                          forum_cm_author_id,
                          forum_qa_id_fk,
                          forum_cm_text,
                    FROM  forum_cm
                    JOIN  user_profiles
                      ON  user_id = forum_cm_author_id) AS c
          ON forum_qa_id = c.forum_qa_id_fk
WHERE   forum_qa_parent_id  = $forum_qa_id

回复收藏 0 原文