求一条sql语句，走过路过不要错过，来者有分

Question

表1（疗程信息）
ID 疗程名      使用仪器  疗程时间（分钟）
1 M6 A  45
2 Maya A  60
3 LPG B  90
4 精油开背                         60表1的含义：M6和Maya使用仪器A，LPG使用仪器B，精油开背不使用仪器表2（直营店仪器）
房间 仪器
1    A
2    A
3    B
4
5表2的含义：店中有2台仪器A，1台仪器B，任何没使用房间可用于手工疗程表3（美容师信息）
美容师ID 美容师名
1 张小姐
2               王小姐
3               李小姐
4               刘小姐表4（美容师排班）
美容师ID  工作开始时间 工作结束时间
1 2006-6-22 10:00 2006-6-22 18:30
1 2006-6-23 00:00 2006-6-23 00:00
1 2006-6-24 14:00 2006-6-24 22:00
1 2006-6-25 10:00 2006-6-25 18:302 2006-6-22 14:00 2006-6-22 22:00
2 2006-6-23 10:00 2006-6-23 18:30
2 2006-6-24 00:00 2006-6-24 00:00
2 2006-6-25 14:00 2006-6-25 22:003 2006-6-22 00:00 2006-6-22 00:00
3 2006-6-23 10:00 2006-6-23 18:30
3 2006-6-24 10:00 2006-6-24 18:30
3 2006-6-25 00:00   2006-6-25 00:004 2006-6-22 00:00 2006-6-22 00:00
4 2006-6-23 14:00 2006-6-23 22:00
4 2006-6-24 10:00 2006-6-24 18:30
4 2006-6-25 14:00 2006-6-25 22:00表4的含义：张小姐 22日上班时间 10:00-18:30 23日休息 
24日 14:00-22:00 25日 10:00-18:30表5（预约时间）  （23日张小姐 休息 24日 王小姐 休息）
预约疗程ID 预约开始时间 预约结束时间 美容师ID
2              2006-6-23 10:00  2006-6-23 11:00    2
3              2006-6-23 10:00  2006-6-23 11:30    3
3              2006-6-23 12:00  2006-6-23 13:30    2
2              2006-6-23 12:00  2006-6-23 13:00    3
3              2006-6-23 14:30  2006-6-23 16:00    2
4              2006-6-23 15:00  2006-6-23 16:00    3
3              2006-6-23 17:00  2006-6-23 18:30    2
1              2006-6-23 17:00  2006-6-23 17:45    3
4              2006-6-23 17:00  2006-6-23 18:00    2
2              2006-6-23 18:30  2006-6-23 19:30    4
2              2006-6-23 19:30  2006-6-23 20:30    4
2              2006-6-23 21:00  2006-6-23 22:00    4
2              2006-6-24 12:00  2006-6-23 13:00    4
2              2006-6-25 10:00  2006-6-23 11:00    4假定现在时间是 22日15：00
现在顾客要预约 23日19：00 疗程 3  美容师 4 
但已不能预约（1美容师休息，2，3美容师下班，4美容师已有预约）
计算其它空闲时段（从当前时间算起 22日 但美容师 4 休息，
还要同一时间考虑设备的使用，A有两台那说明同一时间可以有两个使用A仪器的疗程一起做
B有一台，同一时间只能有一台使用，手工疗程只有有空房间就行）人工计算空闲时段：(列出前3条)
空闲开始时间   空闲结束时间
2006-6-23 16:00         2006-6-23 18:30（2006-6-23 14:30  2006-6-23 16:00 仪器B被别人使用）
2006-6-24 10:00 2006-6-24 12:00
2006-6-25 11:00 2006-6-24 18:00分不够可加，大家多帮忙

Answer 1

to dutguoyi(新鲜鱼排)  
是有点麻烦，请耐心看一下，先谢了

Answer 2

to wwh999(印钞机的卖 V2.0...发梦ing)
它涉及的内容比较多，所以看上去乱

Answer 3

to coolingpipe(冷箫轻笛)  
最后一条数据有问题
2                            2006-6-25  10:00    2006-6-23  11:00        4  
她还没上班

Answer 4

