下面的告告警表的数据 ,每生成一条告警就生成一条数据 求连续三天都出现记录的CELL_ID
举例:GLWTPCN 符合条件
cell_ID alarm_Desc begin_time
GBAFLDN AC-MINUS 2008-31 20:24:00.000
GAAHQDN UPS MAINS ALARM 2008-09-15 14:36:00.000
GLWTPCN UPS FAULT ALARM 2008-09-18 17:58:00.000
GLWTPCN AC-LOW&HIGH 2008-09-19 15:56:00.000
GLWTPCN BREAK-LINE 2008-09-20 02:42:00.000
H22DEC1 GATING ALARM 2008-09-21 10:16:00.000
举例:GLWTPCN 符合条件
cell_ID alarm_Desc begin_time
GBAFLDN AC-MINUS 2008-31 20:24:00.000
GAAHQDN UPS MAINS ALARM 2008-09-15 14:36:00.000
GLWTPCN UPS FAULT ALARM 2008-09-18 17:58:00.000
GLWTPCN AC-LOW&HIGH 2008-09-19 15:56:00.000
GLWTPCN BREAK-LINE 2008-09-20 02:42:00.000
H22DEC1 GATING ALARM 2008-09-21 10:16:00.000
select cellID fom A,(
select B.cellID,count(1)over(partition by B.cellID,br) cn from
(select A.*,(begin_time-rownum) br from A order by A.cellID,begin_time) B) C
where A.cellID=C.cellID and C.cn>=3
DROP TABLE TEST;
CREATE TABLE TEST(CELL_ID VARCHAR2(10),ALARM_DESC VARCHAR2(20),BEGIN_TIME DATE);
INSERT INTO TEST
VALUES
('GBAFLDN',
'AC-MINUS',
TO_DATE('2008-09-11 20:24:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST
VALUES
('GAAHQDN',
'UPS MAINS ALARM',
TO_DATE('2008-09-15 14:36:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST
VALUES
('GLWTPCN',
'UPS FAULT ALARM',
TO_DATE('2008-09-18 17:58:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST
VALUES
('GLWTPCN',
'AC-LOW&HIGH',
TO_DATE('2008-09-19 15:56:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST
VALUES
('GLWTPCN',
'BREAK-LINE',
TO_DATE('2008-09-20 02:42:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO TEST
VALUES
('H22DEC1',
'GATING ALARM',
TO_DATE('2008-09-21 10:16:00', 'YYYY-MM-DD HH24:MI:SS'));SELECT DISTINCT CELL_ID
FROM (SELECT B.CELL_ID,
COUNT(1) OVER(PARTITION BY B.CELL_ID, SUBSTR(B.BR, 1, '10')) CN
FROM (SELECT A.*, (BEGIN_TIME - ROWNUM) BR
FROM TEST A
ORDER BY A.CELL_ID, BEGIN_TIME) B)
WHERE CN >= 3--运行结果
CELL_ID
1 GLWTPCN
SQL> SELECT CELL_ID
2 FROM (
3 SELECT CELL_ID,
4 ROWNUM - ROW_NUMBER() OVER(PARTITION BY CELL_ID ORDER BY BEGIN_TIME,CELL_ID) RN
5 FROM TABLE_NAME TT
6 )
7 GROUP BY CELL_ID, RN
8 HAVING COUNT(1) >= 3;CELL_ID
-------
GLWTPCN
select a.cell_id from tab a, tab b
where a.cell_id= b.cell_id and trunc(a.begin_time) = trunc(b.begin_time)+1
group by a.cell_id having count(1)>1;
LZ你真逗,问题提在oracle版,当然用oracle的语法和函数来解决了。