主页 / dba / 问题 / 211185
Accepted
pbarney
Asked: 2018-07-03 12:06:44 +0800 CST

关于分组和计数的基本问题

  • 772

我在 MySQL 中提出查询时遇到问题。

假设我们有一个记录用户操作的表。

  • 用户 1 是一名医生，并且被记录了多次。
  • 用户 2 是一名医生并被登录一次。
  • 用户 3 是一名会计师，被记录了两次。
  • User4 是职员，尚未登录。
  • User5 是一名会计师，尚未登录。

我想知道什么“角色”（即医生、会计师、文员）在日志文件中有条目。

这是架构：

CREATE TABLE `test1_users` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `role` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `test1_log` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

和测试数据：

INSERT INTO `test1_users` VALUES
(1, 'Franklin', 'Doctor'),
(2, 'Sally', 'Doctor'),
(3, 'Linus', 'Accountant'),
(4, 'Patty', 'Clerk'),
(5, 'Marcie', 'Accountant');

INSERT INTO `test1_log` VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 1),
(5, 3),
(6, 3);

如果我想查看某个用户登录了多少次，这非常简单。

SELECT u.name, COUNT(*) AS total
FROM test1_log l
LEFT JOIN test1_users u ON u.id = l.user_id
GROUP BY user_id;

查询产生以下内容：

+----------+-------+
| name     | total |
+----------+-------+
| Franklin |     3 |
| Sally    |     1 |
| Linus    |     2 |
+----------+-------+

但是，如果我想查看记录了多少不同的“角色”，我将如何构建查询？不是每个角色被记录了多少次，只是记录了所有角色的列表。

具体来说，记录了多少个不同的医生？我不在乎每个记录了多少次。

我正在寻找的此类查询会产生以下输出：

+------------+-------+
| role       | total |
+------------+-------+
| Doctor     |     2 |
| Accountant |     1 |
| Clerk      |     0 |
+------------+-------+

两位医生都有记录，但只有一名会计师，没有文员。

这是我最接近的：

SELECT role, COUNT(l.id) AS total FROM test1_users u 
LEFT JOIN test1_log l ON u.id = l.user_id 
GROUP BY role;

但这只给我日志条目的数量，我需要进入日志的角色数量。

想法？

谢谢。

mysql group-by

2 个回答

  1. Best Answer

    通过阅读您的问题，您需要已记录的不同用户的数量，并按每个用户的角色进行细分。

    您的查询返回按角色细分的日志条目数。

    为了获得不同的用户，我们需要在代表用户的COUNT(DISTINCT x)某个列上获得一个。x

    如果我们使用来自 的列test1_users，我们只会获得分配给每个角色的用户数，而不会参考用户是否在日志中有关联条目。

    如果我们使用INNER JOIN，我们将只计算日志中确实有条目的用户 - 但我们不会看到日志中不存在用户的任何角色（“Clerk”也是如此）。

    但是，其中有一个test1_log特定于用户的列 - test1_log.user_id。

    因此，如果我们将查询修改为：

    SELECT role, COUNT(DISTINCT l.user_id) AS total FROM test1_users u 
LEFT JOIN test1_log l ON u.id = l.user_id 
GROUP BY role;

    我们得到了你请求的结果（正如你在这个SQLFiddle中看到的那样） 。

    • 2

  2. 您想知道在给定时期内记录的不同角色的数量吗？

    SELECT
  COUNT(DISTINCT U.Role) AS NumRolesRecorded
FROM
  test1_log AS Lg
  INNER JOIN test1_users AS U ON Lg.User_ID = U.ID

    结果应该是“2”。如果您需要更多详细信息，请在 Stack Exchange 上解决COUNT DISTINCTMySQL的使用问题。

    • 0

相关问题