一条查询语句，急急急！

Question

select count(num) from 
(select col1 num,TRUNC(TO_CHAR(coldate,'HH24')/2) dt tbname
where coldate>=(to_date('20030224','yyyymmdd')) and 
coldate<=(to_date('20030226','yyyymmdd'))) a group by a.dt;

Answer 1

select count(colname) from tbname
where coldate>=(to_date('20030224','yyyymmdd')) and 
coldate<=(to_date('20030226','yyyymmdd')) group by TRUNC(TO_CHAR(coldate,'HH24')/2);

Answer 2

我觉得在上述语句的coldate改成trunc(coldate)较好。

Answer 3

SQL> select * from t;D                   V
---------- ----------
24-2月 -03          1
24-2月 -03         10
24-2月 -03         11
24-2月 -03         12
24-2月 -03         13
25-2月 -03         14
25-2月 -03         12
26-2月 -03         16已选择8行。select to_char(d,'yyyy-mm-dd'),decode(sign(to_char(d,'hh24')-2),-1,'0-2',decode(sign(to_char(d,'hh24')-4),
    -1,'2-4',decode(sign(to_char(d,'hh24')-6),-1,'4-6',decode(sign(to_char(d,'hh24')-8),-1,'6-8',
decode(sign(to_char(d,'hh24')-10),-1,'8-10',decode(sign(to_char(d,'hh24')-12),-1,'10-12',
decode(sign(to_char(d,'hh24')-14),-1,'12-14',decode(sign(to_char(d,'hh24')-16),-1,'14-16',
decode(sign(to_char(d,'hh24')-18),-1,'16-18',decode(sign(to_char(d,'hh24')-20),-1,'18,20',
decode(sign(to_char(d,'hh24')-22),-1,'20-22',decode(sign(to_char(d,'hh24')-24),-1,'22-24','0-2')))))))))))) ,count(*) 
from t
  group by to_char(d,'yyyy-mm-dd'),decode(sign(to_char(d,'hh24')-2),-1,'0-2',decode(sign(to_char(d,'hh24')-4),
    -1,'2-4',decode(sign(to_char(d,'hh24')-6),-1,'4-6',decode(sign(to_char(d,'hh24')-8),-1,'6-8',
decode(sign(to_char(d,'hh24')-10),-1,'8-10',decode(sign(to_char(d,'hh24')-12),-1,'10-12',
decode(sign(to_char(d,'hh24')-14),-1,'12-14',decode(sign(to_char(d,'hh24')-16),-1,'14-16',
decode(sign(to_char(d,'hh24')-18),-1,'16-18',decode(sign(to_char(d,'hh24')-20),-1,'18,20',
decode(sign(to_char(d,'hh24')-22),-1,'20-22',decode(sign(to_char(d,'hh24')-24),-1,'22-24','0-2'))))))))))))TO_CHAR(D, DECOD   COUNT(*)
---------- ----- ----------
2003-02-24 0-2            4
2003-02-24 2-4            1
2003-02-25 0-2            1
2003-02-25 4-6            1
2003-02-26 6-8            1

Answer 4

select count(colname) from tbname
where coldate>=(to_date('20030224','yyyymmdd')) and 
coldate<=(to_date('20030226','yyyymmdd')) 
group by to_char(coldate,"dd-mon-yyyy')||TRUNC(TO_CHAR(coldate,'HH24')/2);