sql server2000查询问题

我没有吃饭 2010-04-23 10:25:37 




--生成测试数据

--创建表，cno=编号，remsum=余额，ttime=时间

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'


正常的情况是：同一编号（cno）一天最多只能出现两次，且每一次出现，余额(remsum)就会比上次少1，如果当天
同一编号有两次记录，那么两次记录的时间间隔不少于1分钟

现出现异常数据，即某些编号同一天出现次数超过两次，或两次记录的时间间隔小于1分钟，需要查询出这些异常数据

希望有两种查询结果：
一、同一编号同一天出现次数大于2，且两次记录之间的时间间隔小于1分钟
二、同一编号同一天出现次数大于2，或两次记录之间的时间间隔小于1分钟

...全文
154 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2010-04-24
  • 打赏
  • 举报
 回复
--仔细想了想,如下即可:
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



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 行）



*/
dawugui 2010-04-23
  • 打赏
  • 举报
 回复 
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 m.* from testtb m where exists (select 1 from

(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n

where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))

union all

select m.* from testtb m , testtb n 

where m.cno = n.cno and convert(varchar(10),m.ttime,120) =  convert(varchar(10),n.ttime,120) and m.ttime <> n.ttime and abs(datediff(ss,m.ttime,n.ttime)) <= 60

and exists (select 1 from

(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) = 2) t

where t.cno = m.cno and t.ttime = convert(varchar(10),m.ttime,120))

order by cno , 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 行）



*/
dawugui 2010-04-23
  • 打赏
  • 举报
 回复 
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 m.* from testtb m where exists (select 1 from

(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n

where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))

union all

select m.* from testtb m , testtb n 

where m.cno = n.cno and convert(varchar(10),m.ttime,120) =  convert(varchar(10),n.ttime,120) and m.ttime <> n.ttime and abs(datediff(ss,m.ttime,n.ttime)) <= 60

and not exists (select 1 from

(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) t

where t.cno = m.cno and t.ttime = convert(varchar(10),m.ttime,120))

order by cno , 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 行）





*/
dawugui 2010-04-23
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 getdate123 的回复:]
编号6为什么不正常呢，虽然它只出现了两次，但是两条记录的时间间隔却只有2秒，所以仍然是不正常数据[/Quote]--前面我理解错了.
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 m.* from testtb m where exists (select 1 from

(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n

where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))

union all

select m.* from testtb m , testtb n 

where m.cno = n.cno and convert(varchar(10),m.ttime,120) =  convert(varchar(10),n.ttime,120) and m.ttime < n.ttime and datediff(ss,m.ttime,n.ttime) <= 60

and not exists (select 1 from

(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) t

where t.cno = m.cno and t.ttime = convert(varchar(10),m.ttime,120))

order by cno , 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



（所影响的行数为 8 行）



*/
东那个升 2010-04-23
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 ldslove 的回复:]
SQL code
select * from testtb a where
exists(select 1 from testtb where cno=a.cno group by cno having count(*)>2)
or exists(select 1 from testtb where cno=a.cno and a.sid<>sid and abs(dated……
[/Quote]

写错了




select *  from testtb a where 

exists(select 1     from testtb  where cno=a.cno group by cno having count(*)>2)

or 

(exists(select 1     from testtb  where cno=a.cno group by cno having count(*)=2)

and

exists(select 1 from testtb where cno=a.cno and a.sid<>sid  and abs(datediff(ss,ttime,a.ttime))<60)

)





        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 行受影响)
我是小数位 2010-04-23
  • 打赏
  • 举报
 回复
楼主怎么没说那个答案是对的啊？我怎么和他拉有点不一样呢，你那数据是不是也有点问题，1的那项只有一次，而2出现了四次，这个是怎么回事呢？按理说2用且的时候不会出来啊!
我没有吃饭 2010-04-23
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 dawugui 的回复:]
希望有两种查询结果：
一、同一编号同一天出现次数大于2，且两次记录之间的时间间隔小于1分钟
二、同一编号同一天出现次数大于2，或两次记录之间的时间间隔小于1分钟

--你这个需求可以整合成:
同一编号同一天出现次数大于2即可.
[/Quote]
谢谢，不过这个结果是不一样的。
以上的测试数据中，其实正常的数据只有


