按日统计的问题，估计有点麻烦，搞过的朋友指点一下我吧：）
票据表：
FoodID FoodWeight SaleTime ProducerID
1 10 2006-01-01 10000
2 120 2006-01-01 10001
3 10 2006-01-01 10002
2 180 2006-01-02 10002
1 10 2006-01-02 10001
3 10 2006-01-02 10000
食品类别表：
FoodID Rate
1 1.0
2 2.0
3 5.0
票据表的记录数量超过100万条，现在希望能有如下结果：
2006-01-01 2006-01-02 2006-01-03 2006-01-04 2006-01-05 ……
10000 10 50 ………………
10001 240 10 ………………
10002 50 360 ………………
还有
2006-01 2006-02 2006-03 2006-04 …………
10001 *** *** *** ***
10002 *** *** *** ***
10003 *** *** *** ***
……
的效果，现在有几个问题：
（1）无论这一天有没有数据，每一天每一个producer都要显示出来，结果显示0
（2）票据表中的数量不是直接的累加，必须从食品类别表中找到rate,然后foodweight乘以rate得到结果
（3）怎么样提高查询速度，我写了一个不正确而且比较慢
谢谢大家：）
[解决办法]
无论这一天有没有数据，每一天每一个producer都要显示出来，结果显示0
你这个几百天都要显示啊?
[解决办法]
Create Table 票据表
(FoodIDInt,
FoodWeightInt,
SaleTimeDateTime,
ProducerIDVarchar(10))
Insert 票据表 Select 1, 10, '2006-01-01 ', '10000 '
Union All Select 2, 120, '2006-01-01 ', '10001 '
Union All Select 3, 10, '2006-01-01 ', '10002 '
Union All Select 2, 180, '2006-01-02 ', '10002 '
Union All Select 1, 10, '2006-01-02 ', '10001 '
Union All Select 3, 10, '2006-01-02 ', '10000 '
Create Table 食品类别表
(FoodIDInt,
Rate Numeric(10, 1))
Insert 食品类别表 Select 1, 1.0
Union All Select 2, 2.0
Union All Select 3, 5.0
GO
Create Procedure SP_DayReport(@StartDate DateTime, @EndDate DateTime)
As
Begin
Select Top 50 ID = Identity(Int, 0, 1) Into #T From Syscolumns A, Syscolumns B
Declare @S Nvarchar(4000)
Select @S = 'Select ProducerID '
Select @S = @S + ', SUM(Case Convert(Varchar(10), SaleTime, 120) When ' ' ' + SaleTime + ' ' ' Then FoodWeight * Rate Else 0 End) As '+ QUOTENAME(SaleTime)
From (Select Convert(Varchar(10), DateAdd(dd, ID, @StartDate), 120) As SaleTime From #T Where ID <= DateDiff(dd,@StartDate, @EndDate)) A
Select @S = @S + N ' From 票据表 A Inner Join 食品类别表 B On A.FoodID = B.FoodID Group By ProducerID '
EXEC(@S)
Drop Table #T
End
GO
EXEC SP_DayReport '2006-01-01 ', '2006-01-31 '
GO
Drop Table 票据表, 食品类别表
Drop Procedure SP_DayReport
--Result
/*
ProducerID2006-01-012006-01-02...2006-01-31
1000010.050.0.0.0
10001240.010.0.0.0
1000250.0360.0.0.0
*/
[解决办法]
第一个结果的
create table 票据表(FoodID int,FoodWeight int,SaleTime datetime,ProducerID varchar(20))
insert into 票据表
select 1,10, '2006-01-01 ', '10000 '
union all select 2,120, '2006-01-01 ', '10001 '
union all select 3,10, '2006-01-01 ', '10002 '
union all select 2,180, '2006-01-02 ', '10002 '
union all select 1,10, '2006-01-02 ', '10001 '
union all select 3,10, '2006-01-02 ', '10000 '
create table 食品类别表(FoodID int,Rate numeric(20,6))
insert into 食品类别表
select 1,1.0
union all select 2,2.0
union all select 3,5.0
select top 31 identity(int,0,1) as id into #t from sysobjects
alter table #t add dt datetime
go
declare @bdate datetime
set @bdate= '2006-01-01 '
update #t
set dt=dateadd(day,id,@bdate)
from #t
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',sum(case when SaleTime= ' ' '+convert(char(10),dt,120)+ ' ' ' then FoodWeight*Rate else 0 end) as ' ' '+convert(char(10),dt,120)+ ' ' ' '
from #t where dt <dateadd(month,1,@bdate)
exec ( 'select ProducerID '+@sql+ ' from 票据表,食品类别表 where 票据表.FoodID=食品类别表.FoodID group by ProducerID ')
drop table #t
