表结构如下:
Id PriceType Areas ListPriceTemp BeginDate EndDate
1 2 5,6,7 1 2010-01-03 2010-03-01
要求如下:
新增记录时判断,同一个Areas,同一个priceType,同一个日期只能有一套价格(日期不能交叉)。
注:Areas是由多个外键值由,拼接而成,即新增时,若Areas为5则与已有ID为1的记录Areas重复实现要求:
根据新增时传入的PriceType,Areas,BeginDate,EndDate构造判断条件,用一条SQL语句实现判断
请各位高手帮忙!
if exists
(
select * from tb
where
PriceType = @PriceType and
charindex(@Areas,Areas)>0 and
(
@BeginDate between BeginDate and EndDate or
@EndDate between BeginDate and EndDate
)
即 @BeginDate <BeginDate && @EndDate>EndDate 请解
Id PriceType Areas ListPriceTemp BeginDate EndDate
1 2 5,6,7 1 2010-01-03 2010-03-01
--1 导入测试数据#a
select *
into #a
from (
select 1 Id
, 2 PriceType
, '5' Areas
, 1 ListPriceTemp
, '2010-01-03' BeginDate
, '2010-03-01' EndDate ) a
--(1 行受影响)
--2 检查PriceType,Areas,BeginDate,EndDate 不导入--insert into 表
select *
from #a
where not exists (
select *
from (
select 1 Id
, 2 PriceType
, '5,6,7' Areas
, 1 ListPriceTemp
, '2010-01-03' BeginDate
, '2010-03-01' EndDate ) a
where CHARINDEX(#a.Areas,a.Areas) > 0 and
a.PriceType=#a.PriceType and
a.BeginDate=#a.BeginDate and
a.EndDate=#a.EndDate)
--(0 行受影响)--3 导入测试数据#b
select *
into #b
from (
select 1 Id
, 2 PriceType
, '8' Areas
, 1 ListPriceTemp
, '2010-01-03' BeginDate
, '2010-03-01' EndDate ) a
--(1 行受影响)--4 检查PriceType,Areas,BeginDate,EndDate 可以导入
--insert into 表
select *
from #b
where not exists (
select *
from (
select 1 Id
, 2 PriceType
, '5,6,7' Areas
, 1 ListPriceTemp
, '2010-01-03' BeginDate
, '2010-03-01' EndDate ) a
where CHARINDEX(#b.Areas,a.Areas) > 0 and
a.PriceType=#b.PriceType and
a.BeginDate=#b.BeginDate and
a.EndDate=#b.EndDate)--result:
Id PriceType Areas ListPriceTemp BeginDate EndDate
----------- ----------- ----- ------------- ---------- ----------
1 2 8 1 2010-01-03 2010-03-01(1 行受影响)