解决方案 »
- SQL Server 存储过程的经典分页
- sql 2005 management studio无法启动
- 多表查詢中,A表查B表有相同記錄,就取出,如果找不到相同數據,那麼麼在A表就顯示空白.保證顯示的數據量還是A表的. ???
- 关于链接服务器的一点问题
- 用SQL SERVER 2005导入数据的时候出现这种情况,不知道怎么解决?
- 如下的sql语句有错误吗?大家帮我理解下呗
- 急求一个去字符串的问题
- 一个看是很需要但很难做出来的问题
- 有人知道什么地方有ORALCE的电子书download
- 产生模拟数据库!!
- mysql的安装程序有100多兆,discuz里的mysql才10几兆,差别咋这么大?
- 求重复数据过滤问题解决方法
where exists(select 1 from tb where Moldid=a.Moldid
and Moid<>a.Moid)
and (select 1 from tb where Moldid=a.Moldid-1
and a.starttime betweem starttime and endtime)
--来个繁琐的!create table tb(Moid varchar(10),Moldid varchar(20),starttime datetime,endtime datetime)
insert into tb
select '20010750', 'ITM9812025', '2011-05-04 00:00:00', '2011-05-13 22:20:00' union all
select '20010764', 'ITM9812008', '2011-05-04 00:00:00', '2011-05-05 11:33:00' union all
select '20010765', 'ITM9812008', '2011-05-06 00:00:00', '2011-06-06 02:26:00' union all
select '20010766', 'ITM9812008', '2011-05-08 00:00:00', '2011-06-06 02:26:00' union all
select '20010767', '(DU098)', '2011-05-04 00:00:00', '2011-06-06 02:26:00' union all
select '20010768', '(DU098)', '2011-05-08 00:00:00', '2011-06-06 02:26:00' union all
select '20010779', '(DU104)', '2011-05-09 00:00:00', '2011-05-13 22:20:00' union all
select '20010780', '(DU104)', '2011-05-10 00:00:00', '2011-05-20 13:13:00' union all
select '20010781', '(DU104)', '2011-05-12 00:00:00', '2011-05-20 13:13:00'
gowith cte as
(
select *,px = row_number() over (partition by moldid order by getdate())
from tb
),ctf as
(
select *
from cte t
where exists (select 1 from cte where moldid = t.moldid and px = t.px - 1 and t.starttime between starttime and endtime)
)select *
from cte t
where exists (select 1 from ctf where moldid = t.moldid and px = t.px + 1 and starttime between t.starttime and t.endtime)
union
select * from ctf
order by moldid descdrop table tb
/*Moid Moldid starttime endtime px
---------- -------------------- ----------------------- ----------------------- --------------------
20010765 ITM9812008 2011-05-06 00:00:00.000 2011-06-06 02:26:00.000 2
20010766 ITM9812008 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000 3
20010779 (DU104) 2011-05-09 00:00:00.000 2011-05-13 22:20:00.000 1
20010780 (DU104) 2011-05-10 00:00:00.000 2011-05-20 13:13:00.000 2
20010781 (DU104) 2011-05-12 00:00:00.000 2011-05-20 13:13:00.000 3
20010767 (DU098) 2011-05-04 00:00:00.000 2011-06-06 02:26:00.000 1
20010768 (DU098) 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000 2(7 行受影响)
create table tb(Moid int,Moldid char(20),starttime datetime,endtime datetime)
insert into tb
select 20010750,'ITM9812025','2011-05-04 00:00:00', '2011-05-13 22:20:00' union
select 20010764,'ITM9812008','2011-05-04 00:00:00', '2011-05-05 11:33:00' union
select 20010765,'ITM9812008','2011-05-06 00:00:00', '2011-06-06 02:26:00' union
select 20010766,'ITM9812008','2011-05-08 00:00:00', '2011-06-06 02:26:00' union
select 20010767,'DU098','2011-05-04 00:00:00', '2011-06-06 02:26:00' union
select 20010768,'DU098','2011-05-08 00:00:00', '2011-06-06 02:26:00' union
select 20010779,'DU104','2011-05-09 00:00:00', '2011-05-13 22:20:00' union
select 20010780,'DU104','2011-05-10 00:00:00', '2011-05-20 13:13:00' union
select 20010781,'DU104','2011-05-12 00:00:00', '2011-05-20 13:13:00' select * from tb a
where exists(select 1 from tb where Moldid=a.Moldid
and Moid<>a.Moid)
and exists (select 1 from tb where (Moid=a.Moid-1
and a.starttime between starttime and endtime)
or (Moid=a.Moid+1
and starttime between a.starttime and a.endtime))
/*
Moid Moldid starttime endtime
----------- -------------------- ----------------------- -----------------------
20010765 ITM9812008 2011-05-06 00:00:00.000 2011-06-06 02:26:00.000
20010766 ITM9812008 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000
20010767 DU098 2011-05-04 00:00:00.000 2011-06-06 02:26:00.000
20010768 DU098 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000
20010779 DU104 2011-05-09 00:00:00.000 2011-05-13 22:20:00.000
20010780 DU104 2011-05-10 00:00:00.000 2011-05-20 13:13:00.000
20010781 DU104 2011-05-12 00:00:00.000 2011-05-20 13:13:00.000
--看差,你本来有排序的字段create table tb(Moid varchar(10),Moldid varchar(20),starttime datetime,endtime datetime)
insert into tb
select '20010750', 'ITM9812025', '2011-05-04 00:00:00', '2011-05-13 22:20:00' union all
select '20010764', 'ITM9812008', '2011-05-04 00:00:00', '2011-05-05 11:33:00' union all
select '20010765', 'ITM9812008', '2011-05-06 00:00:00', '2011-06-06 02:26:00' union all
select '20010766', 'ITM9812008', '2011-05-08 00:00:00', '2011-06-06 02:26:00' union all
select '20010767', '(DU098)', '2011-05-04 00:00:00', '2011-06-06 02:26:00' union all
select '20010768', '(DU098)', '2011-05-08 00:00:00', '2011-06-06 02:26:00' union all
select '20010779', '(DU104)', '2011-05-09 00:00:00', '2011-05-13 22:20:00' union all
select '20010780', '(DU104)', '2011-05-10 00:00:00', '2011-05-20 13:13:00' union all
select '20010781', '(DU104)', '2011-05-12 00:00:00', '2011-05-20 13:13:00'
gowith ctf as
(
select *
from tb t
where exists (select 1 from tb where moldid = t.moldid and Moid = t.Moid - 1 and t.starttime between starttime and endtime)
)select *
from tb t
where exists (select 1 from ctf where moldid = t.moldid and Moid = t.Moid + 1 and starttime between t.starttime and t.endtime)
union
select * from ctf
order by moldid descdrop table tb
/*Moid Moldid starttime endtime
---------- -------------------- ----------------------- -----------------------
20010765 ITM9812008 2011-05-06 00:00:00.000 2011-06-06 02:26:00.000
20010766 ITM9812008 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000
20010779 (DU104) 2011-05-09 00:00:00.000 2011-05-13 22:20:00.000
20010780 (DU104) 2011-05-10 00:00:00.000 2011-05-20 13:13:00.000
20010781 (DU104) 2011-05-12 00:00:00.000 2011-05-20 13:13:00.000
20010767 (DU098) 2011-05-04 00:00:00.000 2011-06-06 02:26:00.000
20010768 (DU098) 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000(7 行受影响)
Moid字段是不一定是按顺序来的,可能会出现以下情况Moid Moldid starttime endtime
20010779 (DU104) 2011-05-09 00:00:00.000 2011-05-13 22:20:00.000
20010885 (DU104) 2011-05-10 00:00:00.000 2011-05-20 13:13:00.000
20010965 (DU104) 2011-05-12 00:00:00.000 2011-05-20 13:13:00.000
那就三楼吧!自己做一个排序的字段就行! row_number() over (partition by moldid order by [这里写你按什么排序的])
create table #tb(Moid varchar(10),Moldid varchar(20),starttime datetime,endtime datetime)
insert into #tb
select '20010750', 'ITM9812025', '2011-05-04 00:00:00', '2011-05-13 22:20:00' union all
select '20010764', 'ITM9812008', '2011-05-04 00:00:00', '2011-05-05 11:33:00' union all
select '20010765', 'ITM9812008', '2011-05-06 00:00:00', '2011-06-06 02:26:00' union all
select '20010766', 'ITM9812008', '2011-05-08 00:00:00', '2011-06-06 02:26:00' union all
select '20010767', '(DU098)', '2011-05-04 00:00:00', '2011-06-06 02:26:00' union all
select '20010768', '(DU098)', '2011-05-08 00:00:00', '2011-06-06 02:26:00' union all
select '20010779', '(DU104)', '2011-05-09 00:00:00', '2011-05-13 22:20:00' union all
select '20010780', '(DU104)', '2011-05-10 00:00:00', '2011-05-20 13:13:00' union all
select '20010781', '(DU104)', '2011-05-12 00:00:00', '2011-05-20 13:13:00'
gowith atc as
(
select num = (row_number() over( partition by Moldid order by Moid )),* from #tb
)select * from atc a
where exists(select 1 from atc b where b.Moldid=a.Moldid and b.num=a.num+1 and (b.starttime between a.starttime and a.endtime))
or exists (select 1 from atc c where c.Moldid=a.Moldid and c.num=a.num-1 and (a.starttime between c.starttime and c.endtime))
order by Moid
*/
num Moid Moldid starttime endtime
-------------------- ---------- -------------------- ----------------------- -----------------------
2 20010765 ITM9812008 2011-05-06 00:00:00.000 2011-06-06 02:26:00.000
3 20010766 ITM9812008 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000
1 20010767 (DU098) 2011-05-04 00:00:00.000 2011-06-06 02:26:00.000
2 20010768 (DU098) 2011-05-08 00:00:00.000 2011-06-06 02:26:00.000
1 20010779 (DU104) 2011-05-09 00:00:00.000 2011-05-13 22:20:00.000
2 20010780 (DU104) 2011-05-10 00:00:00.000 2011-05-20 13:13:00.000
3 20010781 (DU104) 2011-05-12 00:00:00.000 2011-05-20 13:13:00.000(7 row(s) affected)