mysql - 获取百分比和 group by 语句

Question

我正在使用连接查询来获取按标题分组的数据，其中标题存储在另一个具有用户 ID 的表中

所以查询如下

SELECT title, COUNT(title) as cnt FROM users_titles
JOIN users
ON users_titles.uid = users.uid
WHERE users.useractivated = 1
GROUP BY title

现在我想添加一个额外的列来显示每种类型的百分比，如下所示:

----------------------------------
| title   |   cnt   | percentage |
----------------------------------
| 0       | 23658   | 23.67%     |
----------------------------------
| 1       | 53658   | 43.67%     |
----------------------------------

像这样的东西。我怎样才能做到这一点？

Answer 1

您可以将此查询与计算用户总数的查询交叉连接，然后将两个计数相除以获得百分比:

SELECT     title, COUNT(title) AS cnt, COUNT(title) / total_count AS percentage
FROM       users_titles
JOIN       users ON users_titles.uid = users.uid
CROSS JOIN (SELECT COUNT(*) AS total_count 
            FROM   users) t 
WHERE      users.useractivated = 1
GROUP BY   title

注意:如果您想将数字表示为活跃用户的百分比，您还需要向内部查询添加一个 WHERE users.useractivated = 1 子句。