'1','99','2010-03-01 05:35:28.000'

'3','22','2010-03-01 06:27:20.000'

'5','45','2010-03-01 07:27:20.000'

'3','21','2010-03-01 12:29:20.000'

'5','44','2010-03-01 14:27:20.000'


编号6为什么不正常呢，虽然它只出现了两次，但是两条记录的时间间隔却只有2秒，所以仍然是不正常数据
我没有吃饭 2010-04-23
  • 打赏
  • 举报
 回复
现在有点忙，一会有空测试一下，呵呵，如果解决，今天结贴，哈哈
htl258_Tony 2010-04-23
  • 打赏
  • 举报
 回复 
;with t as(

    select * 

    from testtb t 

    where exists(

        select 1 from testtb 

        where cno=t.cno and convert(char,ttime,23)=convert(char,t.ttime,23) 

        having count(1)>2)

)

select * from t a 

where exists(select 1 from t where cno=a.cno and abs(datediff(ss,ttime,a.ttime))<60)

/*

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

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



(7 行受影响)

*/
5#手快漏了个条件cno=a.cno 补上
老黎 2010-04-23
  • 打赏
  • 举报
 回复 


--一、同一编号同一天出现次数大于2，且两次记录之间的时间间隔小于1分钟

select *

from 

    testtb a

where 

    exists(select 1 from testtb where cno = a.cno and ttime <> a.ttime and abs(datediff(ss,ttime,a.ttime))<60)

order by

    cno

--结果

/*

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

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

*/
--小F-- 2010-04-23
  • 打赏
  • 举报
 回复 
搞半天才看清楚是2000的

select

 *  

from

 testtb a 

where 

 exists(select 1 from testtb  where cno=a.cno group by cno having count(*)>2)

or

 exists(select 1 from testtb where cno=a.cno and a.sid<>sid  and abs(datediff(ms,ttime,a.ttime))<60
东那个升 2010-04-23
  • 打赏
  • 举报
 回复 
select *  from testtb a where 

exists(select 1	 from testtb  where cno=a.cno group by cno having count(*)>2)

or exists(select 1 from testtb where cno=a.cno and a.sid<>sid  and abs(datediff(ms,ttime,a.ttime))<60)



        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

         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



(7 行受影响)
dawugui 2010-04-23
  • 打赏
  • 举报
 回复
希望有两种查询结果：
一、同一编号同一天出现次数大于2，且两次记录之间的时间间隔小于1分钟
二、同一编号同一天出现次数大于2，或两次记录之间的时间间隔小于1分钟

--你这个需求可以整合成:
同一编号同一天出现次数大于2即可.
--小F-- 2010-04-23
  • 打赏
  • 举报
 回复 
--生成测试数据

--创建表，cno=编号，remsum=余额，ttime=时间

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'



select

 *

from

(

select *, (select count(1) from testtb where cno=t.cno and datediff(dd,ttime,t.ttime)=0 and sid<=t.sid) as  num from testtb t

)t

where num>2



drop table testtb

/*sid         cno        remsum     ttime                   num

----------- ---------- ---------- ----------------------- -----------

62          4          20         2010-03-01 13:29:23.000 3

64          2          85         2010-03-01 16:29:20.000 3

65          2          84         2010-03-01 16:29:25.000 4



(3 行受影响)*/
dawugui 2010-04-23
  • 打赏
  • 举报
 回复 
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'



select m.* from testtb m where exists (select 1 from

(select cno , convert(varchar(10),ttime,120) ttime from testtb group by cno , convert(varchar(10),ttime,120) having count(1) > 2) n

where n.cno = m.cno and n.ttime = convert(varchar(10),m.ttime,120))

order by m.cno , m.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



（所影响的行数为 7 行）



*/
liushengsu 2010-04-23
  • 打赏
  • 举报
 回复
学习学习呢。嘿嘿
htl258_Tony 2010-04-23
  • 打赏
  • 举报
 回复 
--生成测试数据

--创建表，cno=编号，remsum=余额，ttime=时间

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'



;with t as(

	select * 

	from testtb t 

	where exists(

		select 1 from testtb 

		where cno=t.cno and convert(char,ttime,23)=convert(char,t.ttime,23) 

		having count(1)>2)

)

select * from t a 

where exists(select 1 from t where abs(datediff(ss,ttime,a.ttime))<60)

/*

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

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



(7 行受影响)

*/
SQL77 2010-04-23
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 sql77 的回复:]
SQL code
--生成测试数据
--创建表，cno=编号，remsum=余额，ttime=时间
create table testtb(sid int identity(52,1),cno char(10),remsum varchar(10),ttime datetime)
--插入数据
insert into testtb(cno,remsum,ttime)
……
[/Quote]
第二种结果改为OR EXISTS就行了
htl258_Tony 2010-04-23
  • 打赏
  • 举报
 回复 
