本文介绍了在SQL查询中需要帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
DECLARE @ TB 表(ItemId INT ,PriceUpdateDate DATE ,价格 DECIMAL ( 18 , 3 )) INSERT INTO @ TB VALUES ( 101 ,' 2018 -07-20', 375 ) INSERT INTO @ TB VALUES ( 101 ,' 2018-07-25', 390 ) INSERT INTO @ TB VALUES ( 101 ,' 2018-08-10', 400 ) INSERT INTO @ TB VALUES ( 101 ,' 2018-08-15' , 450 ) INSERT INTO @ TB VALUES ( 101 ,' 2018-08-17', 300 ) INSERT INTO @ TB VALUES ( 102 ,' 2018-07- 17', 400 ) INSERT INTO @ TB VALUES ( 102 ,' 2018-07-18', 450 ) INSERT INTO @TB VALUES ( 102 ,' 2018-07-19', 300 ) INSERT INTO @ TB VALUES ( 102 ,' 2018-08-05', 375 ) INSERT INTO @ TB VALUES ( 102 ,' 2018-08-15', 390 ) SELECT * FROM @ TB DECLARE @ FromDate DATE = ' 2018-08-01', @ ToDate DATE = ' 2018-08-31' 选择 * 来自 @ TB 其中 PriceUpdateDate 之间 @ FromDate 和 @ ToDate
根据我的要求,上面给出的标准有记录。但是我们假设没有与@相对应的记录。我们通过的FromDate和@ToDate。 在这种情况下,我想要最后更新的记录,即@FromDate之前的第一个记录,即ItemId = 101和'2018-07的'2018-07-25' -19'对于ItemId = 102 我尝试过: 我试过
DECLARE @ FromDate DATE = ' 2018-08-01', @ ToDate DATE = ' 2018-08-31' 选择 * 来自 @ TB 其中 PriceUpdateDate @ FromDate 和 @ ToDate解决方案
DECLARE @ FromDate DATE = ' 2018/08/01', @ ToDate DATE = ' 2018/08/31' select * 来自 @ TB 其中 PriceUpdateDate 之间 @ FromDate 和 @ ToDate
not - but /
您需要知道每个日期相关的日期item ... DECLARE @ m TABLE (ItemId INT ,MaxDate DATE ) 插入 进入 @ m SELECT ItemId,MAX(PriceUpdateDate) FROM @ TB WHERE PriceUpdateDate< @ FromDate GROUP BY ItemId
如果原始查询没有返回任何记录,你可以做一些事情
if 存在(选择 top 1 * 来自 @ TB 其中 PriceUpdateDate @ FromDate 和 @ ToDate ) 选择 * 来自 @ TB 其中 PriceUpdateDate @从日期 和 @ ToDate else 选择 T1。* 来自 @TB T1 INNER JOIN @ M T2 ON T1.ItemId = T2.ItemId AND T1.PriceUpdateDate = T2.MaxDateDECLARE @TB table (ItemId INT, PriceUpdateDate DATE, Price DECIMAL(18,3)) INSERT INTO @TB VALUES (101,'2018-07-20', 375) INSERT INTO @TB VALUES (101,'2018-07-25', 390) INSERT INTO @TB VALUES (101,'2018-08-10', 400) INSERT INTO @TB VALUES (101,'2018-08-15', 450) INSERT INTO @TB VALUES (101,'2018-08-17', 300) INSERT INTO @TB VALUES (102,'2018-07-17', 400) INSERT INTO @TB VALUES (102,'2018-07-18', 450) INSERT INTO @TB VALUES (102,'2018-07-19', 300) INSERT INTO @TB VALUES (102,'2018-08-05', 375) INSERT INTO @TB VALUES (102,'2018-08-15', 390) SELECT * FROM @TB DECLARE @FromDate DATE='2018-08-01',@ToDate DATE='2018-08-31' select * from @TB where PriceUpdateDate between @FromDate and @ToDate
There are records for the criteria given above which is as per my requirement.But let us suppose there is no record corresponding to @FromDate and @ToDate that we passed. In that case i want the last updated record i.e. very first record before @FromDate which is '2018-07-25' for ItemId=101 and '2018-07-19' for ItemId=102 What I have tried: I tried
DECLARE @FromDate DATE='2018-08-01',@ToDate DATE='2018-08-31' select * from @TB where PriceUpdateDate between @FromDate and @ToDate 解决方案 DECLARE @FromDate DATE='2018/08/01',@ToDate DATE='2018/08/31' select * from @TB where PriceUpdateDate between @FromDate and @ToDatenot - but /
You need to know which dates are relevent for each item ... DECLARE @m TABLE (ItemId INT, MaxDate DATE) insert into @m SELECT ItemId, MAX(PriceUpdateDate) FROM @TB WHERE PriceUpdateDate < @FromDate GROUP BY ItemIdand then you can do something if no records are returned from your original query
if exists (select top 1 * from @TB where PriceUpdateDate between @FromDate and @ToDate) select * from @TB where PriceUpdateDate between @FromDate and @ToDate else select T1.* from @TB T1 INNER JOIN @M T2 ON T1.ItemId = T2.ItemId AND T1.PriceUpdateDate = T2.MaxDate更多推荐
在SQL查询中需要帮助
发布评论