php - 根据用户使用 groupby 或 unionall 对表中的两列进行计数

Question

我想对同一个表中的单个查询中的两列进行计数。

1- 关注者数量

2-关注数

我使用 union 和 groupby 来获取两列的计数，但只能从查询中获取一列的计数。

我在下面使用的查询:

SELECT follow_attr.username, follow_attr.followerid, follow_attr.followeeid, follow_attr.followercount, follow_attr.followeecount 
 FROM (
    SELECT u.username, dff.followerid AS followerid, dff.followeeid AS followeeid, count(dff.followerid) AS followercount, count(dff.followeeid) AS followeecount
FROM tablename AS dff
LEFT JOIN user AS u ON u.userid = dff.followerid
GROUP BY dff.followerid
UNION ALL
SELECT u.username, dffs.followerid AS followerid, dffs.followeeid AS followeeid, count(dffs.followerid) AS followercount, count(dffs.followeeid) AS followeecount
FROM tablename AS dffs
LEFT JOIN user AS u ON u.userid = dffs.followeeid
GROUP BY dffs.followeeid
   ) AS follow_attr
WHERE follow_attr.username IS NOT NULL
GROUP BY follow_attr.followerid, follow_attr.followeeid
ORDER BY RAND()
LIMIT 0,6

我无法在此查询中获得 followeecount。

让我知道该过程出了什么问题。

Answer 1

这样做SELECT COUNT(followercount) AS f_count, COUNT(followeecount) AS fe_count FROM 表名