日期:2014-05-17  浏览次数:20971 次

SQL问题,求解决
select t.orderdate, count(t.ticketid),sum(t.fare),sum(t.fuel_tax),sum(t.airport_tax),sum(t.paymoney)
  from metticket t
 where t.status = '2'
  and t.orderdate is not null
 group by t.orderdate
 order by t.orderdate

这样统计每天的金额,就用日期分组了。

20100929 1 1460 0 50 1510
20101019 1 920 0 50 970
20101020 1 800 0 50 850
20101021 2 2500 0 100 2600
20101025 5 3570 0 250 3820
20101027 4 2330 0 200 2530
20101028 3 1710 0 150 1860
20101029 6 3920 0 300 4220
20101102 1 680 0 50 730
20101105 1 870 0 50 920
  ......

我想实现:没有数据的那天,也能查询到记录。因为没有记录,所有金额都为0
比如20101022没有记录,则为:
20101022 0 0 0 0 0
不知道怎么实现,求帮助

------解决方案--------------------
把下面的365换成你想要从今天开始统计到过去多少天的数据。

SQL code
with
date_table as (select (sysdate-level) orderdate from dual connect by level < 365),
order_table as (select t.orderdate, count(t.ticketid) cnt,
    sum(t.fare) sumfare, sum(t.fuel_tax) sumfueltax,
    sum(t.airport_tax) sumairtax, sum(t.paymoney) sumpay from metticket t
    where t.status = '2'
        and t.orderdate is not null
        group by t.orderdate
        order by t.orderdate)
select date_table.orderdate,
       decode(cnt,null,0,cnt),
       decode(sumfare,null,0,sumfare),
       decode(sumfueltax,null,0,sumfueltax),
       decode(sumairtax,null,0,sumairtax),
       decode(sumpay,null,0,sumpay)
    from date_table left join order_table
        on date_table.orderdate=order_table.orderdate
    order by 1;