1: 一张主 Product 表 有字段
SELECT p.Id, p.NomarlPrice,p.Imprest FROM Product p WHERE p.Id=1134102:一张子 PriceStrategy 表 有字段
SELECT ps.ProductId ,ps.SpecialPrice,ps.CPSSpeciaImprest,ps.SubType,ps.BeginDate,ps.EndDate,cp.[Level]
FROM PriceStrategy ps WHERE ps.ProductId=113410关系: 第二张表 ProductId 字段 和 第一张表 id 相等的。插入数据: 第一张表不变,id唯一, PriceStrategy 表 根据ProductId, BeginDate, EndDate 每一次操作插入一条数据。
Level 每一次 会增加 1。ps.SpecialPrice的值 是新添加的,,ps.CPSSpeciaImprest 的值也是新添加,但是两个数据插入时不是同一条插入。
也就是说 选择 插入 SpecialPrice:添加一条记录 SpecialPrice 的值,CPSSpeciaImprest 值为0,SubType 为 0
插入 CPSSpeciaImprest :添加一条记录 CPSSpeciaImprest 的值,SpecialPrice 为 0 ,SubType 为 1日期是一个 开始日期 和结束日期,level 每一次添加一条数据,会增加一,前提是 ProductId 等于 上面表中的id,如果换id则新添加从1开始累加。
1:需要 查询结果:根据开始、结束时间 查询 level 值最高的,SubType 等于 0 的 SpecialPrice,如果没有记录SpecialPrice的值为 主表中的 NomarlPrice
2:根据开始、结束时间 查询 level 值最高,SubType 等于 1 的 CPSSpeciaImprest,如果没有记录 CPSSpeciaImprest 值为 主表的 Imprest 表数据情况:
第一张表:
Id NomarlPrice Imprest
113410 120.0000 10.00
第二张表:
ProductId SpecialPrice CPSSpeciaImprest SubType BeginDate EndDate Level
113410 0.0000 180.0000 1 2010-12-24 00:00 2010-12-25 23:59 31
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 30
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 29
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 28
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 27
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 26
113410 0.0000 160.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 25
113410 0.0000 160.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 24
113410 0.0000 0.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 23
113410 0.0000 0.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 22
113410 0.0000 0.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 21
113410 0.0000 0.0000 1 2010-12-25 00:00:00.000 2010-12-26 23:59:59.000 20
113410 190.0000 0.0000 0 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 19
113410 180.0000 0.0000 0 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 18
113410 130.0000 13.0000 0 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 17
113410 156.0000 47.0000 0 2010-12-23 00:00:00.000 2010-12-24 23:59:59.000 16
113410 144.0000 29.0000 0 2010-12-23 00:00:00.000 2010-12-30 23:59:59.000 15
113410 144.0000 29.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 14
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 13
113410 134.0000 16.0000 0 2010-12-23 00:00:00.000 2010-12-24 23:59:59.000 12
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 11
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 10
113410 132.0000 13.0000 0 2010-12-22 00:00:00.000 2010-12-22 23:59:59.000 9
113410 102.0000 15.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 8
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 7
113410 134.0000 16.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 6
113410 133.0000 15.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 5
113410 132.0000 13.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 4
113410 132.0000 13.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 3
113410 132.0000 13.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 2
113410 620.0000 100.0000 0 2010-12-22 00:00:00.000 2010-12-22 23:59:59.000 1从上述数据中 如果根据条件 传 2010-12-24、 2010-12-25 id等于113410 的话
要得到的结果应该是 SpecialPrice 值 190.00 CPSSpeciaImprest 值 180.00
还有一种情况 下面 的表中没有上面id的数据 ,则 要得到 主表中的两个数据。
望高手指教
SELECT p.Id, p.NomarlPrice,p.Imprest FROM Product p WHERE p.Id=1134102:一张子 PriceStrategy 表 有字段
SELECT ps.ProductId ,ps.SpecialPrice,ps.CPSSpeciaImprest,ps.SubType,ps.BeginDate,ps.EndDate,cp.[Level]
FROM PriceStrategy ps WHERE ps.ProductId=113410关系: 第二张表 ProductId 字段 和 第一张表 id 相等的。插入数据: 第一张表不变,id唯一, PriceStrategy 表 根据ProductId, BeginDate, EndDate 每一次操作插入一条数据。
Level 每一次 会增加 1。ps.SpecialPrice的值 是新添加的,,ps.CPSSpeciaImprest 的值也是新添加,但是两个数据插入时不是同一条插入。
也就是说 选择 插入 SpecialPrice:添加一条记录 SpecialPrice 的值,CPSSpeciaImprest 值为0,SubType 为 0
插入 CPSSpeciaImprest :添加一条记录 CPSSpeciaImprest 的值,SpecialPrice 为 0 ,SubType 为 1日期是一个 开始日期 和结束日期,level 每一次添加一条数据,会增加一,前提是 ProductId 等于 上面表中的id,如果换id则新添加从1开始累加。
1:需要 查询结果:根据开始、结束时间 查询 level 值最高的,SubType 等于 0 的 SpecialPrice,如果没有记录SpecialPrice的值为 主表中的 NomarlPrice
2:根据开始、结束时间 查询 level 值最高,SubType 等于 1 的 CPSSpeciaImprest,如果没有记录 CPSSpeciaImprest 值为 主表的 Imprest 表数据情况:
第一张表:
Id NomarlPrice Imprest
113410 120.0000 10.00
第二张表:
ProductId SpecialPrice CPSSpeciaImprest SubType BeginDate EndDate Level
113410 0.0000 180.0000 1 2010-12-24 00:00 2010-12-25 23:59 31
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 30
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 29
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 28
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 27
113410 0.0000 180.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 26
113410 0.0000 160.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 25
113410 0.0000 160.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 24
113410 0.0000 0.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 23
113410 0.0000 0.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 22
113410 0.0000 0.0000 1 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 21
113410 0.0000 0.0000 1 2010-12-25 00:00:00.000 2010-12-26 23:59:59.000 20
113410 190.0000 0.0000 0 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 19
113410 180.0000 0.0000 0 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 18
113410 130.0000 13.0000 0 2010-12-24 00:00:00.000 2010-12-25 23:59:59.000 17
113410 156.0000 47.0000 0 2010-12-23 00:00:00.000 2010-12-24 23:59:59.000 16
113410 144.0000 29.0000 0 2010-12-23 00:00:00.000 2010-12-30 23:59:59.000 15
113410 144.0000 29.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 14
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 13
113410 134.0000 16.0000 0 2010-12-23 00:00:00.000 2010-12-24 23:59:59.000 12
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 11
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 10
113410 132.0000 13.0000 0 2010-12-22 00:00:00.000 2010-12-22 23:59:59.000 9
113410 102.0000 15.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 8
113410 138.0000 21.0000 0 2010-12-21 00:00:00.000 2010-12-22 23:59:59.000 7
113410 134.0000 16.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 6
113410 133.0000 15.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 5
113410 132.0000 13.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 4
113410 132.0000 13.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 3
113410 132.0000 13.0000 0 2010-12-21 00:00:00.000 2010-12-21 23:59:59.000 2
113410 620.0000 100.0000 0 2010-12-22 00:00:00.000 2010-12-22 23:59:59.000 1从上述数据中 如果根据条件 传 2010-12-24、 2010-12-25 id等于113410 的话
要得到的结果应该是 SpecialPrice 值 190.00 CPSSpeciaImprest 值 180.00
还有一种情况 下面 的表中没有上面id的数据 ,则 要得到 主表中的两个数据。
望高手指教
declare @dt1 as datetime
declare @dt2 as datetime
declare @id as varchar(10)
set @dt1 = '2010-12-24'
set @dt2 = '2010-12-25'
set @id = '113410'if exists(select * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id)
select top 1 * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id order by desc
else
select * from tb1 where Id = '113410'
declare @dt2 as datetime
declare @id as varchar(10)
set @dt1 = '2010-12-24'
set @dt2 = '2010-12-25'
set @id = '113410'if exists(select * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id)
select top 1 * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id order by SpecialPrice desc
else
select * from tb1 where Id = '113410'if exists(select * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 1 and ProductId = @id)
select top 1 * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 1 and ProductId = @id order by CPSSpeciaImprest desc
else
select * from tb1 where Id = '113410'
insert into tb1 select 113410,120.0000,10.00
insert into tb1 select 113420,150.0000,23.00
create table tb2(ProductId int,SpecialPrice decimal(18,4),CPSSpeciaImprest decimal(18,4),SubType int,BeginDate datetime,EndDate datetime,Level int)
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00','2010-12-25 23:59',31
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',30
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',29
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',28
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',27
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',26
insert into tb2 select 113410,0.0000,160.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',25
insert into tb2 select 113410,0.0000,160.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',24
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',23
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',22
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',21
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-25 00:00:00.000','2010-12-26 23:59:59.000',20
insert into tb2 select 113410,190.0000,0.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',19
insert into tb2 select 113410,180.0000,0.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',18
insert into tb2 select 113410,130.0000,13.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',17
insert into tb2 select 113410,156.0000,47.0000,0,'2010-12-23 00:00:00.000','2010-12-24 23:59:59.000',16
insert into tb2 select 113410,144.0000,29.0000,0,'2010-12-23 00:00:00.000','2010-12-30 23:59:59.000',15
insert into tb2 select 113410,144.0000,29.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',14
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',13
insert into tb2 select 113410,134.0000,16.0000,0,'2010-12-23 00:00:00.000','2010-12-24 23:59:59.000',12
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',11
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',10
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-22 00:00:00.000','2010-12-22 23:59:59.000',9
insert into tb2 select 113410,102.0000,15.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',8
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',7
insert into tb2 select 113410,134.0000,16.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',6
insert into tb2 select 113410,133.0000,15.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',5
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',4
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',3
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',2
insert into tb2 select 113410,620.0000,100.0000,0,'2010-12-22 00:00:00.000','2010-12-22 23:59:59.000',1
go
declare @id int,@begin datetime,@end datetime
set @id=113420
set @begin='2010-12-24'
set @end='2010-12-25 23:59:59'
select (case when t.CPSSpeciaImprest is not null then t.CPSSpeciaImprest else tb1.Imprest end) from tb1 left join(
select ProductId,CPSSpeciaImprest from tb2 a where ProductId=@id and subtype=1 and begindate=@begin and enddate=@end
and not exists(select 1 from tb2 where ProductId=@id and subtype=1 and begindate=@begin and enddate=@end and level>a.level)
)t on tb1.Id=t.ProductId where tb1.id=@id
/*
---------------------------------------
180.0000(1 行受影响)
*/
/*
如改为
set @id=113420
则
---------------------------------------
23.0000(1 行受影响)
*/
go
drop table tb1,tb2
create table tb1(Id int,NomarlPrice decimal(18,4),Imprest decimal(18,2))
insert into tb1 select 113410,120.0000,10.00
insert into tb1 select 113420,150.0000,23.00
create table tb2(ProductId int,SpecialPrice decimal(18,4),CPSSpeciaImprest decimal(18,4),SubType int,BeginDate datetime,EndDate datetime,Level int)
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00','2010-12-25 23:59',31
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',30
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',29
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',28
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',27
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',26
insert into tb2 select 113410,0.0000,160.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',25
insert into tb2 select 113410,0.0000,160.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',24
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',23
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',22
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',21
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-25 00:00:00.000','2010-12-26 23:59:59.000',20
insert into tb2 select 113410,190.0000,0.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',19
insert into tb2 select 113410,180.0000,0.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',18
insert into tb2 select 113410,130.0000,13.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',17
insert into tb2 select 113410,156.0000,47.0000,0,'2010-12-23 00:00:00.000','2010-12-24 23:59:59.000',16
insert into tb2 select 113410,144.0000,29.0000,0,'2010-12-23 00:00:00.000','2010-12-30 23:59:59.000',15
insert into tb2 select 113410,144.0000,29.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',14
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',13
insert into tb2 select 113410,134.0000,16.0000,0,'2010-12-23 00:00:00.000','2010-12-24 23:59:59.000',12
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',11
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',10
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-22 00:00:00.000','2010-12-22 23:59:59.000',9
insert into tb2 select 113410,102.0000,15.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',8
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',7
insert into tb2 select 113410,134.0000,16.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',6
insert into tb2 select 113410,133.0000,15.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',5
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',4
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',3
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',2
insert into tb2 select 113410,620.0000,100.0000,0,'2010-12-22 00:00:00.000','2010-12-22 23:59:59.000',1
godeclare @dt1 as datetime
declare @dt2 as datetime
declare @id as varchar(10)
set @dt1 = '2010-12-24'
set @dt2 = '2010-12-25'
set @id = '113410'declare @SpecialPrice as decimal(18,4)
declare @CPSSpeciaImprest as decimal(18,4)if exists(select * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id)
set @SpecialPrice = (select top 1 SpecialPrice from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id order by level desc)
else
begin
set @SpecialPrice = (select NomarlPrice from tb1 where Id = '113410')
endif exists(select * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 1 and ProductId = @id)
set @CPSSpeciaImprest = (select top 1 CPSSpeciaImprest from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 1 and ProductId = @id order by level desc)
else
set @CPSSpeciaImprest = (select Imprest from tb1 where Id = '113410')select @SpecialPrice , @CPSSpeciaImprest
/*
-------------------- --------------------
190.0000 180.0000(所影响的行数为 1 行)
*/--换个ID,其他代码和上面一样
set @dt1 = '2010-12-24'
set @dt2 = '2010-12-25'
set @id = '113420'if exists(select * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id)
set @SpecialPrice = (select top 1 SpecialPrice from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 0 and ProductId = @id order by level desc)
else
begin
set @SpecialPrice = (select NomarlPrice from tb1 where Id = '113410')
endif exists(select * from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 1 and ProductId = @id)
set @CPSSpeciaImprest = (select top 1 CPSSpeciaImprest from tb2 where datediff(day,BeginDate,@dt1) = 0 and datediff(day,EndDate,@dt2) = 0 and SubType = 1 and ProductId = @id order by level desc)
else
set @CPSSpeciaImprest = (select Imprest from tb1 where Id = '113410')select @SpecialPrice , @CPSSpeciaImprest
/*
-------------------- --------------------
120.0000 10.0000(所影响的行数为 1 行)
*/drop table tb1 , tb2
create table tb1(Id int,NomarlPrice decimal(18,4),Imprest decimal(18,2))
insert into tb1 select 113410,120.0000,10.00
insert into tb1 select 113420,150.0000,23.00
create table tb2(ProductId int,SpecialPrice decimal(18,4),CPSSpeciaImprest decimal(18,4),SubType int,BeginDate datetime,EndDate datetime,Level int)
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00','2010-12-25 23:59',31
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',30
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',29
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',28
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',27
insert into tb2 select 113410,0.0000,180.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',26
insert into tb2 select 113410,0.0000,160.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',25
insert into tb2 select 113410,0.0000,160.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',24
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',23
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',22
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',21
insert into tb2 select 113410,0.0000,0.0000,1,'2010-12-25 00:00:00.000','2010-12-26 23:59:59.000',20
insert into tb2 select 113410,190.0000,0.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',19
insert into tb2 select 113410,180.0000,0.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',18
insert into tb2 select 113410,130.0000,13.0000,0,'2010-12-24 00:00:00.000','2010-12-25 23:59:59.000',17
insert into tb2 select 113410,156.0000,47.0000,0,'2010-12-23 00:00:00.000','2010-12-24 23:59:59.000',16
insert into tb2 select 113410,144.0000,29.0000,0,'2010-12-23 00:00:00.000','2010-12-30 23:59:59.000',15
insert into tb2 select 113410,144.0000,29.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',14
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',13
insert into tb2 select 113410,134.0000,16.0000,0,'2010-12-23 00:00:00.000','2010-12-24 23:59:59.000',12
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',11
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',10
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-22 00:00:00.000','2010-12-22 23:59:59.000',9
insert into tb2 select 113410,102.0000,15.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',8
insert into tb2 select 113410,138.0000,21.0000,0,'2010-12-21 00:00:00.000','2010-12-22 23:59:59.000',7
insert into tb2 select 113410,134.0000,16.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',6
insert into tb2 select 113410,133.0000,15.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',5
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',4
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',3
insert into tb2 select 113410,132.0000,13.0000,0,'2010-12-21 00:00:00.000','2010-12-21 23:59:59.000',2
insert into tb2 select 113410,620.0000,100.0000,0,'2010-12-22 00:00:00.000','2010-12-22 23:59:59.000',1
go
declare @id int,@begin datetime,@end datetime
set @id=113410
set @begin='2010-12-24'
set @end='2010-12-25 23:59:59'
select (case when t1.SpecialPrice is not null then t1.SpecialPrice else tb1.NomarlPrice end),
(case when t2.CPSSpeciaImprest is not null then t2.CPSSpeciaImprest else tb1.Imprest end)
from tb1 left join(
select ProductId,SpecialPrice from tb2 a where ProductId=@id and subtype=0 and begindate=@begin and enddate=@end
and not exists(select 1 from tb2 where ProductId=@id and subtype=0 and begindate=@begin and enddate=@end and level>a.level)
)t1 on tb1.Id=t1.ProductId left join (
select ProductId,CPSSpeciaImprest from tb2 a where ProductId=@id and subtype=1 and begindate=@begin and enddate=@end
and not exists(select 1 from tb2 where ProductId=@id and subtype=1 and begindate=@begin and enddate=@end and level>a.level)
)t2 on tb1.Id=t2.ProductId where tb1.id=@id
/*
--------------------------------------- ---------------------------------------
190.0000 180.0000(1 行受影响)(1 行受影响)
*/
/*
如改为
set @id=113420
则
--------------------------------------- ---------------------------------------
150.0000 23.0000(1 行受影响)
*/
go
drop table tb1,tb2一样处理.