我正在尝试为以下结果创建 SQL 查询:

id, name,   Users in group, Messages in groups, Message type 1 in groups, Message type 2 in groups
1   Group1  4               3                   2                         1
2   Group2  3               2                   1                         1
3   Group1  1               1                   1                         0

我的数据库:

create database user_test;

use user_test;


CREATE TABLE `user` (
  `id`             BIGINT      NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `group_id`       BIGINT      NOT NULL,
  PRIMARY KEY (`id`)
);


CREATE TABLE `user_group` (
  `id`      BIGINT           NOT NULL AUTO_INCREMENT,
  `name`    VARCHAR(100)
            CHARACTER SET utf8
            COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `unique` (`name`) USING BTREE,
  PRIMARY KEY (`id`)
);


CREATE TABLE `message` (
  `id`               BIGINT   NOT NULL AUTO_INCREMENT,
  `user_id`           BIGINT   NOT NULL,
  `type`             INT      NOT NULL,
  `date`             DATETIME NOT NULL,
  UNIQUE KEY `unique` (`user_id`, `type`, `date`) USING BTREE,
  PRIMARY KEY (`id`)
);


ALTER TABLE user
    ADD CONSTRAINT user_group_c_fk FOREIGN KEY (group_id) REFERENCES user_group(id);


ALTER TABLE message
    ADD CONSTRAINT user_a_fk FOREIGN KEY (user_id) REFERENCES user(id);

和初始值:

INSERT INTO `user_group`
VALUES (1, "Group 1"),
       (2, "Group 2"),
       (3, "Group 3");

INSERT INTO `user`
VALUES (1, "User 1", 1),
       (2, "User 2", 1),
       (3, "User 3", 1),
       (4, "User 4", 1),
       (5, "User 5", 2),
       (6, "User 6", 2),
       (7, "User 7", 2),
       (8, "User 8", 3);

INSERT INTO `message`
VALUES (1, 1, 1, "2019-07-25 00:00:00"),
       (2, 1, 2, "2019-07-25 00:00:01"),
       (3, 1, 2, "2019-07-25 00:00:02"),
       (4, 5, 1, "2019-07-25 00:00:03"),
       (5, 6, 2, "2019-07-25 00:00:04"),
       (6, 8, 1, "2019-07-25 00:00:05");

我想知道是否有可能的结果。我正在尝试这样的事情:

use user_test;

select user_group.id, 
       user_group.name,
       count(user.id) As "Users in group",
       count(message.id) AS "Messages in groups",
       count(message.type = 1) AS "Message type1 in groups",
       count(message.type = 2) AS "Message type2 in groups"
FROM user_group
LEFT JOIN user ON user.group_id = user_group.id
LEFT JOIN message ON message.user_id = user.id
WHERE user_group.id = message.user_id
GROUP BY user_group.id;

但是，当然，这是完全错误的查询。我认为配置数据库在这种情况下无济于事，但它与整个系统的 split 很小，我无法修改。我想为后端端点创建一个通用查询 - 最好的解决方案是对所有结果进行一次查询。

最佳答案

您可以使用 count(distinct) 快速完成此操作:

SELECT ug.id, ug.name,
       count(distinct u.id) As "Users in group",
       count(distinct m.id) AS "Messages in groups",
       count(distinct case when m.type = 1 then m.id end) AS "Message type1 in groups",
       count(distinct case when m.type = 2 then m.id end) AS "Message type2 in groups"
FROM user_group ug  LEFT JOIN
     user u
     ON u.group_id = ug.id LEFT JOIN
     message m
     ON m.user_id = u.id
WHERE ug.id = m.user_id
GROUP BY ug.id;

您还可以通过在执行联接之前聚合来获得准确的计数。另请注意，我添加了表别名，以便查询更易于编写和阅读。

关于mysql - SQL - 来自 3 个表的结果，聚合函数，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/57197510/