代码之家 › 专栏 › 技术社区 › wen tian

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

sql mysql

wen tian · 技术社区 · 6 年前

我想用mysql遍历 goods_id 在 goods 表,然后查询 order_goods 基于全部的表 古德萨德 你能解决这个问题吗?

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.创建订单货物并插入表格

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.查询订单货物表

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语句?

3 回复 | 直到 6 年前

Madhur Bhaiya 6 年前

首先,做一个 INNER JOIN 在两张桌子之间,使用 goods_id .
然后,做一个 grouping 属于 古德萨德 使用 GROUP BY 条款。
最后,使用聚合函数 SUM 函数,获取所需的和值和 Alias 它作为 num .

尝试以下查询:

SELECT g.goods_id, 
       g.goods_name, 
       SUM(og.goods_num) AS num 
FROM goods g 
JOIN order_goods og ON og.goods_id = g.goods_id 
GROUP BY g.goods_id, g.goods_name

El.Hum 6 年前

X 是您要分组的查询吗? SUM()
然后你加入 goods 从这个表中得到你需要的名字和身份证
这可能会更好地通过性能来完成分组

    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

Fahmi 6 年前

将内部联接和聚合与Group By一起使用:

http://sqlfiddle.com/#!9/ae4fd9/3

   select go.goods_id,g.goods_name,sum(goods_num) as num
from goods g inner join order_goods go
on g.goods_id=go.goods_id
group by g.goods_name,go.goods_id

输出:

goods_id    goods_name  num
1           Apple        3
3           Huawei       9
2           Xiaomi       4