Mysql - SUM 列 - 子查询返回多于 1 行

Question

我想计算一下我们成员(member)的工作时间，他们为我们提供了帮助。该表看起来像这样:

    CREATE TABLE `stunden` (
  `std_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `einsatz_id` int(11) NOT NULL,
  `stunden` decimal(5,1) NOT NULL DEFAULT '0.0',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`std_id`)
) ENGINE=InnoDB;

INSERT INTO stunden VALUES
(1,1,2,1.0,'2018-01-19 00:36:15'),
(2,2,2,1.0,'2018-01-19 00:36:15'),
(3,4,2,1.0,'2018-01-19 00:36:15'),
(4,1,1,2.0,'2018-01-19 00:36:37'),
(5,5,1,2.0,'2018-01-19 00:36:37'),
(6,1,2,2.5,'2018-01-20 12:00:36');

 mysql> select * from stunden;
+--------+---------+------------+---------+---------------------+
| std_id | user_id | einsatz_id | stunden | last_update         |
+--------+---------+------------+---------+---------------------+
|      1 |       1 |          2 |     1.0 | 2018-01-19 00:36:15 |
|      2 |       2 |          2 |     1.0 | 2018-01-19 00:36:15 |
|      3 |       4 |          2 |     1.0 | 2018-01-19 00:36:15 |
|      4 |       1 |          1 |     2.0 | 2018-01-19 00:36:37 |
|      5 |       5 |          1 |     2.0 | 2018-01-19 00:36:37 |
|      6 |       1 |          2 |     2.5 | 2018-01-20 12:00:36 |
+--------+---------+------------+---------+---------------------+
6 rows in set (0,00 sec)

您可以看到 user_id = 1 的成员在 einsatz_id=2 时可用两次，在 einsatz_id = 1 时可用一次。我需要的是每个 einsatz_id 和每个 user_id 的 stunden(stunden=小时)的总和。我尝试过以下查询。

SELECT 
(SELECT SUM(s.stunden) AS Zeit FROM stunden s GROUP BY s.user_id, s.einsatz_id) AS STUNDEN,
    m.nachname,
    m.vorname,
    a.einsatz
FROM
    stunden s,
    mitglieder m,
    arbeitseinsatz a
WHERE
    s.user_id = m.id
        AND s.einsatz_id = a.einsatz_id
GROUP BY s.user_id, s.einsatz_id
ORDER BY m.nachname , m.vorname ASC

它会导致以下错误消息:

"ERROR 1242 (21000): Subquery returns more than 1 row". 

如有任何帮助，我们将不胜感激。

Answer 1

如果这不是您想要的，请按照上述修改您的问题...

SELECT user_id, einsatz_id, SUM(stunden) total FROM stunden GROUP BY user_id, einsatz_id;
+---------+------------+-------+
| user_id | einsatz_id | total |
+---------+------------+-------+
|       1 |          1 |   2.0 |
|       1 |          2 |   3.5 |
|       2 |          2 |   1.0 |
|       4 |          2 |   1.0 |
|       5 |          1 |   2.0 |
+---------+------------+-------+