求SQL

表tempid      cgicode           starttime               endtime
1         A                2010-01-01              2010-02-01
2         A                2010-02-01              2010-03-01
3         A                2010-03-01
4         B                2010-02-01              2010-03-01
5         B                2010-03-01
6         C                2010-01-01             2010-02-01
7         C                2010-03-01
8         C                2010-04-01
9         D                2010-01-01             2010-02-01
10        D                2010-03-01
11        E                2010-02-01             2010-03-01
12        E                2010-04-01
13        E                2010-05-01想要的结果如下：

id       cgicode           starttime               endtime 6         C                2010-01-01             2010-02-01
7         C                2010-03-01
8         C                2010-04-01
9         D                2010-01-01             2010-02-01
10        D                2010-03-01
11        E                2010-02-01             2010-03-01
12        E                2010-04-01
13        E                2010-05-01查处endtime有异常的数据,cgicode为C，是因为id为7的没有endtime，
cgicode为D是因为下一条开始日并不等于上一条的结束日
cgicode为E是因为cgicode为C和cgicode为D的总和原因，
求助各位了

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

1.同一个cgicode的endtime要和上一条的starttime相同
2.同一个cgicode不能同时有两条endtime为null的记录
3.同一个cgicode的最后一条记录endtime必须为空
同一个cgicode只要有一个地方发生了异常，则整个cgicode的记录都算作异常的？
从你的结果中来看的话
不就是select * from temp where cgicod in ('C','D','E')
可以写个存储过程进行处理，sql估计会比较复杂
SELECT * FROM temp WHERE cgicode IN (
SELECT cgicode FROM temp t
  WHERE  (t.endtime IS NULL AND EXISTS(SELECT 1 FROM temp a WHERE t.cgicode=a.cgicode AND a.id>t.id))  OR--endtime为空但不是最后一条
         (t.endtime IS NOT NULL AND NOT EXISTS(SELECT 1 FROM temp b WHERE b.id>t.id))  OR--最后一条但endtime不为空
         (NOT EXISTS (SELECT 1 FROM temp c WHERE c.id=t.id AND c.starttime=t.endtime)    --没有后继
          AND NOT EXISTS(SELECT 1 FROM temp d WHERE d.id=t.id AND  t.starttime=d.endtime)--也没有前驱
          AND (SELECT COUNT(*) FROM temp e WHERE t.id=e.id)<>1 )--并且不是一条记录
          )    ;
这样应该能满足你的要求，但是太复杂了
建议在id列建个索引，不然上面的sql全部全表扫描，在数据量很大的时候运行时间估计会让你抓狂
Select *
  From t
Where Exists (Select 1 From
       (Select Cgicode
          From (Select Id,
                       Cgicode,
                       Starttime,
                       Endtime,
                       Lag(Endtime) Over(Partition By Cgicode Order By Starttime) l_Endtime,
                       Lead(Cgicode) Over(Order By Id) n_Cgicode
                  From t) T1
         Where Starttime <> l_Endtime
            Or (Endtime Is Null And Endtime = l_Endtime)
            Or (Endtime Is Not Null And Cgicode <> n_Cgicode)) t2
        Where t.cgicode = t2.cgicode)
凑热闹
运行了楼上的sql，结果为
1 A 2010-1-1 2010-2-1
2 A 2010-2-1 2010-3-1
3 A 2010-3-1
6 C 2010-1-1 2010-2-1
7 C 2010-3-1
8 C 2010-4-1
9 D 2010-1-1 2010-2-1
10 D 2010-3-1
11 E 2010-2-1 2010-3-1
12 E 2010-4-1
13 E 2010-5-1
SQL> select * from t;        ID C STARTTIME  ENDTIME
---------- - ---------- ----------
         1 A 2010-01-01 2010-02-01
         2 A 2010-02-01 2010-03-01
         3 A 2010-03-01
         4 B 2010-02-01 2010-03-01
         5 B 2010-03-01
         6 C 2010-01-01 2010-02-01
         7 C 2010-03-01
         8 C 2010-04-01
         9 D 2010-01-01 2010-02-01
        10 D 2010-03-01
        11 E 2010-02-01 2010-03-01                                                                                                                            ID C STARTTIME  ENDTIME
