目前数据库中有如下数据,反应了每个部门在一个月内的归属人变化情况。
DeptName StartTime EndTime Manager
部门A 2009-10-1 2009-10-8 经理A
部门A 2009-10-9 2009-10-31 经理B
部门B 2009-10-1 2009-10-15 经理A
部门B 2009-10-17 2009-10-31 经理C
部门C 2009-10-1 2009-10-7 经理A
部门C 2009-10-9 2009-10-18 经理B
部门C 2009-10-20 2009-10-31 经理C
部门D 2009-10-1 2009-10-8 经理C
部门D 2009-10-9 2009-10-21 经理D
部门D 2009-10-22 2009-10-31 经理A
部门E 2009-10-1 2009-10-31 经理D
我现在想挑出部门发生归属人变化时,开始时间与上次结束时间不连续的所有条目。
例如部门B,第二条开始时间为2009-10-17,而第一条的结束时间是2009-10-15。则2009-10-17的这条记录就需要被挑出。请问,这个应该如何实现?
DeptName StartTime EndTime Manager
部门A 2009-10-1 2009-10-8 经理A
部门A 2009-10-9 2009-10-31 经理B
部门B 2009-10-1 2009-10-15 经理A
部门B 2009-10-17 2009-10-31 经理C
部门C 2009-10-1 2009-10-7 经理A
部门C 2009-10-9 2009-10-18 经理B
部门C 2009-10-20 2009-10-31 经理C
部门D 2009-10-1 2009-10-8 经理C
部门D 2009-10-9 2009-10-21 经理D
部门D 2009-10-22 2009-10-31 经理A
部门E 2009-10-1 2009-10-31 经理D
我现在想挑出部门发生归属人变化时,开始时间与上次结束时间不连续的所有条目。
例如部门B,第二条开始时间为2009-10-17,而第一条的结束时间是2009-10-15。则2009-10-17的这条记录就需要被挑出。请问,这个应该如何实现?
from ta a
where not exists(select 1 from ta where DeptName = a.DeptName and dateadd(d,-1,StartTime) between a.StartTime and a.endtime)
from tb t
where
not exists(select 1 from tb where DeptName=t.DeptName and EndTime=dateadd(dd,-1,t.StartTime))
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-04 14:05:04
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([DeptName] varchar(5),[StartTime] datetime,[EndTime] datetime,[Manager] varchar(5))
insert [tb]
select '部门A','2009-10-1','2009-10-8','经理A' union all
select '部门A','2009-10-9','2009-10-31','经理B' union all
select '部门B','2009-10-1','2009-10-15','经理A' union all
select '部门B','2009-10-17','2009-10-31','经理C' union all
select '部门C','2009-10-1','2009-10-7','经理A' union all
select '部门C','2009-10-9','2009-10-18','经理B' union all
select '部门C','2009-10-20','2009-10-31','经理C' union all
select '部门D','2009-10-1','2009-10-8','经理C' union all
select '部门D','2009-10-9','2009-10-21','经理D' union all
select '部门D','2009-10-22','2009-10-31','经理A' union all
select '部门E','2009-10-1','2009-10-31','经理D'
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where DeptName = t.DeptName and dateadd(dd,-1,StartTime) between t.StartTime and t.endtime)
----------------结果----------------------------
/* DeptName StartTime EndTime Manager
-------- ----------------------- ----------------------- -------
部门A 2009-10-09 00:00:00.000 2009-10-31 00:00:00.000 经理B
部门B 2009-10-01 00:00:00.000 2009-10-15 00:00:00.000 经理A
部门B 2009-10-17 00:00:00.000 2009-10-31 00:00:00.000 经理C
部门C 2009-10-01 00:00:00.000 2009-10-07 00:00:00.000 经理A
部门C 2009-10-09 00:00:00.000 2009-10-18 00:00:00.000 经理B
部门C 2009-10-20 00:00:00.000 2009-10-31 00:00:00.000 经理C
部门D 2009-10-22 00:00:00.000 2009-10-31 00:00:00.000 经理A
部门E 2009-10-01 00:00:00.000 2009-10-31 00:00:00.000 经理D(8 行受影响)
*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([DeptName] varchar(5),[StartTime] datetime,[EndTime] datetime,[Manager] varchar(5))
insert [TB]
select '部门A','2009-10-1','2009-10-8','经理A' union all
select '部门A','2009-10-9','2009-10-31','经理B' union all
select '部门B','2009-10-1','2009-10-15','经理A' union all
select '部门B','2009-10-17','2009-10-31','经理C' union all
select '部门C','2009-10-1','2009-10-7','经理A' union all
select '部门C','2009-10-9','2009-10-18','经理B' union all
select '部门C','2009-10-20','2009-10-31','经理C' union all
select '部门D','2009-10-1','2009-10-8','经理C' union all
select '部门D','2009-10-9','2009-10-21','经理D' union all
select '部门D','2009-10-22','2009-10-31','经理A' union all
select '部门E','2009-10-1','2009-10-31','经理D'select * from [TB] t
where not exists(select 1 from TB where t.DeptName=DeptName and datediff(dd,EndTime,t.StartTime)=1)/*
DeptName StartTime EndTime Manager
-------- ------------------------------------------------------ ------------------------------------------------------ -------
部门A 2009-10-01 00:00:00.000 2009-10-08 00:00:00.000 经理A
部门B 2009-10-01 00:00:00.000 2009-10-15 00:00:00.000 经理A
部门B 2009-10-17 00:00:00.000 2009-10-31 00:00:00.000 经理C
部门C 2009-10-01 00:00:00.000 2009-10-07 00:00:00.000 经理A
部门C 2009-10-09 00:00:00.000 2009-10-18 00:00:00.000 经理B
部门C 2009-10-20 00:00:00.000 2009-10-31 00:00:00.000 经理C
部门D 2009-10-01 00:00:00.000 2009-10-08 00:00:00.000 经理C
部门E 2009-10-01 00:00:00.000 2009-10-31 00:00:00.000 经理D(所影响的行数为 8 行)*/drop table TB
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([DeptName] varchar(5),[StartTime] datetime,[EndTime] datetime,[Manager] varchar(5))
insert [tb]
select '部门A','2009-10-1','2009-10-8','经理A' union all
select '部门A','2009-10-9','2009-10-31','经理B' union all
select '部门B','2009-10-1','2009-10-15','经理A' union all
select '部门B','2009-10-17','2009-10-31','经理C' union all
select '部门C','2009-10-1','2009-10-7','经理A' union all
select '部门C','2009-10-9','2009-10-18','经理B' union all
select '部门C','2009-10-20','2009-10-31','经理C' union all
select '部门D','2009-10-1','2009-10-8','经理C' union all
select '部门D','2009-10-9','2009-10-21','经理D' union all
select '部门D','2009-10-22','2009-10-31','经理A' union all
select '部门E','2009-10-1','2009-10-31','经理D'
---查询---
select *
from tb t
where
not exists(select 1 from tb where DeptName=t.DeptName and EndTime=dateadd(dd,-1,t.StartTime))
and
exists(select 1 from tb where DeptName=t.DeptName and StartTime<t.StartTime)---结果---
DeptName StartTime EndTime Manager
-------- ------------------------------------------------------ ------------------------------------------------------ -------
部门B 2009-10-17 00:00:00.000 2009-10-31 00:00:00.000 经理C
部门C 2009-10-09 00:00:00.000 2009-10-18 00:00:00.000 经理B
部门C 2009-10-20 00:00:00.000 2009-10-31 00:00:00.000 经理C(所影响的行数为 3 行)