求解決方案,高手指點,謝謝!

Question

这样不知是否可以?select a.* , count(*)
  from tab_name a
 where a.col3 = 1
   and a.col2 = next a.col2+1
  start with a.col2 = select (min(col2) from tab_name) 
 having (count*) > 3

Answer 1

13:45:27 SQLPLUS>select * from ttt;COL1       COL2             COL3
---------- ---------- ----------
a          2004-01-01          1
a          2004-01-02          1
a          2004-01-03          1
a          2004-01-04          1
a          2004-01-05          1
a          2004-01-06          0
a          2004-01-07          0
a          2004-01-08          1
a          2004-01-09          1
a          2004-01-10          1
1          2004-01-11          1已选择11行。已用时间:  00: 00: 00.00
13:45:30 SQLPLUS>SELECT *
13:45:32   2    FROM TTT
13:45:32   3   WHERE COL2 IN (SELECT B.A
13:45:32   4                    FROM (SELECT ROWNUM R, A
13:45:32   5                            FROM (SELECT COL2 A
13:45:32   6                                    FROM TTT
13:45:32   7                                   WHERE COL3 = 1
13:45:32   8                                  UNION
13:45:32   9                                  SELECT COL2 + 1 A FROM TTT WHERE COL3 = 1)) A,
13:45:32  10                         (SELECT ROWNUM R, A
13:45:32  11                            FROM (SELECT COL2 A FROM TTT WHERE COL3 = 1)) B
13:45:32  12                   WHERE A.R = B.R);COL1       COL2             COL3
---------- ---------- ----------
a          2004-01-01          1
a          2004-01-02          1
a          2004-01-03          1
a          2004-01-04          1
a          2004-01-05          1
a          2004-01-08          1
a          2004-01-09          1
a          2004-01-10          1
1          2004-01-11          1已选择9行。已用时间:  00: 00: 00.00
13:45:33 SQLPLUS>

Answer 2

哦.col1有一个值插入时为1.
所以就显示为1了.本来是a的.

Answer 3

日期（列2）會出現重復嗎？比如 ：a          2004-01-01          1
a          2004-01-02          1
a          2004-01-03          1
a          2004-01-04          1
a          2004-01-05          1
a          2004-01-05          0
a          2004-01-08          0
a          2004-01-08          1
a          2004-01-09          1
a          2004-01-10          1
1          2004-01-11          1

Answer 4

tiger_hs(tiger) 你好！
请问这个sql 是那个版本的？select a.* , count(*)
  from tab_name a
 where a.col3 = 1
   and a.col2 = next a.col2+1
  start with a.col2 = select (min(col2) from tab_name) 
 having (count*) > 3

Answer 5

yantao14 (yt) 
討厭，是你嗎？提的問題還挺難的說，

Answer 6

baojianjun(包子)怎么老是用繁体中文啊.

Answer 7

SQL> 
SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(2)
 COL2                                               DATE
 COL3                                               NUMBER(38)SQL> select * from t
  2  /CO COL2                 COL3                                                    
-- -------------- ----------                                                    
a  01-1月 -04              1                                                    
a  02-1月 -04              1                                                    
a  03-1月 -04              1                                                    
a  04-1月 -04              1                                                    
a  04-1月 -04              0                                                    
a  05-1月 -04              1                                                    
a  06-1月 -04              0                                                    
a  07-1月 -04              0                                                    
a  08-1月 -04              1                                                    
a  09-1月 -04              1                                                    
a  10-1月 -04              1                                                    CO COL2                 COL3                                                    
-- -------------- ----------                                                    
a  11-1月 -04              1                                                    12 rows selected.SQL> @x1
SQL> select a.*
  2    from t a,
  3    (
  4      select max(l) the_days,colx
  5        from (
  6   select max(level) l,a.col2,a.col2-max(level)+1 colx
  7     from t  a
  8   connect by a.col3 = 1 and a.col2 = prior a.col2+1
  9   start with a.col2 in (select distinct col2 from t where col3 = 1)
 10   group by a.col2
 11        ) group by colx
 12   ) b
 13   where a.col3 = 1 and a.col2 between b.colx and b.colx+b.the_days
 14  /CO COL2                 COL3                                                    
-- -------------- ----------                                                    
a  01-1月 -04              1                                                    
a  02-1月 -04              1                                                    
a  03-1月 -04              1                                                    
a  04-1月 -04              1                                                    
a  05-1月 -04              1                                                    
a  08-1月 -04              1                                                    
a  09-1月 -04              1                                                    
a  10-1月 -04              1                                                    
a  11-1月 -04              1                                                    9 rows selected.SQL> spool off

Answer 8

还是楼上的方法好:)学习!不过还少个条件.我补上09:27:56 liuyi>select * from ttt;COL1       COL2             COL3
---------- ---------- ----------
a          2004/02/11          1
a          2004/02/12          1
a          2004/01/01          1
a          2004/01/02          1
a          2004/01/03          1
a          2004/01/04          1
a          2004/01/05          1
a          2004/01/06          0
a          2004/01/07          0
a          2004/01/08          1
a          2004/01/09          1
a          2004/01/10          1
a          2004/01/11          1已选择13行。已用时间:  00: 00: 00.00
09:28:00 liuyi>select a.*
09:28:01   2      from ttt a,
09:28:01   3      (
09:28:01   4        select max(l) the_days,colx
09:28:01   5          from (
09:28:01   6      select max(level) l,a.col2,a.col2-max(level)+1 colx
09:28:01   7        from ttt  a
09:28:01   8      connect by a.col3 = 1 and a.col2 = prior a.col2+1
09:28:01   9      start with a.col2 in (select distinct col2 from ttt where col3 = 1)
09:28:01  10      group by a.col2
09:28:01  11          ) group by colx
09:28:01  12     ) b
09:28:01  13     where a.col3 = 1 and a.col2 between b.colx and b.colx+b.the_days
09:28:01  14      AND b.the_days>3;COL1       COL2             COL3
---------- ---------- ----------
a          2004/01/01          1
a          2004/01/02          1
a          2004/01/03          1
a          2004/01/04          1
a          2004/01/05          1
a          2004/01/08          1
a          2004/01/09          1
a          2004/01/10          1
a          2004/01/11          1已选择9行。已用时间:  00: 00: 00.01
09:28:02 liuyi>