mysql - 我如何离开加入子查询

Question

我有这样的数据，我正在尝试获取一个用户列表，其中包含完成的独特事件的总数以及完成的独特事件的所有点数的总和，所以如果例如用户重复事件 1 3 次，它将算作 100 加上任何其他事件等。

我试过了，但是好像不行

SELECT *,
  ( SELECT COUNT(*) 
    FROM done 
     WHERE done.user_id = user.id 
  ) as activity_count
FROM
  user AS user
LEFT JOIN 
  (
    SELECT SUM(points), d.user_id
    FROM done AS d
    LEFT JOIN activity AS a
      ON d.activity_id = a.id
    GROUP BY user_id
  ) AS subtable 
ON  subtable.user_id = user.id

我的数据模式是这样的:

CREATE TABLE IF NOT EXISTS `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));
CREATE TABLE IF NOT EXISTS `activity` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `points` INT UNSIGNED NULL,
  PRIMARY KEY (`id`));
CREATE TABLE IF NOT EXISTS `done` (
  `id` INT NOT NULL,
  `user_id` INT UNSIGNED NULL,
  `activity_id` INT UNSIGNED NULL,
  `status` INT UNSIGNED NULL,
  PRIMARY KEY (`id`));

INSERT INTO user
  (`id`, `name`)
  VALUES
   (1, 'User 1'),
   (2, 'User 2'),
   (3, 'User 3'),
   (4, 'User 4');
INSERT INTO activity
   (`id`, `name`, `points`)
  VALUES
   (1, 'activity 1', 100), 
   (2, 'activity 2', 200),
   (3, 'activity 3', 300),
   (4, 'activity 4', 400);
INSERT INTO done
  (`id`, `user_id`, `activity_id`, `status`)
  VALUES
    (1, 1, 1, 2),
    (2, 2, 1, 3),
    (3, 1, 2, 2),
    (4, 3, 1, 1),
    (5, 2, 1, 3),
    (6, 4, 4, 2),
    (7, 3, 1, 1),
    (8, 1, 1, 3);

预期结果:

user_id,   count_of_unique_activities_done, sum_of_unique_activity_points
1,   2,  300;
2,   1,  100;
3,   1,  100;
4,   1,  400;

在完成表中添加了一个新行以帮助说明我需要什么。

Answer 1

您的查询可以简化为

SELECT u.id, SUM(a.points), count(a.*)
FROM `user` u
LEFT JOIN done d ON done.user_id = u.id
LEFT JOIN activity a ON d.activity_id = a.id
GROUP BY u.id