select to_char(日期,'yyyy-mm-dd'),count(*) from 表 group by to_char(日期,'yyyy-mm-dd') where ......
select count(decode(field1,'2005-10-09','AnyChar',null)) as COUNT1, count(decode(field1,'2005-10-10','AnyChar',null)) as COUNT2, count(decode(field1,'2005-10-11','AnyChar',null)) as COUNT3 from table1
select 日期 , count(日期) as 总数 from table group by 日期
上面的日期应该有格式的,to_char转换
to waterfirer(水清) : 11号没有记录,我的意思是要把11号记录总数为0也取出来,你那样做不到
SELECT b.rq,count(a.数量) 总数 FROM tablename a, (SELECT DATE '2005-10-09' + rownum - 1 AS rq FROM all_objects WHERE rownum <= DATE '2005-10-11' - DATE '2005-10-09' + 1) b WHERE a.日期(+) = b.rq GROUP BY b.rq
上面有点问题,改下:SELECT b.rq,count(a.日期) 总数 FROM tablename a, (SELECT DATE '2005-10-09' + rownum - 1 AS rq FROM all_objects WHERE rownum <= DATE '2005-10-11' - DATE '2005-10-09' + 1) b WHERE trunc(a.日期(+)) = b.rq GROUP BY b.rq
select rq,(select count(*) from tablename where 日期=rq) from (SELECT DATE '2005-10-09' + rownum - 1 AS rq FROM all_objects WHERE rownum <= DATE '2005-10-11' - DATE '2005-10-09' + 1)
count(decode(field1,'2005-10-09','AnyChar',null)) as COUNT1,
count(decode(field1,'2005-10-10','AnyChar',null)) as COUNT2,
count(decode(field1,'2005-10-11','AnyChar',null)) as COUNT3
from table1
from table
group by 日期
11号没有记录,我的意思是要把11号记录总数为0也取出来,你那样做不到
FROM tablename a,
(SELECT DATE '2005-10-09' + rownum - 1 AS rq
FROM all_objects
WHERE rownum <= DATE '2005-10-11' - DATE '2005-10-09' + 1) b
WHERE a.日期(+) = b.rq
GROUP BY b.rq
FROM tablename a,
(SELECT DATE '2005-10-09' + rownum - 1 AS rq
FROM all_objects
WHERE rownum <= DATE '2005-10-11' - DATE '2005-10-09' + 1) b
WHERE trunc(a.日期(+)) = b.rq
GROUP BY b.rq
用duanzilin(寻)的办法吧,找一个表把日期列列出来再求count。
我9号和11号没有记录,但按duanzilin(寻)的方法取出来的9号、11号的记录却都是1
rq,(select count(*) from tablename where 日期=rq)
from
(SELECT DATE '2005-10-09' + rownum - 1 AS rq
FROM all_objects
WHERE rownum <= DATE '2005-10-11' - DATE '2005-10-09' + 1)
我9号和11号没有记录,但按duanzilin(寻)的方法取出来的9号、11号的记录却都是1--------------------------------------------不会的,你如果count(*)就会是1,count(b.rq)也是1,你试着count(a.字段)就会是0 了