一张表中包含N个字段,其中有三个字段名为:【职员名称】、【起始日期】、【截止日期】,
我希望给一个限定,条件是:【职员名称】+两个日期之间包含的日期不能有重复,举例来说吧:
老大 6.1 6.5--以下记录跟这条来对比
老大 5.30 5.31--没重复
老大 6.5 6.10--没重复
老大 6.1 6.5--重复了
老大 5.10 6.1--重复了
老大 6.1 6.2--重复了
老大 6.4 6.6--重复了
大家应该看明白了吧。谢谢帮我解答一下哦~
我希望给一个限定,条件是:【职员名称】+两个日期之间包含的日期不能有重复,举例来说吧:
老大 6.1 6.5--以下记录跟这条来对比
老大 5.30 5.31--没重复
老大 6.5 6.10--没重复
老大 6.1 6.5--重复了
老大 5.10 6.1--重复了
老大 6.1 6.2--重复了
老大 6.4 6.6--重复了
大家应该看明白了吧。谢谢帮我解答一下哦~
drop table tb
Go
Create table tb([zy] nvarchar(2),[ksrq] decimal(18,2),[jsrq] decimal(18,2))
Insert tb
select N'老大',5.30,5.31 union all
select N'老大',6.5,6.10 union all
select N'老大',6.1,6.5 union all
select N'老大',5.10,6.1 union all
select N'老大',6.1,6.2 union all
select N'老大',6.4,6.6
Go
declare @s dec(18,2),@e dec(18,2)
select @s=6.1,@e=6.5
select *
from tb
where not(([ksrq] between 6.1 and 6.5 or
[jsrq] between 6.1 and 6.5) or
(@s between [ksrq] and [jsrq] or
@e between [ksrq] and [jsrq]
))
/*
zy ksrq jsrq
---- --------------------------------------- ---------------------------------------
老大 5.30 5.31(1 個資料列受到影響)*/
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([职员名称] [nvarchar](10),[起始日期] [nvarchar](10),[截止日期] [nvarchar](10))
INSERT INTO [tb]
SELECT '老大','6.1','6.5' UNION ALL
SELECT '老大','5.30','5.31' UNION ALL
SELECT '老大','6.5','6.10' UNION ALL
SELECT '老大','6.1','6.5' UNION ALL
SELECT '老大','5.10','6.1' UNION ALL
SELECT '老大','6.1','6.2' UNION ALL
SELECT '老大','6.4','6.6'
-->SQL查询如下:SELECT DISTINCT a.*
FROM [tb] a
JOIN (
SELECT TOP 1*
FROM tb
) b
ON a.职员名称 = b.职员名称
AND NOT (a.起始日期>b.起始日期 AND a.起始日期<b.截止日期)
AND NOT (a.截止日期>b.起始日期 AND a.截止日期<b.截止日期)
/*
职员名称 起始日期 截止日期
---------- ---------- ----------
老大 5.10 6.1
老大 5.30 5.31
老大 6.1 6.5(3 行受影响)
*/
drop table tb
Go
Create table tb([zy] nvarchar(2),[ksrq] decimal(18,2),[jsrq] decimal(18,2))
Insert tb
select N'老大',5.30,5.31 union all
select N'老大',6.5,6.10 union all
select N'老大',6.1,6.5 union all
select N'老大',5.10,6.1 union all
select N'老大',6.1,6.2 union all
select N'老大',6.4,6.6
Go
declare @s dec(18,2),@e dec(18,2)
select @s=6.1,@e=6.5
select *
from tb
where not(([ksrq]>= 6.1 and [ksrq]<6.5 or
[jsrq] >6.1 and [ksrq]<=6.5) or
(@s>=[ksrq] and @s<[jsrq] or
@s>[ksrq] and @s<=[jsrq] or
@e>=[ksrq] and @e<[jsrq] or
@e>[ksrq] and @e<=[jsrq]
))
/*
zy ksrq jsrq
---- --------------------------------------- ---------------------------------------
老大 5.30 5.31
老大 6.50 6.10(2 個資料列受到影響)*/
drop table tb
Go
Create table tb([zy] nvarchar(2),[ksrq] varchar(5),[jsrq] varchar(5))
Insert tb
select N'老大','6.1','6.5' union all
select N'老大','5.30','5.31' union all
select N'老大','6.5','6.10' union all
select N'老大','6.1','6.5' union all
select N'老大','5.10','6.1' union all
select N'老大','6.1','6.2' union all
select N'老大','6.4','6.6'
Go--1
select top 1 * from tb
union all
select * from tb
where ksrq not between (select top 1 ksrq from tb) and (select top 1 jsrq from tb)
and jsrq not between (select top 1 ksrq from tb) and (select top 1 jsrq from tb)/*
zy ksrq jsrq
---- ----- -----
老大 6.1 6.5
老大 5.30 5.31(所影响的行数为 2 行)
*/
--2
select top 1 * from tb
union all
select * from tb
where ksrq >= (select top 1 jsrq from tb) or jsrq < (select top 1 ksrq from tb)/*
zy ksrq jsrq
---- ----- -----
老大 6.1 6.5
老大 5.30 5.31
老大 6.5 6.10(所影响的行数为 3 行)*/
正确
select top 1 * from tb
union all
select * from tb
where ksrq > (select top 1 jsrq from tb) or jsrq < (select top 1 ksrq from tb)/*
zy ksrq jsrq
---- ----- -----
老大 6.1 6.5
老大 5.30 5.31(所影响的行数为 2 行)
*/between的写法有错误
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([职员名称] [nvarchar](10),[起始日期] [nvarchar](10),[截止日期] [nvarchar](10))
INSERT INTO [tb]
SELECT '老大','6.1','6.5' UNION ALL
SELECT '老大','5.30','5.31' UNION ALL
SELECT '老大','6.5','6.10' UNION ALL
SELECT '老大','6.1','6.5' UNION ALL
SELECT '老大','5.10','6.1' UNION ALL
SELECT '老大','6.1','6.2' UNION ALL
SELECT '老大','6.4','6.6'
-->SQL查询如下:SELECT DISTINCT a.*
FROM [tb] a
JOIN (
SELECT TOP 1*
FROM tb
) b
ON a.职员名称 = b.职员名称
AND NOT (a.起始日期 BETWEEN b.起始日期 AND b.截止日期)
AND NOT (a.截止日期 BETWEEN b.起始日期 AND b.截止日期)
/*
职员名称 起始日期 截止日期
---------- ---------- ----------
老大 5.30 5.31(1 行受影响)
*/
你要的最终结果是什么