单个查询中的 MySQL 多个计数

问题描述

限时删除！！

我试图在单个查询中对两列进行计数，但结果为 medcount 和 uploadcount 吐出相同的值.有什么建议吗?

I am trying to COUNT two columns in a single query, but the results are spitting out the same values for medcount and uploadcount. Any suggestions?

SELECT * , COUNT($tbl_list.listname) AS listcount,
                    COUNT($tbl_uploads.id) AS uploadcount
        FROM $tbl_members
        LEFT JOIN $tbl_list ON $tbl_members.username = $tbl_list.username
        LEFT JOIN $tbl_uploads ON $tbl_members.username = $tbl_uploads.username
        GROUP BY $tbl_members.username
        ORDER BY $tbl_members.lastname, $tbl_members.firstname;

推荐答案

使用:

   SELECT tm.*,
          x.listcount,
          y.uploadcount
     FROM $tbl_members tm
LEFT JOIN (SELECT tl.username,
                  COUNT(tl.listname) AS listcount
             FROM $tbl_list tl
         GROUP BY tl.username) x ON x.username = tm.username
LEFT JOIN (SELECT tu.username,
                  COUNT(tu.id) AS uploadcount
             FROM $tbl_uploads tu
         GROUP BY tu.username) y ON y.username = tm.username
 GROUP BY tm.username
 ORDER BY tm.lastname, tm.firstname

这篇关于单个查询中的 MySQL 多个计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..