如何在一个查询中“合并”多个结果？(How to “merge” multiple results in one query? (Or sum up))

如何在一个查询中“合并”多个结果？(How to “merge” multiple results in one query? (Or sum up))

目前我的表结构是：

CREATE TABLE `taxes` ( `id` int(8) NOT NULL, `time` datetime NOT NULL, `name` varchar(64) NOT NULL, `amount` decimal(10,2) NOT NULL );

如果我想在一个月内总结所有税款，我会使用：

SELECT SUM(`amount`) as `taxesThisMonth` FROM `taxes` WHERE `time` > '2014-10-01 00:00:00'

这很好用。 但有可能总结每个名字的所有税收吗？ Name条目可以在数据库中多次，如：

INSERT INTO `taxes` (`id`, `time`, `name`, `amount`) VALUES (1, '2014-10-29 08:59:51', 'Lukas M', '637687.80'), (2, '2014-10-29 07:39:50', 'Lukas M.', '430500.15'), (3, '2014-10-29 07:14:50', 'Simon F.', '511707.00'), (4, '2014-10-29 06:49:49', 'Alex B.', '140982.30');

所以我得到一个有3行的结果，Lukas M.被“合并”成一个结果？

非常感谢你的帮助！ 我很长时间没有使用MySQL。

currently my table structure is:

CREATE TABLE `taxes` ( `id` int(8) NOT NULL, `time` datetime NOT NULL, `name` varchar(64) NOT NULL, `amount` decimal(10,2) NOT NULL );

If I want to sum up all the taxes in one month I use:

SELECT SUM(`amount`) as `taxesThisMonth` FROM `taxes` WHERE `time` > '2014-10-01 00:00:00'

this works fine. But is it possible to sum up all taxes per name? The Name entry could be in the database mutiple times like:

INSERT INTO `taxes` (`id`, `time`, `name`, `amount`) VALUES (1, '2014-10-29 08:59:51', 'Lukas M', '637687.80'), (2, '2014-10-29 07:39:50', 'Lukas M.', '430500.15'), (3, '2014-10-29 07:14:50', 'Simon F.', '511707.00'), (4, '2014-10-29 06:49:49', 'Alex B.', '140982.30');

So that I get a result that has 3 rows and Lukas M. is "merged" into one result?

Thanks a lot for you help! I didn't use MySQL for a long time.

最满意答案

您需要使用group by来汇总每个组的数据，以便查询成为

SELECT name, SUM(`amount`) as `taxesThisMonth` FROM `taxes` WHERE `time` > '2014-10-01 00:00:00' group by name

You need to use group by to sum up data per group, so the query becomes

SELECT name, SUM(`amount`) as `taxesThisMonth` FROM `taxes` WHERE `time` > '2014-10-01 00:00:00' group by name