如何在表中不存在数据时读取默认值。 [英] How to read default value when data not present in table.
问题描述
如果我给@startdate = 01-01-2015和@enddate = 01-31-2015作为参数。如果数据不在01-20-2015那么需要返回值为0,否则从表中返回数据。请帮忙..!
生成所需范围内的日期列表,并使用ISNULL函数将其与您的表连接,以获得您的默认值。
例如我创建了一些简单的样本数据,如CREATE < span class =code-keyword> TABLE CP5
(
aDate date ,
aValue int
)
insert into CP5 值(getdate() - 1 , 1 )
插入 进入 CP5 值(getdate() - 2 , 2 )
插入 进入 CP5 值(getdate() - < span class =code-digit> 3 , 3 )
insert 进入 CP5 值(getdate() - 5 ,< span class =code-digit> 5 )
insert 进入 CP5 值(getdate() - 6 , 7 )设置我的开始/结束日期如下
DECLARE @startdate date
SET @startdate = getdate() - 7 - 即2015-02-24
DECLARE @ enddate date
SET @ enddate = getdate(); - 即2015-03-03
我现在可以使用此查询来获取范围中的所有日期,其中包含表中的值或0
WITH q AS
(
SELECT @startdate AS datum
UNION ALL
SELECT DATEADD(dd, 1 ,datum)
FROM q WHERE DATEADD(dd, 1 ,datum)< @ enddate
)
SELECT datum,ISNULL(aValue, 0 ) as aValue
FROM q
LEFT OUTER JOIN CP5 ON q .datum = CP5.aDate
给我这些结果
2015-02-24 0
2015-02-25 7
2015-02-26 5
2015-02-27 0
2015-02-28 3
2015-03-01 2
2015-03-02 1
CTE的想法改编自Tip 在SQL中生成序列 [ ^ ]作者:Manas Bhardwaj(我的名字也出现在该提示旁边,但我不能赞成日期顺序总的来说)
这个查询可以帮到你。
select isnull(Colum n1,0),isnull(Column2,0)来自TableName,其中@StartDate和@EndDate之间的Yourdate
祝你好运。
Hi Susheel ..表中没有空值。
Hi if I am giving @startdate = 01-01-2015 and @enddate = 01-31-2015 as parameters. If data is not there for 01-20-2015 then need to return value as 0 else return data from table. Can any help please..!
Generate a list of dates within the range required and join that with your table using the ISNULL function to derive your default value.
For example I created some simple sample data like thisCREATE TABLE CP5 ( aDate date, aValue int ) insert into CP5 values(getdate() - 1,1) insert into CP5 values(getdate() - 2,2) insert into CP5 values(getdate() - 3,3) insert into CP5 values(getdate() - 5,5) insert into CP5 values(getdate() - 6,7)Set up my start/end dates as follows
DECLARE @startdate date SET @startdate = getdate() - 7 -- i.e. 2015-02-24 DECLARE @enddate date SET @enddate = getdate(); -- i.e. 2015-03-03
I can now use this query to get all of the dates in the range with either a value from the table or 0
WITH q AS ( SELECT @startdate AS datum UNION ALL SELECT DATEADD(dd, 1, datum) FROM q WHERE DATEADD(dd, 1, datum) < @enddate ) SELECT datum, ISNULL(aValue, 0) as aValue FROM q LEFT OUTER JOIN CP5 ON q.datum = CP5.aDate
Gives me these results
2015-02-24 0 2015-02-25 7 2015-02-26 5 2015-02-27 0 2015-02-28 3 2015-03-01 2 2015-03-02 1
The idea for the CTE was adapted from the Tip Generating a Sequence in SQL[^] by Manas Bhardwaj (My name also appears next to that tip but I can't take credit for the date sequence bit at all)
This query can help you.
select isnull(Column1,0) , isnull(Column2,0) from TableName where Yourdate between @StartDate and @EndDate
Good luck.
Hi Susheel.. nulls are not present in table.
这篇关于如何在表中不存在数据时读取默认值。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!