MySQL : Group count specific column per user?

Question

我希望使用 3 个表中的数据为每个特定用户计算列。

表 1(用户):

CREATE TABLE `datastore`.`users` ( `uid` INT NOT NULL AUTO_INCREMENT ,  `name` VARCHAR(30) NOT NULL DEFAULT 'john' ,  `class` VARCHAR(20) NOT NULL DEFAULT 'NEW' ,    PRIMARY KEY  (`uid`)) ENGINE = InnoDB;
INSERT INTO `users` (`uid`, `name`, `class`) VALUES (NULL, 'john', 'NEW'), (NULL, 'mark', 'OLD');

样本:

uid name    class
1   john    NEW
2   mark    OLD

表 2(数据):

CREATE TABLE `datastore`.`data` ( `id` INT NOT NULL AUTO_INCREMENT ,  `source` VARCHAR(30) NULL DEFAULT NULL ,  `destination` VARCHAR(30) NULL DEFAULT NULL ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
INSERT INTO `data` (`id`, `source`, `destination`) VALUES (NULL, 'NETWORK', 'SERVER_1'), (NULL, 'STATION', 'SERVER_2'), (NULL, 'DATASTORE', 'SERVER_1');

样本:

id  source  destination
1   NETWORK     SERVER_1
2   STATION     SERVER_2
3   DATASTORE   SERVER_1

表 3(访问):

CREATE TABLE `datastore`.`access` ( `id` INT(11) NOT NULL AUTO_INCREMENT ,  `uid` INT(11) NULL DEFAULT NULL ,  `source` VARCHAR(30) NULL DEFAULT NULL ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
INSERT INTO `access` (`id`, `uid`, `source`) VALUES (NULL, '1', 'NETWORK'), (NULL, '2', 'STATION'), (NULL, '1', 'STATION'), (NULL, '1', 'STATION');

样本:

id  uid source
1   1   NETWORK
2   2   STATION
3   1   STATION
4   1   STATION

到目前为止我尝试了什么:

SELECT access.uid, data.destination, COUNT(*) as count FROM data, access WHERE access.source = data.source GROUP BY destination, uid

结果:

uid destination count   
1   SERVER_1    1
1   SERVER_2    2
2   SERVER_2    1

我还要把它和用户名联系起来，

期望的结果:

uid name destination    count   
1   john SERVER_1       1
1   john SERVER_2       2
2   mark SERVER_2       1

Answer 1

看来你还需要用户加入

 SELECT access.uid
  , users.name
  , data.destination
  , COUNT(*) as count 
FROM data
INNER JOIN  access ON  access.source = data.source 
INNER JOIN users ON users.uid = access.uid
GROUP BY destination, uid, users.name

作为建议，您不应使用基于 where .. 的(旧)隐式连接语法，而应使用显式连接语法。