为了方便我把建表的SQL贴出来了
这是一张客户预约员工表
CREATE TABLE [dbo].[Top_Member_Reservation](
[F_Guid] [numeric](18, 0) IDENTITY(1,1) NOT NULL,--主键
[F_Member_Id] [numeric](18, 0) NOT NULL,--会员id
[F_Reservation_StartTime] [datetime] NOT NULL,--预约开始时间
[F_Reservation_EndTime] [datetime] NOT NULL,--预约结束时间
[F_Employee_Id] [numeric](18, 0) NOT NULL,--被预约员工id
[F_Re] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NULL,--备注
)里面的数据为
1,22,2008-03-17 10:56:00,2008-03-17 15:56:00,27,null
1,22,2008-03-17 11:56:00,2008-03-17 17:56:00,27,null
1,37,2008-03-17 17:57:00,2008-03-17 19:57:00,27,null
1,22,2008-03-17 13:52:00,2008-03-17 14:52:00,28,null
1,22,2008-03-17 15:49:00,2008-03-17 16:49:00,27,null下面是我写的:验证会员预约员工在预约时间段内不能再次被预约
declare @employeeId decimal
set @employeeId=28
select 1
from (
select * from Top_Member_Reservation where F_Employee_Id=@employeeId
)a
inner join
(
select * from Top_Member_Reservation where F_Employee_Id=@employeeId
)b
on
a.F_Reservation_StartTime>b.F_Reservation_StartTime
and a.F_Reservation_StartTime<b.F_Reservation_EndTime
group by a.F_Guid,a.F_Member_Id,
a.F_Reservation_StartTime,a.F_Reservation_EndTime,
a.F_Employee_Id,a.F_Re
查询结果:
为空(这是我想要的)我以前写的是这样的
declare @employeeId decimal
set @employeeId=28select 1
from Top_Member_Reservation a
inner join
Top_Member_Reservation b
on
a.F_Reservation_StartTime>b.F_Reservation_StartTime
and a.F_Reservation_StartTime<b.F_Reservation_EndTime
and a.F_Employee_Id=@employeeId
group by a.F_Guid,a.F_Member_Id,
a.F_Reservation_StartTime,a.F_Reservation_EndTime,
a.F_Employee_Id,a.F_Re
查询结果:
1(说明有值..我一直不明白,为什么这还能查询得出来?)
请教.........
这是一张客户预约员工表
CREATE TABLE [dbo].[Top_Member_Reservation](
[F_Guid] [numeric](18, 0) IDENTITY(1,1) NOT NULL,--主键
[F_Member_Id] [numeric](18, 0) NOT NULL,--会员id
[F_Reservation_StartTime] [datetime] NOT NULL,--预约开始时间
[F_Reservation_EndTime] [datetime] NOT NULL,--预约结束时间
[F_Employee_Id] [numeric](18, 0) NOT NULL,--被预约员工id
[F_Re] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NULL,--备注
)里面的数据为
1,22,2008-03-17 10:56:00,2008-03-17 15:56:00,27,null
1,22,2008-03-17 11:56:00,2008-03-17 17:56:00,27,null
1,37,2008-03-17 17:57:00,2008-03-17 19:57:00,27,null
1,22,2008-03-17 13:52:00,2008-03-17 14:52:00,28,null
1,22,2008-03-17 15:49:00,2008-03-17 16:49:00,27,null下面是我写的:验证会员预约员工在预约时间段内不能再次被预约
declare @employeeId decimal
set @employeeId=28
select 1
from (
select * from Top_Member_Reservation where F_Employee_Id=@employeeId
)a
inner join
(
select * from Top_Member_Reservation where F_Employee_Id=@employeeId
)b
on
a.F_Reservation_StartTime>b.F_Reservation_StartTime
and a.F_Reservation_StartTime<b.F_Reservation_EndTime
group by a.F_Guid,a.F_Member_Id,
a.F_Reservation_StartTime,a.F_Reservation_EndTime,
a.F_Employee_Id,a.F_Re
查询结果:
为空(这是我想要的)我以前写的是这样的
declare @employeeId decimal
set @employeeId=28select 1
from Top_Member_Reservation a
inner join
Top_Member_Reservation b
on
a.F_Reservation_StartTime>b.F_Reservation_StartTime
and a.F_Reservation_StartTime<b.F_Reservation_EndTime
and a.F_Employee_Id=@employeeId
group by a.F_Guid,a.F_Member_Id,
a.F_Reservation_StartTime,a.F_Reservation_EndTime,
a.F_Employee_Id,a.F_Re
查询结果:
1(说明有值..我一直不明白,为什么这还能查询得出来?)
请教.........
我先回去吃晚饭...晚饭后再来look look
谁帮我解决后有大奖....棒棒糖一颗
from Top_Member_Reservation a
inner join
Top_Member_Reservation b
on a.F_Employee_Id=b.F_Employee_Id
where a.F_Reservation_StartTime>b.F_Reservation_StartTime
and a.F_Reservation_StartTime<b.F_Reservation_EndTime
and a.F_Employee_Id=@employeeId
group by a.F_Guid,a.F_Member_Id,
a.F_Reservation_StartTime,a.F_Reservation_EndTime,
a.F_Employee_Id,a.F_Re
declare @employeeId decimal
set @employeeId=28select 1
from Top_Member_Reservation a
inner join
Top_Member_Reservation b
on
a.F_Reservation_StartTime>b.F_Reservation_StartTime
and a.F_Reservation_StartTime<b.F_Reservation_EndTime
and a.F_Employee_Id=@employeeId就这:
and a.f_employee_id = b.f_employee_id
或
and b.f_employee_id = @employeeid
group by a.F_Guid,a.F_Member_Id,
a.F_Reservation_StartTime,a.F_Reservation_EndTime,
a.F_Employee_Id,a.F_Re