小弟请教一个查寻的日期问题，问题如下：以下是十一月的销售报告，我如何可以得到左面是具体日期(例如: 11/1/2006)，右边是具体销售额的查寻结果呢？请注意下面的数据中１２日没有销售数据，如何能在左侧显示１１月的所有日期，而且将１２日的数据显示为０，而不是NULL.
谢谢！1 $1,722.97
2 $2,161.02
3 $2,090.73
4 $2,213.71
6 $1,759.49
7 $1,713.14
8 $1,701.17
9 $2,291.87
10 $2,040.68
11 $2,123.06
13 $1,864.47
14 $1,750.59
15 $1,948.97
16 $2,148.17
17 $2,026.26
18 $2,032.09
20 $1,897.51
21 $1,771.48
22 $1,896.66
23 $2,024.81
24 $1,882.29
25 $1,976.44
27 $1,724.65
28 $1,697.80
29 $1,768.01
30 $2,210.79
谢谢！1 $1,722.97
2 $2,161.02
3 $2,090.73
4 $2,213.71
6 $1,759.49
7 $1,713.14
8 $1,701.17
9 $2,291.87
10 $2,040.68
11 $2,123.06
13 $1,864.47
14 $1,750.59
15 $1,948.97
16 $2,148.17
17 $2,026.26
18 $2,032.09
20 $1,897.51
21 $1,771.48
22 $1,896.66
23 $2,024.81
24 $1,882.29
25 $1,976.44
27 $1,724.65
28 $1,697.80
29 $1,768.01
30 $2,210.79
/*--生成列表 生成指定日期段的日期列表--邹建 2005.03(引用请保留此信息)--*//*--调用示例 --查询工作日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
--查询休息日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
--查询全部日期
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
--*/CREATE FUNCTION dbo.f_getdate(
@begin_date Datetime, --要查询的开始日期
@end_date Datetime, --要查询的结束日期
@bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
)RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
AS
BEGIN
DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
INSERT INTO @tb(a) SELECT TOP 366 0
FROM sysobjects a ,sysobjects b
IF @bz=0
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE IF @bz=1
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
SET @begin_date=DATEADD(Day,366,@begin_date)
END RETURN
END
GO
--创建测试环境
create table #t(id int,amount varchar(20))--插入测试数据
insert #t(id,amount)
select '1','$1,722.97' union all
select '2','$2,161.02' union all
select '3','$2,090.73' union all
select '4','$2,213.71' union all
select '6','$1,759.49' union all
select '7','$1,713.14' union all
select '8','$1,701.17' union all
select '9','$2,291.87' union all
select '10','$2,040.68' union all
select '11','$2,123.06' union all
select '13','$1,864.47' union all
select '14','$1,750.59' union all
select '15','$1,948.97' union all
select '16','$2,148.17' union all
select '17','$2,026.26' union all
select '18','$2,032.09' union all
select '20','$1,897.51' union all
select '21','$1,771.48' union all
select '22','$1,896.66' union all
select '23','$2,024.81' union all
select '24','$1,882.29' union all
select '25','$1,976.44' union all
select '27','$1,724.65' union all
select '28','$1,697.80' union all
select '29','$1,768.01' union all
select '30','$2,210.79'--求解过程
select _d.date,_t.amount
from f_getdate('20061101','20061130',null) _d
left join #t _t on day(_d.date) = _t.id--删除测试环境
drop table #t/*--测试结果
date amount
------------------------------------------------------ --------------------
2006-11-01 00:00:00.000 $1,722.97
2006-11-02 00:00:00.000 $2,161.02
2006-11-03 00:00:00.000 $2,090.73
2006-11-04 00:00:00.000 $2,213.71
2006-11-05 00:00:00.000 NULL
2006-11-06 00:00:00.000 $1,759.49
2006-11-07 00:00:00.000 $1,713.14
2006-11-08 00:00:00.000 $1,701.17
2006-11-09 00:00:00.000 $2,291.87
2006-11-10 00:00:00.000 $2,040.68
2006-11-11 00:00:00.000 $2,123.06
2006-11-12 00:00:00.000 NULL
2006-11-13 00:00:00.000 $1,864.47
2006-11-14 00:00:00.000 $1,750.59
2006-11-15 00:00:00.000 $1,948.97
2006-11-16 00:00:00.000 $2,148.17
2006-11-17 00:00:00.000 $2,026.26
2006-11-18 00:00:00.000 $2,032.09
2006-11-19 00:00:00.000 NULL
2006-11-20 00:00:00.000 $1,897.51
2006-11-21 00:00:00.000 $1,771.48
2006-11-22 00:00:00.000 $1,896.66
2006-11-23 00:00:00.000 $2,024.81
2006-11-24 00:00:00.000 $1,882.29
2006-11-25 00:00:00.000 $1,976.44
2006-11-26 00:00:00.000 NULL
2006-11-27 00:00:00.000 $1,724.65
2006-11-28 00:00:00.000 $1,697.80
2006-11-29 00:00:00.000 $1,768.01
2006-11-30 00:00:00.000 $2,210.79
*/
select _d.date,isnull(_t.amount,0)……
--生成一个日期列表再左关联销售表select A.[date], 销售金额=isnull(B.销售金额, 0)
from
(
select [date]='2006-11-01'
union all
select '2006-11-02'
union all
...
)A
left join 销售表 as B on day(A.[date])=B.销售日期
@s smalldatetime
asdeclare @num int
declare @str varchar(1000)declare @a table(a int,b varchar(100))
insert @a select 1 ,'$1,722.97'
union all select 2 ,'$2,161.02'
union all select 3 ,'$2,090.73'
union all select 4 ,'$2,213.71'
union all select 6 ,'$1,759.49'
union all select 7 ,'$1,713.14'
union all select 8 ,'$1,701.17'
union all select 9 ,'$2,291.87'
union all select 10 ,'$2,040.68'
union all select 11 ,'$2,123.06'
union all select 13 ,'$1,864.47'
union all select 14 ,'$1,750.59'
union all select 15 ,'$1,948.97'
union all select 16 ,'$2,148.17'
union all select 17 ,'$2,026.26'
union all select 18 ,'$2,032.09'
union all select 20 ,'$1,897.51'
union all select 21 ,'$1,771.48'
union all select 22 ,'$1,896.66'
union all select 23 ,'$2,024.81'
union all select 24 ,'$1,882.29'
union all select 25 ,'$1,976.44'
union all select 27 ,'$1,724.65'
union all select 28 ,'$1,697.80'
union all select 29 ,'$1,768.01'
union all select 30 ,'$2,210.79'
if object_id('yyy') is not null
drop table yyy
set @num=datediff(day,@s,dateadd(m,1,@s))
set @str='select top ' +ltrim(@num) +' id=identity(int,0,1),[date]=convert(varchar(20),'' '') into yyy from sysobjects'
exec(@str)
update yyy set [date]=ltrim(dateadd(day,id,@s))
select convert(varchar(10),cast([date] as datetime),120) [date],isnull(b,0) b from yyy a Left Join @a b On a.id+1=b.ago
FormRecord '2006-11-01'
RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
最后的定义似乎太小了，我重新定义成nvarchar(10)就没有问题了。也可能是我用英文系统地关系吧，Weekday最大字数是9 (Wednesday).