select SUM(NM_GPRS_FLOW),nm_access_date from ba_gprs_total where nm_access_date between
to_date('2010-10-11 00:00:00','yyyy-MM-ddHH24:mi:ss') and
to_date('2010-10-17 23:59:59','yyyy-MM-ddHH24:mi:ss') GROUP BY NM_ACCESS_DATE ;以上是ORACLE的SQL语句,作用是求出11号到17号的每天的数据并且按照天求和,现在的问题是,11号到17中间,有几天是没有数据的,但是展示出来的结果要求没有数据的这些天用0来展示。
各位大侠帮帮忙~谢谢啦!
total date
1000 2010-10-11
0 2010-10-12
0 2010-10-13
0 2010-10-14
0 2010-10-15
0 2010-10-16
0 2010-10-17
我现在能做到的就是
1000 2010-10-11
希望各位帮帮忙
(select SUM(NM_GPRS_FLOW) sum_NM_GPRS_FLOW,nm_access_date from ba_gprs_total where nm_access_date between to_date('2010-10-11 00:00:00','yyyy-MM-ddHH24:mi:ss') and
to_date('2010-10-17 23:59:59','yyyy-MM-ddHH24:mi:ss') GROUP BY NM_ACCESS_DATE ) t1,
(select to_date('2010-10-11 00:00:00','yyyy-MM-ddHH24:mi:ss')+(rownum-1) mydate from dual connect by rownum<=
to_date('2010-10-17 23:59:59','yyyy-MM-ddHH24:mi:ss')-
to_date('2010-10-11 00:00:00','yyyy-MM-ddHH24:mi:ss')+1)t2
where t2.mydate =t1.nm_access_date(+)
to_date('2010-10-11 00:00:00','yyyy-MM-ddHH24:mi:ss') and
to_date('2010-10-17 23:59:59','yyyy-MM-ddHH24:mi:ss') GROUP BY NM_ACCESS_DATE ;
使用case,when else也可实现,自己找找吧