mysql - SUM(a*b) 是做什么的？

Question

我以前从未见过 SUM(a*b)。我希望有人能告诉我它的预期行为，以及它是否是 SQL 标准的一部分，或者是 MySQL 恰好实现的。一个例子:

localhost test> CREATE TABLE numbers (id int not null primary key auto_increment, a int, b int);
Query OK, 0 rows affected (0.03 sec)

localhost test> INSERT INTO numbers (a,b) VALUES (1, 2), (3,4),(1,1),(5,6);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

localhost test> select * FROM numbers GROUP BY id;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    2 |
|  2 |    3 |    4 |
|  3 |    1 |    1 |
|  4 |    5 |    6 |
+----+------+------+
4 rows in set (0.00 sec)

localhost test> select SUM(a), SUM(b) FROM numbers GROUP BY id;
+--------+--------+
| SUM(a) | SUM(b) |
+--------+--------+
|      1 |      2 |
|      3 |      4 |
|      1 |      1 |
|      5 |      6 |
+--------+--------+
4 rows in set (0.00 sec)

localhost test> select SUM(a*b), SUM(a), SUM(b) FROM numbers GROUP BY id;
+----------+--------+--------+
| SUM(a*b) | SUM(a) | SUM(b) |
+----------+--------+--------+
|        2 |      1 |      2 |
|       12 |      3 |      4 |
|        1 |      1 |      1 |
|       30 |      5 |      6 |
+----------+--------+--------+
4 rows in set (0.00 sec)

localhost test> alter table numbers add col2 int not null;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

localhost test> update numbers set col2 = id %2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4  Changed: 2  Warnings: 0

localhost test> select col2, SUM(a*b), SUM(a), SUM(b) FROM numbers GROUP BY col2;
+------+----------+--------+--------+
| col2 | SUM(a*b) | SUM(a) | SUM(b) |
+------+----------+--------+--------+
|    0 |       42 |      8 |     10 |
|    1 |        3 |      2 |      3 |
+------+----------+--------+--------+
2 rows in set (0.01 sec)

我本以为最后一行是 80 和 30，或者如果它使用 MySQL 选择一行的非标准行为(我启用了 SQL_MODE=ONLY_FULL_GROUP_BY)会产生错误。

Answer 1

表达式 SUM(a*b) 是完全有效的标准 SQL。它为每一行计算 a * b，并对每个组内的结果求和(如果未指定 GROUP BY，则对整个表求和)。

+----+------+------+------+-----+| id | col2 | a    | b    | a*b |+----+------+------+------+-----+|  1 |    1 |    1 |    2 |   2 |                  |  2 |    0 |    3 |    4 |  12 |  --+|  3 |    1 |    1 |    1 |   1 |    |---> 12 + 30 = 42|  4 |    0 |    5 |    6 |  30 |  --++----+------+------+------+-----|

它不与SUM(a) * SUM(b)相同。