预约疗程ID预约开始时间预约结束时间美容师ID
2 2006-6-24 12:00 2006-6-24 13:00 4
2 2006-6-25 10:00 2006-6-25 11:00 4
最后两条应该是这样的

Answer 5

xiaodong_1567(通吃) ( ) 信誉：100  2006-06-23 15:41:00  得分: 0  
 
 
   to coolingpipe(冷箫轻笛)  
最后一条数据有问题
2                            2006-6-25  10:00    2006-6-23  11:00        4  
她还没上班
  
=================================什么意思？

Answer 6

create table table1
(
id int,
lcm varchar(10),
syyq varchar(2),
lcsj int
)insert into table1 select 1,'M6',     'A', 45
insert into table1 select 2,'Maya',   'A', 60
insert into table1 select 3,'LPG',    'B', 90
insert into table1 select 4,'精油开背','',  60create table table2
(
fj int,
yq varchar(2)
)
insert into table2 select 1, 'A'
insert into table2 select 2, 'A'
insert into table2 select 3, 'B'
insert into table2 select 4, ''
insert into table2 select 5, ''
create table table3
(
mrsid int,
mrsm varchar(20)
)insert into table3 select 1,'张小姐'
insert into table3 select 2,'王小姐'
insert into table3 select 3,'李小姐'
insert into table3 select 4,'刘小姐'
create table table4 
(
mrsid int,
begdate datetime,
enddate datetime
)insert into table4 select 1,'2006-6-22 10:00','2006-6-22 18:30'
insert into table4 select 1,'2006-6-23 00:00','2006-6-23 00:00'
insert into table4 select 1,'2006-6-24 14:00','2006-6-24 22:00'
insert into table4 select 1,'2006-6-25 10:00','2006-6-25 18:30'
insert into table4 select 2,'2006-6-22 14:00','2006-6-22 22:00'
insert into table4 select 2,'2006-6-23 10:00','2006-6-23 18:30'
insert into table4 select 2,'2006-6-24 00:00','2006-6-24 00:00'
insert into table4 select 2,'2006-6-25 14:00','2006-6-25 22:00'
insert into table4 select 3,'2006-6-22 00:00','2006-6-22 00:00'
insert into table4 select 3,'2006-6-23 10:00','2006-6-23 18:30'
insert into table4 select 3,'2006-6-24 10:00','2006-6-24 18:30'
insert into table4 select 3,'2006-6-25 00:00','2006-6-25 00:00'
insert into table4 select 4,'2006-6-22 00:00','2006-6-22 00:00'
insert into table4 select 4,'2006-6-23 14:00','2006-6-23 22:00'
insert into table4 select 4,'2006-6-24 10:00','2006-6-24 18:30'
insert into table4 select 4,'2006-6-25 14:00','2006-6-25 22:00'
create table table5
(
yyid int,
begtime datetime,
endtime datetime,
mrsid int
)insert into table5 select 2,'2006-6-23 10:00', '2006-6-23 11:00', 2
insert into table5 select 3,'2006-6-23 10:00', '2006-6-23 11:30', 3
insert into table5 select 3,'2006-6-23 12:00', '2006-6-23 13:30', 2
insert into table5 select 2,'2006-6-23 12:00', '2006-6-23 13:00', 3
insert into table5 select 3,'2006-6-23 14:30', '2006-6-23 16:00', 2
insert into table5 select 4,'2006-6-23 15:00', '2006-6-23 16:00', 3
insert into table5 select 3,'2006-6-23 17:00', '2006-6-23 18:30', 2
insert into table5 select 1,'2006-6-23 17:00', '2006-6-23 17:45', 3
insert into table5 select 4,'2006-6-23 17:00', '2006-6-23 18:00', 2
insert into table5 select 2,'2006-6-23 18:30', '2006-6-23 19:30', 4
insert into table5 select 2,'2006-6-23 19:30', '2006-6-23 20:30', 4
insert into table5 select 2,'2006-6-23 21:00', '2006-6-23 22:00', 4
insert into table5 select 2,'2006-6-24 12:00', '2006-6-23 13:00', 4
insert into table5 select 2,'2006-6-25 10:00', '2006-6-23 11:00', 4--=============================================================
--=============================================================
--==先来得到美容师没有预约的上班时间==--
--**查询1：得到没有预约的时间列表
select identity(int,1,1) as id,* 
into #tab
from 
(
select convert(varchar(10),begdate,120) as mdate,begdate from table4 where begdate <> enddate and mrsid = 4 union all
select convert(varchar(10),enddate,120) as mdate,enddate from table4 where begdate <> enddate and mrsid = 4 union all
select convert(varchar(10),begtime,120) as mdate,begtime from table5 where mrsid = 4 union all
select convert(varchar(10),endtime,120) as mdate,endtime from table5 where mrsid = 4 --order by mdate,begdate
)t1
group by mdate,begdate 
having count(1) < 2--select * from #tab
--**查询2：美容师没有预约的上班时间列表
select t1.mdate,t1.begdate, t2.begdate as enddate
from #tab t1,#tab t2
where t2.id = t1.id + 1 and t1.id % 2 <> 0--====================================================================
--====================================================================
--====================================================================
--==再来看仪器的使用情况（仪器的使用其实是房间的使用）==--
--**查询3：得到最小时间段划分
--注：如果是想预约疗程4的时候，查询里的where yyid = 3 就应该去掉，而不是改成yyid = 4,
--因为4在所有房间都能做，他可能跟其他的任何疗程冲突
select identity(int,1,1) as id,* 
into #tab2
from 
(
select convert(varchar(10),begtime,120) as ndate,begtime from table5 where yyid = 3 union --注意，这里不是union all
select convert(varchar(10),endtime,120) ,endtime from table5 where yyid = 3 
)t1--查询4：最小时间段划分的房间使用情况（ncount的意义是：这段时间内同时进行的疗程有几个）
select t3.ndate,t3.begtime,t3.endtime,
ncount = (select count(1) from table5 where begtime <= t3.begtime and endtime >= t3.endtime)
from 
(
select t1.id,t1.ndate,t1.begtime,t2.begtime as endtime
from #tab2 t1,#tab2 t2
where t2.id = t1.id + 1
)t3--查询5：从查询4的结果中选择符合条件的纪录
--对于疗程3，同时只能进行 1 个。所以查询条件是ncount = 1 ,意思是当ncount= 1的时候,这段时间是不能够再作疗程3的
--同理对于疗程1,条件应该改成ncount = 2;对于疗程4,应该改成ncount = 5
select * from 
(select t3.ndate,t3.begtime,t3.endtime,
ncount = (select count(1) from table5 where begtime <= t3.begtime and endtime >= t3.endtime)
from 
(
select t1.id,t1.ndate,t1.begtime,t2.begtime as endtime
from #tab2 t1,#tab2 t2
where t2.id = t1.id + 1
)t3
)t4 where ncount = 1--**查询6:综合查询2跟查询5select a.begdate,a.enddate,
isnull('从'+convert(varchar(16),b.begtime,120)+'到'+convert(varchar(16),b.endtime,120)+'仪器使用','') as 仪器使用
from
(
select t1.mdate,t1.begdate, t2.begdate as enddate
from #tab t1,#tab t2
where t2.id = t1.id + 1 and t1.id % 2 <> 0
)a left join 
(
select * from 
(select t3.ndate,t3.begtime,t3.endtime,
ncount = (select count(1) from table5 where begtime <= t3.begtime and endtime >= t3.endtime)
from 
(
select t1.id,t1.ndate,t1.begtime,t2.begtime as endtime
from #tab2 t1,#tab2 t2
where t2.id = t1.id + 1
)t3
)t4 where ncount = 1
)b
on (b.begtime >= a.begdate and b.begtime < a.enddate) or 
   (b.endtime > a.begdate and b.endtime <= a.enddate)--说明:用**标示的查询是必须的查询,没有加标示的只是用来辅助理解--最后的结果不是很满意,楼主可以再用一个临时表,然后用行列转换,把格式再转一下.--不知道有没有高手有更好的思路!--期待ing...

Answer 7

to coolingpipe(冷箫轻笛)
你的sql我在试，有点晕

Answer 8

昨天，把我的头都看晕了，对了对贴，LZ的要求有点BT呢...呵呵..逻辑太复杂.关注一下.

Answer 9

to coolingpipe(冷箫轻笛)
说得差不多