mysql - 如何使用mysql的sum()函数计算每一行的总和？

Question

我想用mysql遍历goods表中的所有goods_id，然后查询order_goods中每一行的总和基于所有goods_id的表格。你能解决这个问题吗？

1.创建商品表和插入表

CREATE TABLE `goods` (
`goods_id` int(11) NOT NULL,
`goods_name` varchar(255) NOT NULL,
 PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `goods` (`goods_id`, `goods_name`) VALUES
(1, 'Apple'),
(2, 'Xiaomi'),
(3, 'Huawei');

2.创建order_goods并插入表

CREATE TABLE `order_goods` (
`id` int(11) NOT NULL,
`order_id` int(11) NOT NULL,
`goods_id` int(11) NOT NULL,
`goods_name` varchar(255) NOT NULL,
`goods_num` int(11) NOT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `order_goods` (`id`, `order_id`, `goods_id`, `goods_name`, 
`goods_num`) VALUES
(1, 1, 1, 'iPhone X', 2),
(2, 2, 1, 'iPhone 8 plus', 1),
(3, 3, 2, 'Y69A', 1),
(4, 4, 2, 'X21', 3),
(5, 5, 3, 'nova 3', 1),
(6, 6, 3, 'P20 Pro', 3),
(7, 7, 3, 'Mate 10 Pro', 5);

3.查询商品表

mysql> select * from goods;
+----------+------------+
| goods_id | goods_name |
+----------+------------+
|        1 | Apple      |
|        2 | Xiaomi     |
|        3 | Huawei     |
+----------+------------+
3 rows in set (0.00 sec)

4.查询order_goods表

mysql> select * from order_goods;
+----+----------+----------+---------------+-----------+
| id | order_id | goods_id | goods_name    | goods_num |
+----+----------+----------+---------------+-----------+
|  1 |        1 |        1 | iPhone X      |         2 |
|  2 |        2 |        1 | iPhone 8 plus |         1 |
|  3 |        3 |        2 | Y69A          |         1 |
|  4 |        4 |        2 | X21           |         3 |
|  5 |        5 |        3 | nova 3        |         1 |
|  6 |        6 |        3 | P20 Pro       |         3 |
|  7 |        7 |        3 | Mate 10 Pro   |         5 |
+----+----------+----------+---------------+-----------+
7 rows in set (0.00 sec)

5.我想试试这个效果!

+----+----------+----------+---------------+
| id | goods_id | goods_name    | num      |
+----+----------+----------+---------------+
|  1 |        1 | Apple         |   3      |
|  2 |        2 | Xiaomi        |   4      |
|  3 |        3 | Huawei        |   9      |
+----+----------+----------+---------------+

例如，写sum(g.goods_num) as num

上面的sql语句怎么写？

Answer 1

X 是你分组依据的查询 SUM()
然后你将它与 goods 结合起来，以获取名称和 ID 以及你需要从该表中获取的任何内容
这可能会更好地执行 group by first

    WITH X AS
   (
    SELECT goods_id,SUM(goods_num) AS num
    FROM order_goods
    GROUP BY goods_id
    )

    SELECT G.goods_name , X.num 
    FROM X
    INNER JOIN goods G  ON G.goods_id = X.goods_id

没有 WITH 子句，所以将您的连接加入您的查询像这样:

SELECT goods_id,SUM(goods_num) AS num
   FROM order_goods OG
   INNER JOIN goods G  ON G.goods_id = OG.goods_id
   GROUP BY OG.goods_id    -- or G.goods_name