基于两列分组Mysql选择查询

cld4siwp 于 2022-11-21 发布在 Mysql

关注(0)|答案(1)|浏览(130)

我有一个如下表Contrib

'Id ward_id  Goods   Amount
1.  2       beans   2 bags
2.  2       Maize   30 bags
3.  4       Beans   5 bags
4.  4      Maize   26 bags
5.  2      Beans   40 bags
6.  4      Maize  1 bags '

我创建了一个选择查询

Select ward_id,goods,sum(amount) as amount group by goods and ward_id;

查询不起作用
我想要的结果是
分摊

'Id ward_id  Goods   Amount
1.  2       beans   42 bags
2.  2       Maize   56 bags
3.  4       Beans   45 bags
4.  4      Maize   26 bags

我的问题在哪里

mysql

来源：https://stackoverflow.com/questions/61426778/mysql-select-query-with-group-based-on-two-column

1条答案

按热度按时间

ca1c2owp1#

您几乎是正确的，但 mysql 中正确的语法是

Select ward_id,goods,sum(amount) as amount 
FROM Contrib
group by goods,ward_id
ORDEr By ward_id;

中的每一个

- 模式（ MySQL v5.7 ） * *

CREATE TABLE Contrib (
  `Id` INTEGER,
  `ward_id` INTEGER,
  `Goods` VARCHAR(5),
  `Amount` INTEGER
);

INSERT INTO Contrib
  (`Id`, `ward_id`, `Goods`, `Amount`)
VALUES
  ('1', '2', 'beans', '2'),
  ('2', '2', 'Maize', '30'),
  ('3', '4', 'Beans', '5'),
  ('4', '4', 'Maize', '26'),
  ('5', '2', 'Beans', '40'),
  ('6', '4', 'Maize', '1');

格式

- 查询 #1 * *

Select ward_id,goods,sum(amount) as amount 
FROM Contrib
group by goods,ward_id
ORDEr By ward_id;

| ward_id | goods | amount |
| ------- | ----- | ------ |
| 2       | beans | 42     |
| 2       | Maize | 30     |
| 4       | Maize | 27     |
| 4       | Beans | 5      |

格式
View on DB Fiddle 的最大值

赞(0）回复(0）举报 2022-11-21

我来回答

基于两列分组Mysql选择查询

1条答案

相关问题

热门标签

最新问答