如何快速的实现一个连续日期
比如说,我需要查日期2009-5-1至2009-5-5
但是数据中可能没有3,5号
但是这几天的要显示一条空的记录
在具体的讲
2009-5-1至2009-5-5日期 金额
2009-5-1 100
2009-5-2 100
2009-5-4 100我需要得出的结果是日期 金额
2009-5-1 100
2009-5-2 100
2009-5-3 0
2009-5-4 100
2009-5-5 0怎么实现
比如说,我需要查日期2009-5-1至2009-5-5
但是数据中可能没有3,5号
但是这几天的要显示一条空的记录
在具体的讲
2009-5-1至2009-5-5日期 金额
2009-5-1 100
2009-5-2 100
2009-5-4 100我需要得出的结果是日期 金额
2009-5-1 100
2009-5-2 100
2009-5-3 0
2009-5-4 100
2009-5-5 0怎么实现
SELECT @StartDate = '2009-05-01', @EndDate = '2009-05-05';WITH DateTimeTable
AS
(
SELECT @StartDate as Date
UNION ALL
SELECT DATEADD(Day, 1, Date)
FROM DateTimeTable
WHERE DATEADD(Day, 1, Date) <= @EndDate
)
SELECT * FROM DateTimeTable
[Table] A Right Join Master.dbo.syslanguages B
On dateadd(day,langid,@Sdate)=日期
where dateadd(day,langid,@Sdate) between @Sdate and @Edate
INSERT @TB
SELECT '2009-5-1', 100 UNION ALL
SELECT '2009-5-2', 100 UNION ALL
SELECT '2009-5-4', 100DECLARE @DAYS INT
DECLARE @START SMALLDATETIME,@END SMALLDATETIME
SET @START='2009-05-01'
SET @END='2009-05-05'
SELECT @DAYS=DATEDIFF(DAY,@START,@END)+1;WITH CET AS
(
SELECT TOP(@DAYS) ID=ROW_NUMBER() OVER (ORDER BY GETDATE()) FROM syscolumns,sysobjects
)SELECT DAT AS [日期],CASE WHEN [金额] IS NULL THEN 0 ELSE [金额] END AS [金额]
FROM (
SELECT DATEADD(DAY,ID-1,@START) AS DAT FROM CET) T
LEFT JOIN @TB ON DAT=[日期]
/*
日期 金额
----------------------- -----------
2009-05-01 00:00:00 100
2009-05-02 00:00:00 100
2009-05-03 00:00:00 0
2009-05-04 00:00:00 100
2009-05-05 00:00:00 0
*/
INSERT @TB
SELECT '2009-5-1', 100 UNION ALL
SELECT '2009-5-2', 100 UNION ALL
SELECT '2009-5-4', 100--查询结果
SELECT IDATE, MAX(IAMOUNT) AS IAMOUNT
FROM
(
SELECT IDATE,IAMOUNT
FROM @TB
UNION
SELECT DATEADD(DAY,RID+1,'2009-05-01'), 0 AS IAMOUNT
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS RID
FROM @TB) LO ) LB
GROUP BY IDATE --结果
2009-05-01 00:00:00.000 100
2009-05-02 00:00:00.000 100
2009-05-03 00:00:00.000 0
2009-05-04 00:00:00.000 100
2009-05-05 00:00:00.000 0
DECLARE @TB TABLE(date DATETIME, money INT)
INSERT @TB
SELECT '2009-5-1', 100 UNION ALL
SELECT '2009-5-2', 100 UNION ALL
SELECT '2009-5-4', 100declare @startdate DATETIME,@endDate DATETIME
declare @t table(date datetime)
declare @date datetime
set @startdate = '2009-5-1'
set @enddate = '2009-5-5'while(@startdate<=@endDate)
begin
insert @t values(@startdate)
set @startdate=@startdate+1
endselect a.date,isnull(money,0) money from @t a
left join @tb b
on a.date = b.date/*
date money
----------------------- -----------
2009-05-01 00:00:00.000 100
2009-05-02 00:00:00.000 100
2009-05-03 00:00:00.000 0
2009-05-04 00:00:00.000 100
2009-05-05 00:00:00.000 0
*/
INSERT @TB
SELECT '2009-5-1', 100 UNION ALL
SELECT '2009-5-2', 100 UNION ALL
SELECT '2009-5-4', 100--SQL2000写法
CREATE TABLE #LI (IDATE DATETIME)
DECLARE @MARK DATETIME
SELECT @MARK = '2009-05-01'
WHILE (@MARK <'2009-05-06')
BEGIN
INSERT #LI(IDATE) VALUES (@MARK)
SELECT @MARK = DATEADD(DAY,1,@MARK)
ENDSELECT L.IDATE,ISNULL(T.IAMOUNT,0)
FROM
@TB T
RIGHT JOIN #LI L
ON CONVERT(varchar,T.IDATE,112) = convert(varchar,l.idate,112)
--DROP TABLE #LI --结果
2009-05-01 00:00:00.000 100
2009-05-02 00:00:00.000 100
2009-05-03 00:00:00.000 0
2009-05-04 00:00:00.000 100
2009-05-05 00:00:00.000 0