具有 Sum、Join 和 Group By 的 Mysql 子查询

Question

我的问题

如何让子查询和单个用户的总分相加？目前我只能获得第一个用户的标记，如何获得每个用户的标记？这是 fiddle http://sqlfiddle.com/#!9/d6d68/3

数据集...

SELECT * FROM positions;
+----+-----------+---------------+------------+---------------+---------------------+---------------------+
| id | code_name | display_name  | category   | default_merit | created_at          | updated_at          |
+----+-----------+---------------+------------+---------------+---------------------+---------------------+
|  1 | K01       | Chairman      | College    |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  2 | K02       | Vice Chairman | College    |             6 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  3 | K03       | Treasurer     | College    |             6 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  4 | K04       | Secretary     | College    |             6 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  5 | K05       | Committee     | College    |             4 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  6 | K06       | Participator  | College    |             1 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  7 | U01       | Chairman      | University |            10 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  8 | U02       | Vice Chairman | University |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
|  9 | U03       | Treasurer     | University |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
| 10 | U04       | Secretary     | University |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
| 11 | U05       | Committee     | University |             5 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
| 12 | U06       | Participator  | University |             2 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
+----+-----------+---------------+------------+---------------+---------------------+---------------------+

SELECT * FROM records;
+----+---------+-------------+--------+---------+---------------+-----------------+------------+------------+------------+---------------------+---------------------+
| id | user_id | position_id | finish | approve | recordable_id | recordable_type | deleted_at | created_by | approve_by | created_at          | updated_at          |
+----+---------+-------------+--------+---------+---------------+-----------------+------------+------------+------------+---------------------+---------------------+
|  1 |       1 |           1 |      1 |       1 |             1 | App\Event       | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
|  2 |       1 |           2 |      1 |       0 |             2 | App\Event       | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
|  3 |       2 |           2 |      1 |       0 |             2 | App\Event       | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
|  4 |       2 |           2 |      1 |       0 |             2 | App\Club        | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
|  5 |       2 |           2 |      1 |       0 |             1 | App\Sub_Event   | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
|  6 |       3 |           2 |      1 |       0 |             2 | App\Club        | NULL       |          1 |          1 | 2016-06-08 10:30:56 | 2016-06-08 10:30:56 |
|  7 |       2 |           3 |      1 |       0 |             2 | App\Club        | NULL       |          1 |          1 | 2016-06-08 10:30:56 | 2016-06-08 10:30:56 |
+----+---------+-------------+--------+---------+---------------+-----------------+------------+------------+------------+---------------------+---------------------+

SELECT * FROM users;
+----+----------------+--------+-------------------------+----------+----------------+------------+---------------------+---------------------+-----------+----------+--------+-----------+-------------+-----------+------------+
| id | name           | matrix | email                   | password | remember_token | deleted_at | created_at          | updated_at          | attention | resident | banned | meritbook | must_inlist | protected | last_login |
+----+----------------+--------+-------------------------+----------+----------------+------------+---------------------+---------------------+-----------+----------+--------+-----------+-------------+-----------+------------+
|  1 | Lim Yoong Kang | 47408  | yoongkang0122@gmail.com | Szha6... | NULL           | NULL       | 2016-06-08 10:30:53 | 2016-06-08 10:31:05 |         0 |        1 |      0 |         0 |           0 |         0 | NULL       |
|  2 | Admin John     | 00001  | idiot.yk@gmail.com      | HYSPq... | NULL           | NULL       | 2016-06-08 10:30:53 | 2016-06-08 10:30:53 |         1 |        1 |      0 |         0 |           0 |         0 | NULL       |
|  3 | Moderator Wong | 00002  | xuanyulao@gmail.com     | fvxWS... | NULL           | NULL       | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |         1 |        1 |      0 |         0 |           0 |         0 | NULL       |
|  4 | Student Tai    | 00003  | taileepeng@gmail.com    | ihdF6... | NULL           | NULL       | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |         1 |        1 |      0 |         0 |           0 |         0 | NULL       |
+----+----------------+--------+-------------------------+----------+----------------+------------+---------------------+---------------------+-----------+----------+--------+-----------+-------------+-----------+------------+

和想要的结果

+--------+----------------+-------+
| matrix | name           | total |
+--------+----------------+-------+
|      1 | Admin John     |     0 |
|      2 | Moderator Wong |     0 |
|      3 | Student Tai    |     0 |
|      4 | Lim Yoong Kang |     8 |
+--------+----------------+-------+

我目前使用的SQL查询

SELECT
  `users`.`matrix`,
  `users`.`name`,
  (SELECT SUM(positions.default_merit)
   FROM `users`
     INNER JOIN `records` ON `records`.`user_id` = `users`.`id`
     INNER JOIN `positions` ON `positions`.`id` = `records`.`position_id`
   WHERE `records`.`finish` = '1' AND `records`.`approve` = '1' AND `users`.`deleted_at` IS NULL) AS total
FROM `users`
GROUP BY `users`.`matrix`
HAVING total >= 0

LIMIT 100

Answer 1

我想到的是选择一个包含所有 default_merits 的表，这些表符合您的条件，然后按用户对其进行分组。这应该导致一个表，其中所有用户只有他们的 default_merits 的总和:

如果您然后加入所有用户的总数，您应该得到您正在寻找的东西。

 SELECT
  `users`.`matrix`,
  `users`.`name`,
  IFNULL(`totals`.`total`, 0) as `total`
FROM `users`
  LEFT JOIN (
  SELECT
    `users`.`matrix`,
    SUM(positions.default_merit) as `total`
  FROM `users`
  LEFT JOIN `records` ON `records`.`user_id` = `users`.`id`
  LEFT JOIN `positions` ON `positions`.`id` = `records`.`position_id`
WHERE `records`.`finish` = 1 AND `records`.`approve` = 1 AND `users`.`deleted_at` IS NULL
GROUP BY `users`.`matrix`) AS `totals` ON `users`.`matrix` = `totals`.`matrix`