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'
SELECT CASE WHEN (cps.Id is null or ('2010-12-24' < cps.BeginDate or '2010-12-24'>cps.EndDate)) THEN cp.NomarlPrice ELSE cps.SpecialPrice END AS Price
FROM PriceStrategy cps
LEFT JOIN Product cp ON cp.Id = cps.ProductId
WHERE case when (cps.Id is null or ('2010-12-24' < cps.BeginDate or '2010-12-24'>cps.EndDate)) then 0 else cps.[Level] end =
(select MAX(case when (F.Id is null or ('2010-12-24' < F.BeginDate or '2010-12-24'>F.EndDate)) then 0 else F.[Level] end )
from Product E with(nolock) left join PriceStrategy F with(nolock) on E.Id = F.ProductId
where E.Id = cp.Id AND F.SubType = 0)
AND cp.Id=113410SELECT CASE WHEN (cps.Id is null or ('2010-12-24' < cps.BeginDate or '2010-12-24'>cps.EndDate)) THEN cp.Imprest ELSE cps.CPSSpeciaImprest END AS Imprest
FROM PriceStrategy cps
LEFT JOIN Product cp ON cp.Id = cps.ProductId
WHERE case when (cps.Id is null or ('2010-12-24' < cps.BeginDate or '2010-12-24'>cps.EndDate)) then 0 else cps.[Level] end =
(select MAX(case when (F.Id is null or ('2010-12-24' < F.BeginDate or '2010-12-24'>F.EndDate)) then 0 else F.[Level] end )
from Product E with(nolock) left join PriceStrategy F with(nolock) on E.Id = F.ProductId
where E.Id = cp.Id AND F.SubType = 1)
AND cp.Id=113410
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)
select @SpecialPrice = 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 SpecialPrice desc
else
begin
select @SpecialPrice = 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)
select @CPSSpeciaImprest = 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 CPSSpeciaImprest desc
else
select @CPSSpeciaImprest = Imprest from tb1 where Id = '113410'select @SpecialPrice , @CPSSpeciaImprest
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'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)
select @SpecialPrice = 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
select @SpecialPrice = 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)
select @CPSSpeciaImprest = 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
select @CPSSpeciaImprest = Imprest from tb1 where Id = '113410'select @SpecialPrice , @CPSSpeciaImprest
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
还是不行 用不了 id 有时候是不需要用到的 去除后 取到很多数据 但其中 有id 那一条 就不对了