MySQL单次查询中的多个计数 [英] MySQL Multiple Counts in Single Query
本文介绍了MySQL单次查询中的多个计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在一个查询中COUNT两列,但结果是吐出相同的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单次查询中的多个计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文