select * 

from testtb t 

where exists(

	select 1 from testtb 

	where cno=t.cno and convert(char,ttime,23)=convert(char,t.ttime,23) 

	having count(1)>2)
SQL77 2010-04-23
  • 打赏
  • 举报
 回复 
--生成测试数据

--创建表，cno=编号，remsum=余额，ttime=时间

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'





select *,

(select count(1) from testtb where cno=t.cno and datediff(dd,ttime,t.ttime)=0 and sid<=t.sid) num into #t

from testtb t 



select * from testtb  T

where cno IN(

select cno from #t where num>2)

 AND EXISTS(SELECT 1 FROM TESTTB WHERE CNO=T.CNO AND ABS(DATEDIFF(MI,TTIME,T.TTIME))<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

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



(7 行受影响)
加载更多回复（2）
高效的SQLSERVER分页查询(推荐)
Sqlserver数据库分页查询一直是Sqlserver的短板，闲来无事，想出几种方法，假设有表ARTICLE,字段ID、YEAR...(其他省略)，数据53210条(客户真实数据，量不大)，分页查询每页30条，查询第1500页（即第45001-45030条数据），字段ID聚集索引，YEAR无索引，Sqlserver版本：2008R2 第一种方案、最简单、普通的方法：
彻底卸载SQL Server
很多人在面对SQL Server出现的一些无法解决的问题时，会选择卸载重装，但是SQL Server卸载不干净的话，后续安装过程会出现很多问题，因此安全干净的卸载软件是很重要的步骤。 1、在运行中输入services.msc,然后找到所有跟Sql Server有关的服务，并且停止这些服务。（一定要先停止服务再卸载，否则后续卸载文件过程中会失败） 2、进入控制面板，点击“程序卸载” 3...
win10、win7安装SQLServer2000最详细教程
win10安装SQLServer2000最详细教程 最近，自己尝试在win10本地安装SQLServer2000，网上找了很多资料（有好多坑），浪费了好些时间，顺便记录下笔记，希望能给你节省宝贵的时间。 一、安装包链接（SQL2000开发版+SP4）： 链接：https://pan.baidu.com/s/1VJFyaQHjFXw7_xuRLylpLA 提取码：qwas 二、安装教程： 1、打开解压目录SQL2000开发版+SP4(支持WIN10安装) 2、运行“右键获取管理员权限.reg”。 3、找到C:
sql2000 mysql_sql2000迷你版 超精简版SQL Server 2000数据库下载
这个《sql2000迷你版》其实就是一个超精简的SQL Server 2000数据库版本！《sql2000迷你版》的好处是，不需要安装庞大的sql2000版本，并且你的电脑可能还用不了，因为不是服务器，而这个普通电脑就可以用了，它是一个精简版SQL Server 2000数据库。sql2000迷你版又称sql2000精简版，这个版本是基于MSDE2000 SP4及SQL2000 SP4企业版合并后...
SQL SERVER 2012链接到SQL SERVER 2000的问题解决案例
1. 在mssql2012增加2000的链接受服务器添加失败，提示以下信息       2.SQL Server2012 增加 SQL Server2000 服务器的链接服务器 先安装这个组件，然后可以成功在sql2012服务器添加mssql2000链接服务器 ，要用以下的语句添加   EXEC sp_addlinkedserver @server='ITSV', @sr...
MS-SQL Server

34,594

社区成员

254,590

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章