MySQL:使用介于中间条件中的日期作为结果 [英] MySQL: Using the dates in a between condition for the results

问题描述

我有一条执行此操作的SQL语句

I have a SQL statement in which I do this

... group by date having date between '2010-07-01' and '2010-07-10';

结果如下:

sum(test)        day
--------------------
20        2010-07-03
120       2010-07-07
33        2010-07-09
42        2010-07-10

所以我有这些结果，但是有可能我可以写一条语句，在这种情况下，每天在介于"条件下返回这样的结果行:

So I have these results, but is it possible, that I can write a statement that returns me for every day in the "between" condition a result row in this kind:

sum(test)        day
--------------------
0         2010-07-01
0         2010-07-02
20        2010-07-03
0         2010-07-04
0         2010-07-05
0         2010-07-06
120       2010-07-07
...       ...
42        2010-07-10

否则，如果这不可能，则必须在程序逻辑中进行.

Otherwise, if this is not possible, I have to do it in my program logic.

非常感谢&最好的问候.

Thanks a lot in advance & Best Regards.

更新:如果我向您展示完整的SQL语句，也许会更好:

Update: Perhaps it will be better if I will show you the full SQL statement:

select COALESCE(sum(DUR), 0) AS "r", 0 AS "opt", DATE_FORMAT(date, '%d.%m.%Y') AS "day" from (
    select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, 
        a_au.re as RE, a_au.stat as STAT from b_c
        join c on b_c.c_id = c.id
        join a on c.id = a.c_id
        join a_au on a.id = a_au.id
        join revi on a_au.re = revi.re
        join (
            select a.id as ID, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, 
            max(a_au.re) as MAX_RE from b_c
                join c on b_c.c_id = c.id
                join a on c.id = a.c_id
                join a_au on a.id = a_au.id
                join revi on a_au.re = revi.re
                where b_c.b_id = 30 group by ID, date) x on
                x.id = a.id and x.date = date and x.MAX_RE = a_au.rev
                where a_au.stat != 7
            group by ID, x.date)
         AS SubSelTable where date between '2010-07-01' and '2010-07-15' group by date;

---

更新: 我的新SQL语句(-> Dave Rix):

---

Update: My new SQL statement (-> Dave Rix):

select coalesce(`theData`.`real`, 0) as 'real', 0 as 'opt', DATE_FORMAT(`DT`.`ddDate`, '%d.%m.%Y') as 'date'
    from `dimdates` as DT 
    left join (
        select coalesce(sum(DUR), 0) AS 'real', 0 AS 'opt', date 
            from (
                select a.id as ID, a.dur as DUR, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, a_au.RE as RE, a_au.stat as STAT 
                    from b_c 
                        join c on b_c.c_id = c.id 
                        join a on c.id = a.c_id 
                        join a_au on a.id = a_au.id 
                        join revi on a_au.RE = revi.RE 
                        join ( 
                            select a.id as ID, DATE(FROM_UNIXTIME(REVTSTMP / 1000)) as date, max(a_au.RE) as MAX_RE 
                                from b_c 
                                join c on b_c.c_id = c.id 
                                join a on c.id = a.c_id 
                                join a_au on a.id = a_au.id 
                                join revi on a_au.RE = revi.RE 
                            where b_c.b_id = 30 GROUP BY ID, date
                        ) x 
                    on x.id = a.id and x.date = date and x.MAX_RE = a_au.RE 
                    where a_au.stat != 20 
                    group by ID, x.date
            ) AS SubTable 
        where date between '2010-07-01' and '2010-07-10' group by date) AS theData 
    ON `DT`.`ddDate` = `theData`.`date` where `DT`.`ddDate` between '2010-07-01' and '2010-07-15';

推荐答案

查看我对以下问题的回答；

Check out my answer to the following question;

选择所有月份在给定日期范围内，包括值为0的那些

这可能就是您要寻找的:)

This may be just what you are looking for :)

您可以按如下方式修改您的查询(您可以对此进行整合，但是这种方式更简单！)

You can modify your query above as follows (you could integrate this, but this way is simpler!);

SELECT COALESCE(`theData`.`opt`, 0), `DT`.`myDate`
FROM `dateTable` AS DT
LEFT JOIN (
        ... INSERT YOUR QUERY HERE ...
) AS theData
ON `DT`.`myDate` = `theData`.`date`

，您还需要将查询中的DATE_FORMAT(date, '%d.%m.%Y') AS "day"更改为date 例如

and you will also need to change the DATE_FORMAT(date, '%d.%m.%Y') AS "day" in your query to just date E.g.

select COALESCE(sum(DUR), 0) AS "r", 0 AS "opt", `date` from

对于@OMG Ponies答案，您将需要用大量的数据行预先填充dateTable！

As for @OMG Ponies answer, you will need to pre-populate the dateTable with plenty of rows of data!

有人知道我如何将该表的SQL转储发布为可以附加的文件吗?它很大，但是很有用...

Does anyone know how I can post my SQL dump of this table as a file which can be attached? It's quite big, but can be useful...

这篇关于MySQL:使用介于中间条件中的日期作为结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！