table a (时间在 08:30~18:00之间)
2009-08-27 08:30:00~2009-08-27 09:00:00
2009-08-27 09:00:00~2009-08-27 09:30:00
2009-08-27 13:00:00~2009-08-27 13:30:00
2009-08-27 15:30:00~2009-08-27 16:00:00
2009-08-27 16:00:00~2009-08-27 16:30:00时间连续作为一条记录2009-08-27 08:30:00~2009- 08-27 09:30:002009-08-27 13:00:00~2009-08-27 13:30:002009-08-27 15:30:00~2009-08-27 16:30:00
2009-08-27 08:30:00~2009-08-27 09:00:00
2009-08-27 09:00:00~2009-08-27 09:30:00
2009-08-27 13:00:00~2009-08-27 13:30:00
2009-08-27 15:30:00~2009-08-27 16:00:00
2009-08-27 16:00:00~2009-08-27 16:30:00时间连续作为一条记录2009-08-27 08:30:00~2009- 08-27 09:30:002009-08-27 13:00:00~2009-08-27 13:30:002009-08-27 15:30:00~2009-08-27 16:30:00
解决方案 »
- 新增一个字段如何立即提交后,再把这个表里的所有的记录的这个新增字段的值设值?
- 怎么写一个相关子查询语句?
- 一个比较复杂的查询....!
- sql语句怎么读取excel中的数据
- 如何尽量避免死锁!请高手指点!!!本人感觉不尽!!!!!!
- 英文sql2000,求sqlserver2000中文帮助文档?
- 我現在用查询分析器可以连接局域网内的数据库服务器,可是用企业管理器来连接提示错误.....
- access98下的数据库到access2000下怎么打开?
- 一个简单的vf问题,请大家帮?
- 有一个表名是“日期”,字段是日期型的,我想用一个sql语句查询某一天的记录,如何写?( 输入的内容只能在text1里)
- 数据问题 谁能帮我!马上送上100分
- ****跨库能否建立表关系*****
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( a varchar (40))
go
insert tb SELECT
'2009-08-27 08:30:00~2009-08-27 09:00:00' UNION ALL SELECT
'2009-08-27 09:00:00~2009-08-27 09:30:00' UNION ALL SELECT
'2009-08-27 13:00:00~2009-08-27 13:30:00' UNION ALL SELECT
'2009-08-27 15:30:00~2009-08-27 16:00:00' UNION ALL SELECT
'2009-08-27 16:00:00~2009-08-27 16:30:00'
go
select identity(int,1,1) as id,
LEFT(a,CHARINDEX('~',a)-1) as beg,
RIGHT(a,LEN(a)-CHARINDEX('~',a)) as ed
into #
from tb
select beg+'~'+ed from(
select b.beg,a.ed
from # a join # b on a.beg=b.ed
union all
select top 100 percent beg,ed from # k
where not exists(select * from # where beg=k.ed or ed=k.beg)
order by beg
) kgo
---------------------------------------------------------------------------------
2009-08-27 08:30:00~2009-08-27 09:30:00
2009-08-27 15:30:00~2009-08-27 16:30:00
2009-08-27 13:00:00~2009-08-27 13:30:00
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( a varchar (40))
go
insert tb SELECT
'2009-08-27 08:30:00~2009-08-27 09:00:00' UNION ALL SELECT
'2009-08-27 09:00:00~2009-08-27 09:30:00' UNION ALL SELECT
'2009-08-27 13:00:00~2009-08-27 13:30:00' UNION ALL SELECT
'2009-08-27 15:30:00~2009-08-27 16:00:00' UNION ALL SELECT
'2009-08-27 16:00:00~2009-08-27 16:30:00'
go
select identity(int,1,1) as id,
LEFT(a,CHARINDEX('~',a)-1) as beg,
RIGHT(a,LEN(a)-CHARINDEX('~',a)) as ed
into #
from tb
select beg+'~'+ed as V
from(
select b.beg,a.ed
from # a join # b on a.beg=b.ed
union all
select beg,ed from # k
where not exists(select * from # where beg=k.ed or ed=k.beg)
) k
order by left(beg+'~'+ed ,CHARINDEX('~',beg+'~'+ed )-1 )
/*
V
---------------------------------------------------------------------------------
2009-08-27 08:30:00~2009-08-27 09:30:00
2009-08-27 13:00:00~2009-08-27 13:30:00
2009-08-27 15:30:00~2009-08-27 16:30:00*/
排序下
if object_id('tb') is not null
drop table tb
go create table tb (startDate datetime, endDate datetime)
go insert into tb(startDate,endDate)
select '2009-08-27 08:30:00', '2009-08-27 09:00:00'
union all
select '2009-08-27 09:00:00', '2009-08-27 09:30:00'
union all
select '2009-08-27 13:00:00', '2009-08-27 13:30:00'
union all
select '2009-08-27 15:30:00', '2009-08-27 16:00:00'
union all
select '2009-08-27 16:00:00', '2009-08-27 16:30:00'
go with LI as
(
Select t1.startdate,t2.enddate from tb t1, tb t2
Where t1.enddate = t2.startdate)
Select *
from
(
Select startdate,enddate
From LI
union all
Select startdate,enddate
From tb
Where startdate not in (select startdate from LI)
and enddate not in (select enddate from LI)
) LO order by startdate
into #
from MMS_MEETING_APPO
select beg+'~'+ed as GET_DATE ,ROOM_CODE,DEPART_ID,USE_ID,REMARK
from(
select b.beg,a.ed,a.ROOM_CODE,a.DEPART_ID,a.USE_ID,a.REMARK
from # a join # b on a.beg=b.ed
union all
select beg,ed,ROOM_CODE,DEPART_ID,USE_ID,REMARK from # k
where not exists(select * from # where beg=k.ed or ed=k.beg)
) k
where DEPART_ID='2' and ROOM_CODE='DRM00003' and USE_ID='2' and (left(beg,10) BETWEEN '2009-01-01' AND '2009-08-31' )
order by left(beg+'~'+ed ,CHARINDEX('~',beg+'~'+ed )-1 )
if object_id('tempdb..#') is not null
Begin
drop table #
End
结果是这样的
2009-08-27 11:30:00~2009-08-27 12:00:00
2009-08-27 12:30:00~2009-08-27 13:30:00
2009-08-27 13:00:00~2009-08-27 14:00:00
2009-08-27 13:30:00~2009-08-27 14:30:00
2009-08-28 09:00:00~2009-08-28 10:00:00
2009-08-28 09:30:00~2009-08-28 10:30:00
2009-08-28 10:00:00~2009-08-28 11:00:00
2009-08-28 10:00:00~2009-08-28 11:00:00
2009-08-29 09:00:00~2009-08-29 10:00:00
2009-08-29 09:30:00~2009-08-29 10:30:00
2009-08-29 10:00:00~2009-08-29 11:00:00
2009-08-30 09:00:00~2009-08-30 10:00:00
2009-08-30 09:30:00~2009-08-30 10:30:00
2009-08-30 10:00:00~2009-08-30 11:00:00
with cte as
(
select starttime,endtime,0 as layer from tb a where not exists(select endtime from tb where endtime=a.starttime)
union all
select starttime,endtime,1 from tb where endtime in(select starttime from cte))
select starttime,max(endtime) as endtime from cte
where starttime in(select starttime from cte where layer=0)
group by starttime
order by starttime未经过测试,
if object_id('tb') is not null
drop table tb
go create table tb (starttime datetime, endtime datetime)
go insert into tb(starttime,endtime)
select '2009-08-27 08:30:00', '2009-08-27 09:00:00'
union all
select '2009-08-27 09:00:00', '2009-08-27 09:30:00'
union all
select '2009-08-27 09:30:00', '2009-08-27 19:30:00'
union all
select '2009-08-27 13:00:00', '2009-08-27 13:30:00'
union all
select '2009-08-27 15:30:00', '2009-08-27 16:00:00'
union all
select '2009-08-27 16:00:00', '2009-08-27 16:30:00'
go with cte as
(
select starttime,endtime from tb a
where not exists(select endtime from tb where endtime=a.starttime)
union all
select b.starttime,a.endtime from tb a join cte b
on b.endtime =a.starttime
)
--select * from cte
select starttime,max(endtime) as endtime from cte
group by starttime
order by starttimestarttime
2009-08-27 08:30:00.000