MySQL:一个 SQL 语句中多个条件的总和列表 [英] MySQL: list of sum for several conditions in one SQL statement
问题描述
我的 MySQL 语句有问题:
I have a problem with a MySQL statement:
select sum(x) as "sum", "01.06.2010" as "date" from (
select distinct a.id as ID, a.dur as x from b_c
inner join c on b_c.c_id = c.id
inner join a on c.id = a.c_id
inner join a_au on a.id = a_aud.id
inner join d on a_au.rev = d.rev
where
b_c.b_id = 30 and
a_au.stat != 1 and
DATE(FROM_UNIXTIME(rtime)) = DATE('2010-06-01')
) AS SubSelectTable;
此语句返回一行,其中包含sum"和date"列.
This statement returns one row with the colums "sum" and "date".
现在我不仅有一个日期('2010-06-01'),我有很多日期我必须得到它的 sum(),但不是在一行中.我希望每天有一个结果行.
Now I have not only one date ('2010-06-01'), I have many dates I have to get the sum() of it, but not in one single row. I want to have per day one result row.
例如,上面的语句返回了我
So, for example, the statement above returns me
sum date
-------------------
20 01.06.2010
现在我有日期 2010-06-01, 2010-06-02, 2010-06-03, ...我能做的是为每个日期编写一个更改日期的 sql 语句.所以如果我有 10 个日期,我将有 10 条数据库的 sql 语句.但是我确实想要一个 SQL 语句,结果不应该是所有给定日期的总和,我希望每个日期有一个结果行.所以结果应该是这样的:
Now I have the dates 2010-06-01, 2010-06-02, 2010-06-03, ... What I can do is to write for every date one sql statement changing the DATE. So if I have 10 dates, I will have 10 sql statements for the database. But I did want it with one SQL statement, and the result should not be the sum of all given dates, I want to have one result row per date. So the result should like that:
sum date
-------------------
20 01.06.2010
133 02.06.2010
19 03.06.2010
88 04.06.2010
... ...
我该怎么做?有人知道吗?
How can I do this? Does anyone know?
提前致谢&最好的问候.
Thanks a lot in advance & Best Regards.
推荐答案
使用 mysql GROUP BY 命令.完全未经测试的代码示例:
Use the mysql GROUP BY command. Completely untested code example:
select sum(x) as "sum", date as "date" from (
select distinct a.id as ID, a.dur as x, DATE(FROM_UNIXTIME(rtime)) as date from b_c
inner join c on b_c.c_id = c.id
inner join a on c.id = a.c_id
inner join a_au on a.id = a_aud.id
inner join d on a_au.rev = d.rev
where
b_c.b_id = 30 and
a_au.stat != 1
) AS SubSelectTable
group by date;
见:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
这篇关于MySQL:一个 SQL 语句中多个条件的总和列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!