即使在SQL SELECT语句的表中不存在日期 [英] Get date even if it doesn't exist in table from SQL SELECT statement
问题描述
| ---- DATE ---- | --- ALARM_ID --- | --- COUNTER --- |
| 2012-01-01 | 1 | 32 |
| 2012-01-01 | 2 | 28 |
| 2012-01-02 | 1 | 12 |
| 2012-01-02 | 2 | 23 |
| 2012-01-03 | 1 | 3 |
| 2012-01-03 | 2 | 9 |
| 2012-01-05 | 1 | 8 |
| 2012-01-05 | 2 | 1 |
| 2012-01-07 | 1 | 102 |
| 2012-01-07 | 2 | 78 |注意日期（2012-01-03 - 2012-01-05）和（2012年之间的差距）
-01-05 - 2012-01-07）。在这些日期，没有任何数据，因为我的程序正在监视的系统在该日期没有报告任何错误。我正在寻找的是一个SQL SELECT查询，返回每个日期的错误总数，例如：
---- DATE ---- | --- COUNTER --- |
| 2012-01-01 | 60 |
| 2012-01-02 | 35 |
| 2012-01-03 | 12 |
| 2012-01-04 | 0 |
| 2012-01-05 | 9 |
| 2012-01-06 | 0 |
| 2012-01-07 | 180 |
我有一个查询返回ID，即使它们不存在于表中，如果ID不存在，返回ID的COUNTER值为0.这样：
在
$之前b $ b | --- ID --- | --- COUNTER --- | | --- ID --- | --- COUNTER --- |
| 1 | 2 | | 1 | 2 |
| 2 | 6 | | 2 | 6 |
| 3 | 1 | - > | 3 | 1 |
| 5 | 9 | | 4 | 0 |
| 6 | 10 | | 5 | 9 |
| 6 | 10 |
| 7 | 0 |
| 8 | 0 |
查询如下：
select t.num as ID，coalesce（yt.COUNTER，0）
from all_stats yt right join
（select t1.num + t2.num * 10 + t3 .num * 100 + t4.num * 1000作为num
从（select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0） t1 cross join
（select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0）t2 cross join
（select 1作为num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0）t3 cross join
（select 1 as num union select 2 union select 3 union select 4联合选择5联合选择6联合选择7联合选择8联合选择9联合选择0）t4）
t on yt.ID = t.num
其中（t.num between（select min（I D）from all_stats）和（从all_stats选择max（ID）））按ID
我可以不知道如何在日期时更改此查询。有人可以帮助我解决这个问题吗？
我正在使用MySQL
提前感谢Steve- O
具体细节将取决于DBMS，以及数据库的性质（例如，OLAP导向以OLTP为导向），但一种常见的一般方法是创建一个辅助日历表，表示日期为维度。然后，您可以使用常规的 JOIN s，而不必使用复杂的逻辑来生成缺少的日期。
答案此StackOverflow问题介绍了如何将此方法应用于MySQL。 / p>
您可以使用类似的数字方法，顺便说一句，通过使用数字表;我从来没有为数字做过，但似乎是一个流行的想法;请参阅此dba.stackexchange.com问题。
I have a table that stores the amount of errors according to what alarm-id it is. The table looks something like this:
|----DATE----|---ALARM_ID---|---COUNTER---|
| 2012-01-01 | 1 | 32 |
| 2012-01-01 | 2 | 28 |
| 2012-01-02 | 1 | 12 |
| 2012-01-02 | 2 | 23 |
| 2012-01-03 | 1 | 3 |
| 2012-01-03 | 2 | 9 |
| 2012-01-05 | 1 | 8 |
| 2012-01-05 | 2 | 1 |
| 2012-01-07 | 1 | 102 |
| 2012-01-07 | 2 | 78 |
Notice the gap between date (2012-01-03 - 2012-01-05) and (2012-01-05 - 2012-01-07). On these dates there isn't any data because the system, that my program is monitoring, haven't reported any errors at that date. What I'm looking for is a SQL SELECT query that returns the total amount of errors on each date, for example:
|----DATE----|---COUNTER---|
| 2012-01-01 | 60 |
| 2012-01-02 | 35 |
| 2012-01-03 | 12 |
| 2012-01-04 | 0 |
| 2012-01-05 | 9 |
| 2012-01-06 | 0 |
| 2012-01-07 | 180 |
I have a query that returns ID's even if they doesn't exist in the table, and if the ID doesn't exist, return the ID anyway with the COUNTER value 0. As such:
BEFORE AFTER
|---ID---|---COUNTER---| |---ID---|---COUNTER---|
| 1 | 2 | | 1 | 2 |
| 2 | 6 | | 2 | 6 |
| 3 | 1 | --> | 3 | 1 |
| 5 | 9 | | 4 | 0 |
| 6 | 10 | | 5 | 9 |
| 6 | 10 |
| 7 | 0 |
| 8 | 0 |
The query goes like this:
select t.num as ID, coalesce(yt.COUNTER, 0)
from all_stats yt right join
( select t1.num + t2.num * 10 + t3.num * 100 + t4.num * 1000 as num
from ( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t1 cross join
( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t2 cross join
( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t3 cross join
( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t4 )
t on yt.ID = t.num
where (t.num between (select min(ID) from all_stats) and (select max(ID) from all_stats)) order by ID
I can't figure out how I can change this query when it's regarding dates. Can someone please help me on this issue?
I'm using MySQL
Thanks in advance, Steve-O
The exact details will depend on the DBMS, and on the nature of the database (e.g., OLAP-oriented vs. OLTP-oriented), but one common general approach is to create an auxiliary calendar table that represents dates as a dimension. Then you can use regular JOINs, rather than having to use complex logic to generate missing dates.
The answers to this StackOverflow question describe how to apply this approach on MySQL.
You can use a similar approach for numbers, by the way, by having a numbers tables; I've never done that myself for numbers, but it seems to be a popular idea; see this dba.stackexchange.com question.
这篇关于即使在SQL SELECT语句的表中不存在日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
