记录补零的SQL语句

Question

现有一张表mytab(id number,exeDate date,exeTime number),
分别表示ID,交易时间,交易数量.
现要用一句SQL语句查询某个月每天的交易数量,
如给定时间为8月,返回31条记录,形如:
2010-08-01    20
2010-08-02    0
2010-08-03    100给定时间为9月,则返回30条记录.由于有些天可能没有交易,则必须进行补零处理,该如何做呢?

Answer 1

select id,exeDate,exeTime
from mytab
where exeDate between  to_date(you_input,'mm') and to_date(to_number(you_input)+1,'mm')-1;
不明白你的补0的情况是什么！请举例说明！

Answer 2

明白你的补0情况了！修改如下：
select exeDate,nvl(exeTime,0) exeTime
from mytab
where exeDate between  to_date(you_input,'mm') and to_date(to_number(you_input)+1,'mm')-1;
you_input是你输入的查询月份数

Answer 3

--you_input是你的输入月份，字串型的，如：2010-08
select you_input||'-'||to_char(level-1,fmt00) 时间 ,nvl(exeTime,0)  交易数量 
from mytab
connect by level <= to_number(to_char(last_day(to_date(you_input,'yyyy-mm')),DD))
where trunc(exeDate,'mm')= trunc(to_date(you_input,'yyyy-mm')),'mm') ;

Answer 4

笨方法
先建个维表，tb_date(dt varchar(20));
dt存每个月的日期，如2010-09-01,2010-09-02。。select a.dt,nvl(b.cnt,0) cnt
from tb_date a
left join (select exeDate ,count(1) cnt
          from mytab 
          group by exeDate )b on substr(a.dt,6,2)=to_char(b.exeDate ,'mm')
;

Answer 5

主要是null的nvl(exeTime,0)的处理吧，顶3楼的解决办法，学习4楼6楼的解决办法

Answer 6

select temp.exeDate, nvl2(exeTime, exeTime, 0) exeTime
  from (select trunc(sysdate, 'MM') + rownum - 1 exeDate
          from dual
        connect by rownum <= to_number(to_char(last_day(SYSDATE), 'DD'))) temp
  left join mytab on temp.exeDate = mytab.exeDate

Answer 7

-- 查找当前月份所有的天数
select * from 
(
select trunc(sysdate,'mm')+rownum-1 as dt  from dual
connect by rownum<=31
)
where trunc(dt,'mm') = trunc(sysdate,'mm')

Answer 8

什么错？
我再修改下：
--you_input是你的输入月份，字串型的，如：2010-08
select you_input||'-'||lpad(level-1,2,'0') 时间 ,nvl(exeTime,0)  交易数量 
from mytab
connect by level <= to_number(to_char(last_day(to_date(you_input,'yyyy-mm')),'DD'))
where trunc(exeDate,'mm')= trunc(to_date(you_input,'yyyy-mm'),'mm') ;

Answer 9

select '2010-10' || '-' || to_char(level - 1) t, nvl(exeTime, 0) n
from mytab
where trunc(exeDate, 'mm') = trunc(to_date('2010-10', 'yyyy-mm'), 'mm')
connect by level <= 31查询出来的记录有很多是重复的

Answer 10

再讲一下我的意图：
建表：
create table MYTAB
(
  ID      NUMBER,
  EXEDATE DATE,
  EXETIME NUMBER
);
插入记录：
insert into mytab (ID, EXEDATE, EXETIME) 
values (1, to_date('2010-10-04, 'yyyy-mm-dd'), 2);insert into mytab (ID, EXEDATE, EXETIME) 
values (2, to_date('2010-10-12', 'yyyy-mm-dd'), 5);insert into mytab (ID, EXEDATE, EXETIME) 
values (3, to_date('2010-10-27', 'yyyy-mm-dd'), 4);表示
2010-10-04有2条记录
2010-10-12有5条记录
2010-10-27有4条记录现在我想得到这样的记录(2010-10月有31天)
2010-10-01      0
2010-10-02      0
2010-10-03      0
2010-10-04      2
2010-10-05      0
2010-10-06      0
2010-10-07      0
2010-10-08      0
2010-10-09      0
2010-10-10      0
2010-10-11      0
2010-10-12      5
2010-10-13      0
...
...
2010-10-31      0

Answer 11

这回可以了：WITH tab AS(
SELECT To_Date('2010-10','yyyy-mm')+LEVEL-1 t FROM dual connect by level <= 31
) 
SELECT a.t,Nvl(exetime,0) FROM tab a,mytab b
WHERE a.t=b.exedate(+)
ORDER BY a.t

Answer 12

感谢gelyon的持续关注，其为正解，接分！另外，我想问一个问题在一般的应用中connect by level,connect by rownum除了产生连续数以外，还有什么其它的用途吗？

Answer 13

connect by 也可以用于递归查询，递归构造树状结构的查询，你可以参考：http://maruibenze.javaeye.com/blog/208541

Answer 14

--这是十月的 你想那个月就在sysdate上改下加个where to_char(exedate,'mm')=n月 把sysdate改成add_months(sysdate,-1) 为9月的SQL> with tb as
  2  (select to_date(to_char(sysdate,'yyyy-mm-')||lpad(level,2,'0'),'yyyy-mm-dd') dt from dual
  3  connect by level<=to_char(last_day(sysdate),'dd'))
  4  select nvl(a.EXEDATE,b.dt) sj,nvl(a.exetime,0)
  5  from mytab a full outer join tb b on a.EXEDATE=b.dt
  6  order by sj

Answer 15

--这是十月的 你想那个月就在sysdate上改下加个where to_char(exedate,'mm')=n月 把sysdate改成add_months(sysdate,-1) 为9月的SQL> with tb as
  2  (select to_date(to_char(sysdate,'yyyy-mm-')||lpad(level,2,'0'),'yyyy-mm-dd') dt from dual
  3  connect by level<=to_char(last_day(sysdate),'dd'))
  4  select nvl(a.EXEDATE,b.dt) sj,nvl(a.exetime,0)
  5  from mytab a full outer join tb b on a.EXEDATE=b.dt
  6  order by sj

Answer 16

--这是十月的 你想那个月就在sysdate上改下加个where to_char(exedate,'mm')=n月 把sysdate改成add_months(sysdate,-1) 为9月的SQL> with tb as
  2  (select to_date(to_char(sysdate,'yyyy-mm-')||lpad(level,2,'0'),'yyyy-mm-dd') dt from dual
  3  connect by level<=to_char(last_day(sysdate),'dd'))
  4  select nvl(a.EXEDATE,b.dt) sj,nvl(a.exetime,0)
  5  from mytab a full outer join tb b on a.EXEDATE=b.dt
  6  order by sj