---------- - ---------- ----------
        12 E 2010-04-01
        13 E 2010-05-01                                                                                                                               已选择13行。已用时间:  00: 00: 00.01SQL> Select *
  2    From t
  3   Where Exists (Select 1 From
  4         (Select Cgicode
  5            From (Select Id,
  6                         Cgicode,
  7                         Starttime,
  8                         Endtime,
  9                         Lag(Endtime) Over(Partition By Cgicode Order By Starttime) l_Endtime,
10                         Lead(Cgicode) Over(Order By Id) n_Cgicode
11                    From t) T1
12           Where Starttime <> l_Endtime
13              Or (Endtime Is Null And Endtime = l_Endtime)
14              Or (Endtime Is Not Null And Cgicode <> n_Cgicode)) t2
15          Where t.cgicode = t2.cgicode);        ID C STARTTIME  ENDTIME
---------- - ---------- ----------
         8 C 2010-04-01
         7 C 2010-03-01
         6 C 2010-01-01 2010-02-01
        10 D 2010-03-01
         9 D 2010-01-01 2010-02-01
        13 E 2010-05-01
        12 E 2010-04-01
        11 E 2010-02-01 2010-03-01                                                                                                                    已选择8行。已用时间:  00: 00: 00.01
不能把，我这正常啊
很奇怪呢，我建的表名叫temp。把你sql里的t用temp代替了，出结果是13行。
create table t as select * from temp;
然后再跑你的sql，出来11行，cgicode为B的以外的其他行都出来了。
我用的plsql developer
SQL> Select *
  2    From t
  3   Where Exists (Select 1 From
  4         (Select Cgicode
  5            From (Select Id,
  6                         Cgicode,
  7                         Starttime,
  8                         Endtime,
  9                         Lag(Endtime) Over(Partition By Cgicode Order By Starttime) l_Endtime,
10                         Lead(Cgicode) Over(Order By Id) n_Cgicode
11                    From t) T1
12           Where Starttime <> l_Endtime
13              Or (Endtime Is Null And Endtime = l_Endtime)
14              Or (Endtime Is Not Null And Cgicode <> n_Cgicode)) t2
15          Where t.cgicode = t2.cgicode);

ID                   CGICODE              STARTTIME            ENDTIME
-------------------- -------------------- -------------------- --------------------
3                    A                    2010-3-1
2                    A                    2010-2-1             2010-3-1
1                    A                    2010-1-1             2010-2-1
10                   D                    2010-3-1
9                    D                    2010-1-1             2010-2-1
13                   E                    2010-5-1
12                   E                    2010-4-1
11                   E                    2010-2-1             2010-3-1
8                    C                    2010-4-1
7                    C                    2010-3-1
6                    C                    2010-1-1             2010-2-1

11 rows selected

SQL>    select * from t;

ID                   CGICODE              STARTTIME            ENDTIME
-------------------- -------------------- -------------------- --------------------
1                    A                    2010-1-1             2010-2-1
2                    A                    2010-2-1             2010-3-1
3                    A                    2010-3-1
4                    B                    2010-2-1             2010-3-1
5                    B                    2010-3-1
6                    C                    2010-1-1             2010-2-1
7                    C                    2010-3-1
8                    C                    2010-4-1
9                    D                    2010-1-1             2010-2-1
10                   D                    2010-3-1
11                   E                    2010-2-1             2010-3-1
12                   E                    2010-4-1
13                   E                    2010-5-1

13 rows selected

SQL>
select * from t where endtime is null结果是什么
7行数据
3 A 2010-3-1
5 B 2010-3-1
7 C 2010-3-1
8 C 2010-4-1
10 D 2010-3-1
12 E 2010-4-1
13 E 2010-5-1
Select Id,
                       Cgicode,
                       Starttime,
                       Endtime,
                       Lag(Endtime) Over(Partition By Cgicode Order By Starttime) l_Endtime,
                       Lead(Cgicode) Over(Order By Id) n_Cgicode
                  From t
