续贴：SQL 2000查询问题，请sql77,(爱新觉罗.毓华)， (别人叫我东升哥)等高手进来

原贴http://topic.csdn.net/u/20100423/10/adb508f2-bba2-4c23-b371-c733d0e823d1.html其中爱新觉罗·毓华兄和别人叫我东升哥的解答在我给出的测试数据中是正确的，但是遇到一个问题，就是当我在原始数据库表中处理时，出现了很多我意料之外的数据，不知道是什么原因。可能跟sid有关系，因为我给出的测试数据中sid是连续的，而数据库表中sid并不一定是连续的，而且表中的数据也不只一天的，有很多天的。表中还有很多别的列。
另外，在出现的两条错误记录中，我需要删除一条，保留一条，也请帮忙写下语句。因为这个算不上完全的数据重复，就只是取时间间隔小于1分钟的两条中的一条。
麻烦了

解决方案 »

  1.   

    --sql
    from ta a where not exists(select 1 from ta where id > a.id)
      

  2.   

    等乌龟大侠,sid不连续,你可以利用一个排序列来实现
      

  3.   

    谢谢各位，其实错误的数据都是由一个因素引起的：短时间内（1分钟内）重复使用。
    我发出来的测试数据中，cno=2本来只应有两条，但是两次使用都造成了错误，都多产生了一条记录
                          cno=4有一次是正常的，另一次也造成了短时间重复使用的错误
                          cno=6就只有一次使用，但是却出错了，衍生了数据
    我需要达到的目的，就是把这些衍生出来的数据清除掉。在测试数据中在到的结果就是cno=2的数据保留两条，这两条时间间隔大于1分钟，cno=4的保留两条，cno=6的保留一条
      

  4.   

    忙了一晚上，终于搞定~with tb1 as(
    select cno from testtb group by cno,convert(char(10), ttime, 112) having count(*)=2--处理次数等于2的情况
    ),
    tb2 as(
    select * from testtb where cno in (select cno from tb1)
    ),
    tb3 as(
    select tb21.sid, 
           tb21.cno, 
           tb21.remsum as first_remsum, 
           tb22.remsum as later_remsum,
           tb21.ttime as first_ttime,
           tb22.ttime as later_ttime 
    from tb2 as tb21 join tb2 as tb22 on tb21.cno =tb22.cno and tb21.ttime<tb22.ttime
    ),
    tb4 as(
    select * from tb3 where convert(int,first_remsum)-convert(int,later_remsum)=1 and datediff(ss, first_ttime, later_ttime)>60
    ),
    tb5 as(
    select tb41.* from testtb as tb41 where tb41.cno in (select tb42.cno from tb4 as tb42) union all 
    select tb43.* from testtb as tb43 
    where cno in (select cno from testtb group by cno,convert(char(10), ttime, 112) having count(*)<2)--处理次数小于2的情况
    ),
    tb6 as(
    select tb51.* from testtb as tb51 where tb51.sid not in (select tb52.sid from tb5 as tb52)
    )select * from tb6/*
    查询结果:
    sid         cno        remsum     ttime
    ----------- ---------- ---------- -----------------------
    53          2          87         2010-03-01 06:21:28.000
    54          2          86         2010-03-01 06:21:31.000
    56          4          22         2010-03-01 06:28:20.000
    57          6          58         2010-03-01 07:20:20.000
    58          6          57         2010-03-01 07:20:22.000
    61          4          21         2010-03-01 13:29:20.000
    62          4          20         2010-03-01 13:29:23.000
    64          2          85         2010-03-01 16:29:20.000
    65          2          84         2010-03-01 16:29:25.000(9 行受影响)
    */
      

  5.   

    --貌似不用你那么复杂,仔细想了想,如下即可:
    create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
    --插入数据
    insert into testtb(cno,remsum,ttime)
              select '1','99','2010-03-01 05:35:28.000'
    union all select '2','87','2010-03-01 06:21:28.000'
    union all select '2','86','2010-03-01 06:21:31.000'
    union all select '3','22','2010-03-01 06:27:20.000'
    union all select '4','22','2010-03-01 06:28:20.000'
    union all select '6','58','2010-03-01 07:20:20.000'
    union all select '6','57','2010-03-01 07:20:22.000'
    union all select '5','45','2010-03-01 07:27:20.000'
    union all select '3','21','2010-03-01 12:29:20.000'
    union all select '4','21','2010-03-01 13:29:20.000'
    union all select '4','20','2010-03-01 13:29:23.000'
    union all select '5','44','2010-03-01 14:27:20.000'
    union all select '2','85','2010-03-01 16:29:20.000'
    union all select '2','84','2010-03-01 16:29:25.000'
    goselect t1.* from testtb t1 ,
    (
      select distinct cno , ttime from
      (
        select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t
      ) m where s <= 60
    ) t2 
    where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime
    order by t1.cno , t1.ttimedrop table testtb/*
    sid         cno        remsum     ttime                                                  
    ----------- ---------- ---------- ------------------------------------------------------ 
    53          2          87         2010-03-01 06:21:28.000
    54          2          86         2010-03-01 06:21:31.000
    64          2          85         2010-03-01 16:29:20.000
    65          2          84         2010-03-01 16:29:25.000
    56          4          22         2010-03-01 06:28:20.000
    61          4          21         2010-03-01 13:29:20.000
    62          4          20         2010-03-01 13:29:23.000
    57          6          58         2010-03-01 07:20:20.000
    58          6          57         2010-03-01 07:20:22.000（所影响的行数为 9 行）*/
      

  6.   

    --以下两条的速度,你测试下,看哪个合适.
    create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
    --插入数据
    insert into testtb(cno,remsum,ttime)
              select '1','99','2010-03-01 05:35:28.000'
    union all select '2','87','2010-03-01 06:21:28.000'
    union all select '2','86','2010-03-01 06:21:31.000'
    union all select '3','22','2010-03-01 06:27:20.000'
    union all select '4','22','2010-03-01 06:28:20.000'
    union all select '6','58','2010-03-01 07:20:20.000'
    union all select '6','57','2010-03-01 07:20:22.000'
    union all select '5','45','2010-03-01 07:27:20.000'
    union all select '3','21','2010-03-01 12:29:20.000'
    union all select '4','21','2010-03-01 13:29:20.000'
    union all select '4','20','2010-03-01 13:29:23.000'
    union all select '5','44','2010-03-01 14:27:20.000'
    union all select '2','85','2010-03-01 16:29:20.000'
    union all select '2','84','2010-03-01 16:29:25.000'
    go--这个速度可能快点
    select t1.* from testtb t1 ,
    (
      select distinct cno , ttime from
      (
        select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t
      ) m where s <= 60
    ) t2 
    where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime
    order by t1.cno , t1.ttime--这个速度可能慢点
    select distinct t1.* from testtb t1 ,
    (
      select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t
    ) t2 
    where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime and t2.s <= 60
    order by t1.cno , t1.ttime
    drop table testtb/*
    sid         cno        remsum     ttime                                                  
    ----------- ---------- ---------- ------------------------------------------------------ 
    53          2          87         2010-03-01 06:21:28.000
    54          2          86         2010-03-01 06:21:31.000
    64          2          85         2010-03-01 16:29:20.000
    65          2          84         2010-03-01 16:29:25.000
    56          4          22         2010-03-01 06:28:20.000
    61          4          21         2010-03-01 13:29:20.000
    62          4          20         2010-03-01 13:29:23.000
    57          6          58         2010-03-01 07:20:20.000
    58          6          57         2010-03-01 07:20:22.000（所影响的行数为 9 行）*/
      

  7.   

    --我的删除原理如下(不知道理解是否正确?)
    --1.如果相同CNO每天出现两条以上,且存在间隔<60S的,保留最大和最小的.(但是如果第一条和最后一条其间隔也小于60S怎么办?如果不存在间隔小于等于60S又怎么办?)
    --2.如果相同CNO每天出现两条,且间隔<=60S,保留时间小的一条.
    --3.如果相同CNO每天只出现一条,则不删除.
    create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
    --插入数据
    insert into testtb(cno,remsum,ttime)
              select '1','99','2010-03-01 05:35:28.000'
    union all select '2','87','2010-03-01 06:21:28.000'
    union all select '2','86','2010-03-01 06:21:31.000'
    union all select '3','22','2010-03-01 06:27:20.000'
    union all select '4','22','2010-03-01 06:28:20.000'
    union all select '6','58','2010-03-01 07:20:20.000'
    union all select '6','57','2010-03-01 07:20:22.000'
    union all select '5','45','2010-03-01 07:27:20.000'
    union all select '3','21','2010-03-01 12:29:20.000'
    union all select '4','21','2010-03-01 13:29:20.000'
    union all select '4','20','2010-03-01 13:29:23.000'
    union all select '5','44','2010-03-01 14:27:20.000'
    union all select '2','85','2010-03-01 16:29:20.000'
    union all select '2','84','2010-03-01 16:29:25.000'
    godelete testtb from testtb t1,
    (select distinct cno , ttime from (select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t) n where s <= 60) t2,
    (select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) >= 3) t3
    where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime and t1.cno = t3.cno and convert(varchar(10),t1.ttime,120) = t3.ttime
    and t1.ttime not in (select min(ttime) from testtb where cno = t1.cno) and t1.ttime not in (select max(ttime) from testtb where cno = t1.cno)delete testtb from testtb t1,
    (select distinct cno , ttime from (select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t) n where s <= 60) t2,
    (select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) = 2) t3
    where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime and t1.cno = t3.cno and convert(varchar(10),t1.ttime,120) = t3.ttime
    and t1.ttime not in (select min(ttime) from testtb where cno = t1.cno) select * from testtb order by cno , ttimedrop table testtb/*
    sid         cno        remsum     ttime                                                  
    ----------- ---------- ---------- ------------------------------------------------------ 
    52          1          99         2010-03-01 05:35:28.000
    53          2          87         2010-03-01 06:21:28.000
    65          2          84         2010-03-01 16:29:25.000
    55          3          22         2010-03-01 06:27:20.000
    60          3          21         2010-03-01 12:29:20.000
    56          4          22         2010-03-01 06:28:20.000
    62          4          20         2010-03-01 13:29:23.000
    59          5          45         2010-03-01 07:27:20.000
    63          5          44         2010-03-01 14:27:20.000
    57          6          58         2010-03-01 07:20:20.000（所影响的行数为 10 行）
    */--1,3点同上,2点更改为如下:
    --2.如果相同CNO每天出现两条,且间隔<=60S,保留时间大的一条.
    create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
    --插入数据
    insert into testtb(cno,remsum,ttime)
              select '1','99','2010-03-01 05:35:28.000'
    union all select '2','87','2010-03-01 06:21:28.000'
    union all select '2','86','2010-03-01 06:21:31.000'
    union all select '3','22','2010-03-01 06:27:20.000'
    union all select '4','22','2010-03-01 06:28:20.000'
    union all select '6','58','2010-03-01 07:20:20.000'
    union all select '6','57','2010-03-01 07:20:22.000'
    union all select '5','45','2010-03-01 07:27:20.000'
    union all select '3','21','2010-03-01 12:29:20.000'
    union all select '4','21','2010-03-01 13:29:20.000'
    union all select '4','20','2010-03-01 13:29:23.000'
    union all select '5','44','2010-03-01 14:27:20.000'
    union all select '2','85','2010-03-01 16:29:20.000'
    union all select '2','84','2010-03-01 16:29:25.000'
    godelete testtb from testtb t1,
    (select distinct cno , ttime from (select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t) n where s <= 60) t2,
    (select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) >= 3) t3
    where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime and t1.cno = t3.cno and convert(varchar(10),t1.ttime,120) = t3.ttime
    and t1.ttime not in (select min(ttime) from testtb where cno = t1.cno) and t1.ttime not in (select max(ttime) from testtb where cno = t1.cno)delete testtb from testtb t1,
    (select distinct cno , ttime from (select t.cno , convert(varchar(10),t.ttime,120) ttime, s = datediff(s ,ttime, (select top 1 ttime from testtb where cno = t.cno and convert(varchar(10),ttime,120) = convert(varchar(10),t.ttime,120) and ttime > t.ttime order by ttime)) from testtb t) n where s <= 60) t2,
    (select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) = 2) t3
    where t1.cno = t2.cno and convert(varchar(10),t1.ttime,120) = t2.ttime and t1.cno = t3.cno and convert(varchar(10),t1.ttime,120) = t3.ttime
    and t1.ttime not in (select max(ttime) from testtb where cno = t1.cno) select * from testtb order by cno , ttimedrop table testtb/*
    sid         cno        remsum     ttime                                                  
    ----------- ---------- ---------- ------------------------------------------------------ 
    52          1          99         2010-03-01 05:35:28.000
    53          2          87         2010-03-01 06:21:28.000
    65          2          84         2010-03-01 16:29:25.000
    55          3          22         2010-03-01 06:27:20.000
    60          3          21         2010-03-01 12:29:20.000
    56          4          22         2010-03-01 06:28:20.000
    62          4          20         2010-03-01 13:29:23.000
    59          5          45         2010-03-01 07:27:20.000
    63          5          44         2010-03-01 14:27:20.000
    58          6          57         2010-03-01 07:20:22.000（所影响的行数为 10 行）*/
      

  8.   

    再提供一个方案：select * from testtb as tb where tb.cno in(
    select tb1.cno from testtb as tb1 join testtb as tb2 on tb1.cno = tb2.cno
    where (datediff(ss, tb1.ttime, tb2.ttime)<60 and datediff(ss, tb1.ttime, tb2.ttime)>0) or
          (convert(int,tb1.remsum)-convert(int,tb2.remsum)>1)
    )/*
    结果:sid         cno        remsum     ttime
    ----------- ---------- ---------- -----------------------
    53          2          87         2010-03-01 06:21:28.000
    54          2          86         2010-03-01 06:21:31.000
    56          4          22         2010-03-01 06:28:20.000
    57          6          58         2010-03-01 07:20:20.000
    58          6          57         2010-03-01 07:20:22.000
    61          4          21         2010-03-01 13:29:20.000
    62          4          20         2010-03-01 13:29:23.000
    64          2          85         2010-03-01 16:29:20.000
    65          2          84         2010-03-01 16:29:25.000(9 行受影响)*/
      

  9.   

    这里假设每次remsum的增量至少为-1，多了这个假设减少不少麻烦
      

  10.   

    那是，被人指名的日后肯定都排进了（CSDN封神榜）！
      

  11.   

    上面要修改一下，加一句，忘了要限制同一天里。select * from testtb as tb where tb.cno in(
    select tb1.cno from testtb as tb1 join testtb as tb2 on tb1.cno = tb2.cno
    where (datediff(ss, tb1.ttime, tb2.ttime)<60 and datediff(ss, tb1.ttime, tb2.ttime)>0) or
          (convert(int,tb1.remsum)-convert(int,tb2.remsum)>1 and convert(char(10), tb1.ttime, 112)=convert(char(10), tb2.ttime, 112))
    )/*
    结果:sid         cno        remsum     ttime
    ----------- ---------- ---------- -----------------------
    53          2          87         2010-03-01 06:21:28.000
    54          2          86         2010-03-01 06:21:31.000
    56          4          22         2010-03-01 06:28:20.000
    57          6          58         2010-03-01 07:20:20.000
    58          6          57         2010-03-01 07:20:22.000
    61          4          21         2010-03-01 13:29:20.000
    62          4          20         2010-03-01 13:29:23.000
    64          2          85         2010-03-01 16:29:20.000
    65          2          84         2010-03-01 16:29:25.000(9 行受影响)*/