如果日期不存在，MySQL选择带有预定义值的日期 [英] MySQL select date with predefined values if date not exist

问题描述

我有以下MySQL查询:

I've got following MySQL query:

SELECT Date,
       Fail/(Success + Fail + NA) AS  'Rate'
FROM (
    SELECT CAST( s.date_str AS DATE ) AS  'Date',
           SUM(CASE WHEN k.result = 'SUCCESS' THEN 1 ELSE 0 END) Success,
           SUM(CASE WHEN k.result = 'FAIL' THEN 1 ELSE 0 END) Fail,
           SUM(CASE WHEN k.result != 'FAIL' AND k.result != 'SUCCESS' THEN 1 ELSE 0 END) NA 
    FROM Session s, KPI k
    WHERE s.id = k.session_id
    GROUP BY Date
)subQuery

例如，显示此结果

日期|费率
.....................
2015-10-15 |0.0836
2015-10-16 |0.0279
2015-10-19 |0.1569
2015-10-21 |0.2756

Date | Rate
.....................
2015-10-15 | 0.0836
2015-10-16 | 0.0279
2015-10-19 | 0.1569
2015-10-21 | 0.2756

因此，它仅在有数据时显示结果.我想要实现的是，当没有数据时，它将显示下一个日期和汇率0.0000，因此在该示例中，它看起来像这样:

So it shows only result when there's some data.. What I'd like to achieve is when there's no data it shows next date and rate 0.0000 so in that example it'd looks like this:

日期|费率
.....................
2015-10-15 |0.0836
2015-10-16 |0.0279
2015-10-17 |0.0000
2015-10-18 |0.0000
2015-10-19 |0.1569
2015-10-20 |0.0000
2015-10-21 |0.2756

Date | Rate
.....................
2015-10-15 | 0.0836
2015-10-16 | 0.0279
2015-10-17 | 0.0000
2015-10-18 | 0.0000
2015-10-19 | 0.1569
2015-10-20 | 0.0000
2015-10-21 | 0.2756

我该如何实现?

推荐答案

您需要连接到至少具有30行的另一个表，以制造数据集中不存在的任何日期.像这样:

You'll need to join to another table that has at least 30 rows to manufacture any dates that do not exist in the data set. Something like:

 select * from(
     select curdate() - interval @rn := @rn + 1 day as last_30_date
     from table_with_at_least_30_rows
     join (select @rn := -1) rows limit 30
 ) days
 left join (select curdate() as data_date) d on last_30_date = data_date;

用查询替换选择curdate()作为data_date .

这篇关于如果日期不存在，MySQL选择带有预定义值的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！