select distinct a.* from t_table a,t_table b
where a.姓名=b.姓名
and (a.开始日期>=b.开始日期
and a.结束日期<=b.开始日期
or a.开始日期>=b.结束日期
and a.结束日期<=b.结束日期
)
where a.姓名=b.姓名
and (a.开始日期>=b.开始日期
and a.结束日期<=b.开始日期
or a.开始日期>=b.结束日期
and a.结束日期<=b.结束日期
)
declare @ table (id int,姓名 varchar(30),金额 int,开始日期 datetime, 结束日期 datetime)
insert @
select
1, '张三', 20, '2004-02-03', '2004-02-07'
union all select
2, '张三', 30, '2004-02-05', '2004-02-08'
union all select
3, '张三', 21, '2004-03-11', '2004-03-22'
union all select
4, '张三', 77, '2004-05-22', '2004-05-27'
union all select
5, '李四', 33, '2004-02-01', '2004-02-07'
union all select
6, '李四', 37, '2004-02-01', '2004-02-01'
union all select
7, '李四', 52, '2004-03-01', '2004-05-02'
union all select
8, '李四', 99, '2004-02-06', '2004-02-07'select distinct b.* from @ a,@ b
where a.姓名=b.姓名
and a.id<>b.id
and (a.开始日期<=b.开始日期
and a.结束日期>=b.开始日期
or a.开始日期<=b.结束日期
and a.结束日期>=b.结束日期
)--结果:
id 姓名 金额 开始日期 结束日期
----------- ------------------------------ ----------- ------------------------------------------------------ ------------------------------------------------------
1 张三 20 2004-02-03 00:00:00.000 2004-02-07 00:00:00.000
2 张三 30 2004-02-05 00:00:00.000 2004-02-08 00:00:00.000
5 李四 33 2004-02-01 00:00:00.000 2004-02-07 00:00:00.000
6 李四 37 2004-02-01 00:00:00.000 2004-02-01 00:00:00.000
8 李四 99 2004-02-06 00:00:00.000 2004-02-07 00:00:00.000(所影响的行数为 5 行)
insert @
select
1, '张三', 20, '2004-02-03', '2004-02-07'
union all select
2, '张三', 30, '2004-02-05', '2004-02-08'
union all select
3, '张三', 21, '2004-03-11', '2004-03-22'
union all select
4, '张三', 77, '2004-05-22', '2004-05-27'
union all select
5, '李四', 33, '2004-02-01', '2004-02-07'
union all select
6, '李四', 37, '2004-02-01', '2004-02-01'
union all select
7, '李四', 52, '2004-03-01', '2004-05-02'
union all select
8, '李四', 99, '2004-02-06', '2004-02-07'select distinct a.* from @ a,@ b
where a.姓名=b.姓名
and a.id<>b.id
and (b.开始日期 between a.开始日期 and a.结束日期
or b.结束日期 between a.开始日期 and a.结束日期
)