按日统计的问题，估计有点麻烦，搞过的朋友指点一下我吧：）

[insnowind]

票据表：

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）怎么样提高查询速度，我写了一个不正确而且比较慢

谢谢大家：）

[dawugui]

无论这一天有没有数据，每一天每一个producer都要显示出来，结果显示0
你这个几百天都要显示啊?

[insnowind]

无论这一天有没有数据，每一天每一个producer都要显示出来，结果显示0
你这个几百天都要显示啊?
==============================
大哥，我正在找你，看你有没有在线呢
是这样的，每天都显示，只显示一个月的

[insnowind]

比如说我要看2006年1月各单位的产量，就显示1月份每一天的
如果要看2006年的，就显示12个月每月的总和就行了

[paoluo]

Create Table 票据表
(FoodID Int,
FoodWeight Int,
SaleTime DateTime,
ProducerID Varchar(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 食品类别表
(FoodID Int,
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
/*
ProducerID 2006-01-01 2006-01-02 ... 2006-01-31
10000 10.0 50.0 .0 .0
10001 240.0 10.0 .0 .0
10002 50.0 360.0 .0 .0
*/

[gahade]

第一个结果的

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

[paoluo]

Create Table 票据表
(FoodID Int,
FoodWeight Int,
SaleTime DateTime,
ProducerID Varchar(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 食品类别表
(FoodID Int,
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_MonthReport(@StartDate DateTime, @EndDate DateTime)
As
Begin
Select Top 500 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(7), SaleTime, 120) When ''' + SaleTime + ''' Then FoodWeight * Rate Else 0 End) As '+ QUOTENAME(SaleTime)
From (Select Distinct Convert(Varchar(7), 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_MonthReport '2006-01-01', '2006-12-31'
GO
Drop Table 票据表, 食品类别表
Drop Procedure SP_MonthReport
--Result
/*
ProducerID 2006-01 2006-02 2006-03 ... 2006-12
10000 60.0 .0 .0 .0 .0
10001 250.0 .0 .0 .0 .0
10002 410.0 .0 .0 .0 .0
*/

[gahade]

第二个结果,测试数据在上一个例子中

select top 12 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(month,id,@bdate)
from #t

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when convert(char(7),SaleTime,120)='''+convert(char(7),dt,120)+''' then FoodWeight*Rate else 0 end) as '''+convert(char(7),dt,120)+''''
from #t

exec ('select ProducerID'+@sql+' from 票据表,食品类别表 where 票据表.FoodID=食品类别表.FoodID group by ProducerID')
drop table #t

[paoluo]

--放到一起

Create Table 票据表
(FoodID Int,
FoodWeight Int,
SaleTime DateTime,
ProducerID Varchar(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 食品类别表
(FoodID Int,
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
--創建月報表的存儲過程
Create Procedure SP_MonthReport(@StartDate DateTime, @EndDate DateTime)
As
Begin
Select Top 500 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(7), SaleTime, 120) When ''' + SaleTime + ''' Then FoodWeight * Rate Else 0 End) As '+ QUOTENAME(SaleTime)
From (Select Distinct Convert(Varchar(7), 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'
--執行月報表
EXEC SP_MonthReport '2006-01-01', '2006-12-31'
GO
Drop Table 票据表, 食品类别表
Drop Procedure SP_DayReport, SP_MonthReport
--Result

[gahade]

鱼的速度真快! :)

[insnowind]

二位果然是高手，我在仔细揣摩一下：）