在SQL查询中需要帮助 [英] Need help in SQL query
本文介绍了在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 和 < span class =code-sdkkeyword> @ ToDate
解决方案
DECLARE @ FromDate DATE = ' 2018/08/01', @ ToDate DATE = ' < span class =code-string> 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 )
选择 * 来自 < span class =code-sdkkeyword> @ TB 其中 PriceUpdateDate @从日期 和 @ ToDate
else
选择 T1。* 来自 @TB T1 INNER JOIN @ M T2 ON T1.ItemId = T2.ItemId AND T1.PriceUpdateDate = T2.MaxDate
DECLARE @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 @ToDate
not - 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查询中需要帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文