如果没有匹配返回0,MYSQL按日期选择SUM()

I have connected three tables to each other. I'm querying keywords + statistics.

我把三张桌子连在一起了。我在查询关键词+统计数据。

I need to get data out by date range, and if there is no data for that specific date range i would like it to return 0 statistics

我需要按日期范围获取数据，如果没有特定日期范围的数据，我希望它返回0个统计信息

Lets say my query is:
SELECT keyword,SUM(stat) FROM keywords WHERE date >='2012-07-10' GROUP BY keyword;

It would return
|my keyword 1|3|
|my keyword 2|6|

Example table content:
| id | keyword          | stat | date            | keywordGroup |
| 1  | my keyword 1 | 2     | 2012-07-11 | 1 |
| 2  | my keyword 1 | 1     | 2012-07-12 | 1 |
| 3  | my keyword 2 | 6     | 2012-07-11 | 1 |
| 4  | my keyword 3 | 10   | 2012-07-09 | 1 |

But there are some keywords which have no stats for that date range
but I would still like to get those keywords showing 0 as stats.

Like this:

假设我的查询是:从日期>='2012-07-10' GROUP BY关键字中选择关键字SUM(stat);它将返回我的关键字1 | 3 | | |我的关键字2 | 6 |示例表内容:| | | id关键字统计| |日期keywordGroup | | 1 |我的关键字1 | 2 | 2012-07-11 | 1 | | 2 |我的关键字1 | 1 | 2012-07-12 | 1 | | 3 |我的关键字2 | 6 | 2012-07-11 | 1 | | 4 |我的关键字3 | 10 | 2012-07-09 | 1 |但是有一些关键词没有统计的日期范围,但我仍然想让这些关键字显示0作为统计数据。是这样的:

|my keyword 1|3|
|my keyword 2|6|
|my keyword 3|0|

The problem is that the where clause blocks values that are not found, is there a workaround.
As I said I have three tables with relations, I use LEFT JOIN to query data, for simplification I left that part out of my example query.

|我的关键字1|3| |我的关键字2|6| | |我的关键字3|0|问题是where子句阻塞未找到的值，是否存在一个变通方法。正如我说过的，我有三个具有关系的表，我使用左连接来查询数据，为了简化，我在示例查询中省略了这一部分。

Table: Campaigns
id
name

表:活动id名称

Table: KeywordGroup
id
name
campaign

表:KeywordGroup id名称战役

Table: KeywordData
id
keyword
stat
date
keywordGroup

表:关键字数据id关键字统计日期关键字组

3 个解决方案

#1

Assuming you are joing tables with left joins correctly, use:

假设您正在正确地使用左连接joing表，请使用:

select keyword, ifnull(sum(stat),0) ... group by keyword

If it is WHERE clause that filters out some stats before even grouping is applied, then you need to move the date condition into LEFT JOIN condition, ie.

如果是WHERE子句在使用分组之前过滤掉一些统计信息，那么您需要将日期条件移动到左连接状态(ie)。

from KeywordGroup left join KeywordData on ... and date > '2012-07-10'

But I cannot help further without seeing actual queries. :-)

但是，如果没有看到实际的查询，我就不能更进一步。:-)

#2

Try this

试试这个

select 
    t1.keyword,
    sum(t2.stat) as stat
from
(   
    select distinct keyword from keywords   
) as t1 left join
(
    SELECT keyword,SUM(stat) FROM keywords WHERE date >='2012-07-10' GROUP BY keyword
) as t2
on t1.keyword=t2.keyword;

#3

Have you looked at a left outer join? Here's a good reference for the join types:

你看过左外连接了吗?以下是连接类型的一个很好的参考:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

"To produce the set of records only in Table A, but not in Table B, 
we perform a left outer join, then exclude the records we don't want 
from the right side via a where clause."

#1