这个呢
13行
1 A 2010-1-1 2010-2-1 D
10 D 2010-3-1 2010-2-1 E
11 E 2010-2-1 2010-3-1 E
12 E 2010-4-1 2010-3-1 E
13 E 2010-5-1 A
2 A 2010-2-1 2010-3-1 2010-2-1 A
3 A 2010-3-1 2010-3-1 B
4 B 2010-2-1 2010-3-1 B
5 B 2010-3-1 2010-3-1 C
6 C 2010-1-1 2010-2-1 C
7 C 2010-3-1 2010-2-1 C
8 C 2010-4-1 D
9 D 2010-1-1 2010-2-1
SQL> set timing on
SQL> set linesize 150
SQL> Select Id,
  2                         Cgicode,
  3                         Starttime,
  4                         Endtime,
  5                         Lag(Endtime) Over(Partition By Cgicode Order By Starttime) l_Endtime,
  6                         Lead(Cgicode) Over(Order By Id) n_Cgicode
  7                    From t;        ID C STARTTIME      ENDTIME        L_ENDTIME      N
---------- - -------------- -------------- -------------- -
         1 A 01-1月 -10     01-2月 -10                    A
         2 A 01-2月 -10     01-3月 -10     01-2月 -10     A
         3 A 01-3月 -10                    01-3月 -10     B
         4 B 01-2月 -10     01-3月 -10                    B
         5 B 01-3月 -10                    01-3月 -10     C
         6 C 01-1月 -10     01-2月 -10                    C
         7 C 01-3月 -10                    01-2月 -10     C
         8 C 01-4月 -10                                   D
         9 D 01-1月 -10     01-2月 -10                    D
        10 D 01-3月 -10                    01-2月 -10     E
        11 E 01-2月 -10     01-3月 -10                    E                                                                                                   ID C STARTTIME      ENDTIME        L_ENDTIME      N
---------- - -------------- -------------- -------------- -
        12 E 01-4月 -10                    01-3月 -10     E
        13 E 01-5月 -10                                                                                                                               已选择13行。已用时间:  00: 00: 00.00
SQL> Select *
  2    From t
  3   Where Exists (Select 1 From
  4         (Select Cgicode
  5            From (Select Id,
  6                         Cgicode,
  7                         Starttime,
  8                         Endtime,
  9                         Lag(Endtime) Over(Partition By Cgicode Order By Starttime) l_Endtime,
10                         Lead(Cgicode) Over(Order By Id) n_Cgicode
11                    From t) T1
12           Where Starttime <> l_Endtime
13              Or (Endtime Is Null And Endtime = l_Endtime)
14              Or (Endtime Is Not Null And Cgicode <> n_Cgicode)) t2
15          Where t.cgicode = t2.cgicode)
16  ;        ID C STARTTIME      ENDTIME
---------- - -------------- --------------
         8 C 01-4月 -10
         7 C 01-3月 -10
         6 C 01-1月 -10     01-2月 -10
        10 D 01-3月 -10
         9 D 01-1月 -10     01-2月 -10
        13 E 01-5月 -10
        12 E 01-4月 -10
        11 E 01-2月 -10     01-3月 -10                                                                                                                已选择8行。已用时间:  00: 00: 00.00
已经崩溃了。
神啊，救救我吧
SQL>
SQL> Select *
  2      From t
  3     Where Exists (Select 1 From
  4           (Select Cgicode
  5              From (Select Id,
  6                           Cgicode,
  7                           Starttime,
  8                           Endtime,
  9                           Lag(Endtime) Over(Partition By Cgicode Order By Starttime) l_Endtime,
10                           Lead(Cgicode) Over(Order By Id) n_Cgicode
11                      From t) T1
12             Where Starttime <> l_Endtime
13                Or (Endtime Is Null And Endtime = l_Endtime)
14                Or (Endtime Is Not Null And Cgicode <> n_Cgicode)) t2
15            Where t.cgicode = t2.cgicode)
16    ;

ID                   CGICODE              STARTTIME            ENDTIME
-------------------- -------------------- -------------------- --------------------
3                    A                    2010-3-1
2                    A                    2010-2-1             2010-3-1
1                    A                    2010-1-1             2010-2-1
10                   D                    2010-3-1
9                    D                    2010-1-1             2010-2-1
13                   E                    2010-5-1
12                   E                    2010-4-1
11                   E                    2010-2-1             2010-3-1
8                    C                    2010-4-1
7                    C                    2010-3-1
6                    C                    2010-1-1             2010-2-1

11 rows selected

SQL>