给你提供一个思路根据这个思路进行分组
用datetimea-起始时段,然后换算成秒,再除以你统计的间隔的秒数,(比如你的例子就是12小时对应的秒数),然后再把结果转换为number,根据number进行分组求和就可以了。具体的时间函数忘记了,没法写出很具体的sql语句。不好意思。
用datetimea-起始时段,然后换算成秒,再除以你统计的间隔的秒数,(比如你的例子就是12小时对应的秒数),然后再把结果转换为number,根据number进行分组求和就可以了。具体的时间函数忘记了,没法写出很具体的sql语句。不好意思。
好..............麻烦!!!! 根据你的思路:
设 datetimea-起始时段 为 2005-01-01 6:00:00 ; 设 具体的时间函数 为B()
麻烦您老人家给个很具体的sql语句. :)
这样就可以求和了
SELECT COUNT(*) FROM 表A
WHERE
DATETIMEA
BETWEEN
TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD') || '060000','YYYYMMDDHH24MISS')
AND
TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD') || '180000','YYYYMMDDHH24MISS')2.当天18:00-次日6:00
SELECT COUNT(*) FROM 表A
WHERE
DATETIMEA
BETWEEN
TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD') || '180000','YYYYMMDDHH24MISS')
AND
TO_DATE(TO_CHAR(SYSDATE + 1,'YYYYMMDD') || '060000','YYYYMMDDHH24MISS')但是,使用BETWEEN就会包括两头的时间。
所以,你可以改成>=和<。
运行结果如下 :
SQL> select round(to_number(datetimea-to_date('2004/12/31 18:00:00','yyyy-mm-dd hh24:mi:ss'))/12*60*6 dateA,count(*)
from A group by
round(to_number(datetimea-to_date('2004/12/31 18:00:00','yyyy-mm-dd hh24:mi:ss'))/12*60*60);
DATEA COUNT(*)
--------- ---------
-827 3
-631 1
-527 4
-356 1
-263 5
-256 2
-188 6
-44 14
-19 22
100 31
125 25
194 32
219 33
263 32
........
9256 32
9269 27
9290 15已选择238行。 和数是有了,可是看不出对应的时间啊.
重新写一条完整的语句
select trunc((datetimea-to_date('2005-01-01 6:00:00','yyyy-mm-dd hh24:mi:ss'))*24*3600/(12*3600)) a,count(*) b from table group by trunc((datetimea-to_date('2005-01-01 6:00:00','yyyy-mm-dd hh24:mi:ss'))*24*3600/(12*3600));这样应该就可以了,可能不是很精确,因为如果时间正好在时间段的起始点上或者结束点上你没有说明该如何处理,你自己试一下。
CREATE TABLE ZHOUXY.TCHAR
(
F1 CHAR(10),
F2 VARCHAR2(10),
F3 DATE
)
/2。测试数据
INSERT INTO TCHAR values('a ','a',to_date('2005/02/03 05:00:00', 'yyyy/MM/dd hh24:mi:ss')
)
/
INSERT INTO TCHAR values('b ','b',to_date('2005/02/03 06:00:00', 'yyyy/MM/dd hh24:mi:ss')
)
/
INSERT INTO TCHAR values('c ','c',to_date('2005/02/03 18:00:00', 'yyyy/MM/dd hh24:mi:ss')
)
/
INSERT INTO TCHAR values('d ','d',to_date('2005/02/04 06:00:00', 'yyyy/MM/dd hh24:mi:ss')
)
/
INSERT INTO TCHAR values('e ','e',to_date('2005/02/04 18:00:00', 'yyyy/MM/dd hh24:mi:ss')
)
/
INSERT INTO TCHAR values('b2 ','b2',to_date('2005/02/03 07:00:00', 'yyyy/MM/dd hh24:mi:ss')
)
/
INSERT INTO TCHAR values('f ','f',to_date('2005/02/04 19:00:00', 'yyyy/MM/dd hh24:mi:ss')
)
/
3。SQL文
SELECT
TO_CHAR ( ( SELECT MIN ( F3 ) -1 FROM TCHAR ) , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
(
SELECT
COUNT ( F3 ) AS TOTAL
FROM
TCHAR
WHERE
F3 < TO_DATE ( TO_CHAR ( ( SELECT MIN ( F3 ) FROM TCHAR ) , 'YYYYMMDD' ) || '060000' , 'YYYYMMDDHH24MISS' ) ) TOTAL
FROM
DUAL
UNION
SELECT
TO_CHAR ( F3 , 'YYYY-MM-DD' ) || ' 06:00-18:00' TIME ,
SUM ( DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '060000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 0 , DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '180000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 1 , 0 ) ) ) AS TOTAL
FROM
TCHAR
GROUP BY
TO_CHAR ( F3 , 'YYYY-MM-DD' )
UNION
SELECT
TO_CHAR ( F3 , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
SUM ( DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '180000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 0 , DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 + 1 , 'YYYYMMDD' ) || '060000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 1 , 0 ) ) ) AS TOTAL
FROM
TCHAR
GROUP BY
TO_CHAR ( F3 , 'YYYY-MM-DD' )
ORDER BY
TIME
刚才去开了个会,让您老人家久等了. 这次的运行结果如下: A B
---------- ----------
-61 71
-60 112
-59 102
-58 76
...............
-2 79
-1 136
0 55
已选择62行。 这个A库中 时间>=2004/12/31 18:00 <2005/01/31 18:00 "已选择62行"是对的.
时间段的起始点和结束点为: > 起始点 <= 结束点
时间正好在时间段的起始点上或者结束点上的情况经常发生,要不您再给个主意。 :)
改为
to_date('2005-01-01 6:00:01','yyyy-mm-dd hh24:mi:ss')
是不是可以呢?
TO_CHAR ((SELECT MIN (F3) -1 FROM TCHAR) , 'YYYY-MM-DD') || ' 18:00-06:00' TIME ,
*
ORA-00936: missing expression
改为
to_date('2005-01-01 6:00:01','yyyy-mm-dd hh24:mi:ss')后,数据完全吻合. 一.select datetimea,count(*) from A
where to_char(datetimea,'hh24:mi:ss') in ('18:00:00','06:00:00')
group by jjshj
......
2005/01/04 18:00:00 21
2005/01/05 06:00:00 34
2005/01/05 18:00:00 51
.......
已选择44行。二. select count(*) from tljf_xz_pmgl
where jjshj>'2005/01/04 18:00:00' and jjshj<='2005/01/05 06:00:00'COUNT(*)
----------
159三. select trunc((jjshj-to_date('2004-12-31 18:00:01'))*2) a,count(*) b
from tljf_xz_pmgl group by
trunc((jjshj-to_date('2004-12-31 18:00:01'))*2);
A B
---------- ----------
0 79
1 117
2 112
3 98
4 101
5 116
6 68
7 75
8 159
9 145
10 85
.............
哈哈,能看出对应的是哪一行吗? 对应的时间与a的值的处理也是蛮伤脑筋的,要不您再给个算法?这要求不过分吧,哈哈......
SQL 后半部运行正常:
SQL> SELECT
2 TO_CHAR ( F3 , 'YYYY-MM-DD' ) || ' 06:00-18:00' TIME ,
3 SUM ( DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '060000' , 'YYYYMMD
DHH24MISS' ) ) , -1 , 0 , DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '180000' , '
YYYYMMDDHH24MISS' ) ) , -1 , 1 , 0 ) ) ) AS TOTAL
4 FROM
5 TCHAR
6 GROUP BY
7 TO_CHAR ( F3 , 'YYYY-MM-DD' )
8 UNION
9 SELECT
10 TO_CHAR ( F3 , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
11 SUM ( DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '180000' , 'YYYYMMD
DHH24MISS' ) ) , -1 , 0 , DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 + 1 , 'YYYYMMDD' ) || '060000'
, 'YYYYMMDDHH24MISS' ) ) , -1 , 1 , 0 ) ) ) AS TOTAL
12 FROM
13 TCHAR
14 GROUP BY
15 TO_CHAR ( F3 , 'YYYY-MM-DD' )
16 ORDER BY
17 TIME
18 /TIME TOTAL
-------------------------------------------- ---------
2005-02-03 06:00-18:00 2
2005-02-03 18:00-06:00 1
2005-02-04 06:00-18:00 1
2005-02-04 18:00-06:00 2可是前半部报错:
SQL> SELECT
2 TO_CHAR ( ( SELECT MIN ( F3 ) -1 FROM TCHAR ) , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
3 (
4 SELECT
5 COUNT ( F3 ) AS TOTAL
6 FROM
7 TCHAR
8 WHERE
9 F3 < TO_DATE ( TO_CHAR ( ( SELECT MIN ( F3 ) FROM TCHAR ) , 'YYYYMMDD' ) || '06
0000' , 'YYYYMMDDHH24MISS' ) ) TOTAL
10 FROM
11 DUAL
12 /
TO_CHAR ( ( SELECT MIN ( F3 ) -1 FROM TCHAR ) , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
*
错误位于第2行:
ORA-00936: missing expression
我的版本是9.2.0的。我从屏幕上面复制回去,执行也没有问题的。
你把她写到文件里面,再执行一次,看看结果呢?我执行的结果,SQL> SELECT
2 TO_CHAR ( ( SELECT MIN ( F3 ) -1 FROM TCHAR ) , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
3 (
4 SELECT
5 COUNT ( F3 ) AS TOTAL
6 FROM
7 TCHAR
8 WHERE
9 F3 < TO_DATE ( TO_CHAR ( ( SELECT MIN ( F3 ) FROM TCHAR ) , 'YYYYMMDD' ) || '06
0000' , 'YYYYMMDDHH24MISS' ) ) TOTAL
10 FROM
11 DUAL
12 /TIME TOTAL
---------------------- ----------
2005-02-02 18:00-06:00 1SQL>
SQL> edit CroatiaSQL> @ Croatia
TO_CHAR ( ( SELECT MIN ( F3 ) -1 FROM TCHAR ) , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
*
错误位于第2行:
ORA-00936: missing expression
这儿就没过:SQL> SELECT
2 COUNT ( F3 ) AS TOTAL
3 FROM
4 TCHAR
5 WHERE
6 F3 < TO_DATE ( TO_CHAR ( ( SELECT MIN ( F3 ) FROM TCHAR ),'YYYYMMDD' ) || '060000' , 'YYYYMMD
DHH24MISS' );
F3 < TO_DATE ( TO_CHAR ( ( SELECT MIN ( F3 ) FROM TCHAR ),'YYYYMMDD' ) || '060000' , 'YYYYMMDDHH24
*
错误位于第6行:
ORA-00936: missing expression
SQL> SELECT ( SELECT f3 FROM TCHAR where f3='2005/02/03 5:00:00' )
2 FROM DUAL
3 /SELECT ( SELECT f3 FROM TCHAR where f3='2005/02/03 5:00:00' )
*
错误位于第1行:
ORA-00936: missing expression
DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '060000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 0 , DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '180000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 1 , 0 ) ) 这个我查到了:
SIGN(): 如果n为负数,返回-1,如果n为正数,返回1,如果n=0返回0.
DECODE() 类似:[if 条件then 语句] 可是合到一起我是越看越胡涂了,你能解释下吗?
create function f_hh24(p_date in date)
return varchar2
as
begin
if to_char(p_date,'hh24') between '06' and '18' then
return '1';
else
return '2';
end if;
end;
/
select to_char(col_name,'yyyymmdd')||' '||f_hh24(col_name),count(1)
from table_name
group by grouping sets(to_char(col_name,'yyyymmdd')||' '||f_hh24(col_name))
函数已创建,可是查询报错:
SQL> create function f_hh24(p_date in date)
2 return varchar2
3 as
4 begin
5 if to_char(p_date,'hh24') between '06' and '18' then
6 return '1';
7 else
8 return '2';
9 end if;
10 end;
11 /函数已创建。SQL> select to_char(jjshj,'yyyymmdd')||' '||f_hh24(jjshj),count(1)
2 from tljf_xz_pmgl
3 group by grouping sets(to_char(jjshj,'yyyymmdd')||' '||f_hh24(jjshj));
group by grouping sets(to_char(jjshj,'yyyymmdd')||' '||f_hh24(jjshj))
*
错误位于第3行:
ORA-00933: SQL command not properly ended
grouping sets 是什么作用?
假如等于或者大于,就看后面的这个条件的值。DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '180000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 1 , 0 )这个就是和18点作比较,假如小于,就说明可以作为一个有效的计数。否则,就不计数。
我终于是搞明白了. 但是又发现了一个小问题:
每天 0:00:00-6:00:00之前 时间段内的数没有计入.
于是又加了
UNION
SELECT
TO_CHAR ( F3-1 , 'YYYY-MM-DD' ) || ' 18:00-06:00' TIME ,
SUM ( DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 , 'YYYYMMDD' ) || '060000' ,
'YYYYMMDDHH24MISS' ) ) , 1 , 0 , DECODE ( SIGN ( F3 - TO_DATE ( TO_CHAR ( F3 ,
'YYYYMMDD' ) || '060000' , 'YYYYMMDDHH24MISS' ) ) , -1 , 1 , 0 ) ) ) AS TOTAL
FROM
TCHAR
GROUP BY
TO_CHAR ( F3-1 , 'YYYY-MM-DD' ) 但是这样写后,夜班时间会出现两次
.....
2005-02-04 06:00-18:00 1
2005-02-04 18:00-06:00 1
2005-02-04 18:00-06:00 1
......
麻烦你再给个好主意.
llm06(blacksheep)的SQL 简洁有效, 也是同我最初的思路最接近的,只要再加个日期处理函数
Croatia(Croatia) 的SQL 查询的结果 同我需要的完全一致,只是在 0:00-6:00 还有点小问题。
yown(yong) 的方法 对我不太适用,因为这个查询实际上是不定期的,时间范围是不确定的。
beckhambobo(beckham) 俺对你有意见! 只露个小脸,问题还没解决,你就再也不出现了!