Вот еще тебе прием [codeWITH sample_table AS
(
SELECT 'Smith' username,'1' log_result,TO_DATE('15.12.10','dd.mm.yy') log_time FROM dual UNION ALL
SELECT 'Hacker' ,'0',TO_DATE('14.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker' ,'0',TO_DATE('13.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker' ,'0',TO_DATE('12.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker1','1',TO_DATE('11.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker1','0',TO_DATE('10.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker1','1',TO_DATE('09.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker1','1',TO_DATE('07.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker1','0',TO_DATE('06.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker2','1',TO_DATE('15.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker2','0',TO_DATE('11.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker2','0',TO_DATE('10.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker2','1',TO_DATE('09.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker2','0',TO_DATE('07.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker2','0',TO_DATE('06.12.10','dd.mm.yy') FROM dual UNION ALL
SELECT 'Hacker2','0',TO_DATE('05.12.10','dd.mm.yy') FROM dual
),
t1 AS (
SELECT *
FROM sample_table
ORDER BY username,
log_time
),
t0 AS (
SELECT t1.*,
ROWNUM rn
FROM t1
),
t3 AS
(
SELECT t1.username, t1.rn, t1.log_result
FROM t0 t1, t0 t2
WHERE t2.rn-1=t1.rn
AND t2.username=t1.username
AND t2.log_result = t1.log_result
AND t1.log_result = 0
ORDER BY
t1.username,
t1.log_time
)
SELECT DISTINCT
username
FROM t3
GROUP BY username, ROWNUM-rn
HAVING COUNT(*) >=2][/code]