该内嵌函数为什么报错？

Question

CREATE FUNCTION ZBaseTabelByTypeShopWeek(@StartTime datetime,@EndTime datetime)

RETURNS TABLE 
AS
RETURN 
(
SELECT   substring(PatternNumBer,1,1) as type, substring(ShopType,1,1) as shop, floor(datediff(dd,@StartTime,[Time])/7)+1 as Week,  SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM     dbo.SalesInfos 
WHERE   ( datediff(day,Time,@StartTime)<=0 and datediff(day, Time,@EndTime)>=0 )
GROUP BY substring(PatternNumBer,1,1), substring(ShopType,1,1), floor(datediff(dd,@StartTime,[Time])/7)
)
GO
运行环境sql server2000
消息 8120，级别 16，状态 1，过程 ZBaseTabelByTypeShopWeek，第 10 行
列 'dbo.SalesInfos.Time' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

Answer 1

CREATE FUNCTION ZBaseTabelByTypeShopWeek(@StartTime datetime,@EndTime datetime)RETURNS TABLE  
AS
RETURN  
(
SELECT 
  substring(PatternNumBer,1,1) as type, 
  substring(ShopType,1,1) as shop, 
  floor(datediff(dd,@StartTime,[Time])/7)+1 as Week, 
  SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos  
WHERE ( datediff(day,Time,@StartTime)<=0 and datediff(day, Time,@EndTime)>=0 )
GROUP BY 
  substring(PatternNumBer,1,1), 
  substring(ShopType,1,1), 
  floor(datediff(dd,@StartTime,[Time])/7)+1)
GO

Answer 2

CREATE FUNCTION ZBaseTabelByTypeShopWeek(@StartTime datetime,@EndTime datetime)RETURNS TABLE 
AS
RETURN 
(
SELECT substring(PatternNumBer,1,1) as type, substring(ShopType,1,1) as shop, floor(datediff(dd,@StartTime,[Time])/7)+1 as Week, SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos 
WHERE ( datediff(day,Time,@StartTime)<=0 and datediff(day, Time,@EndTime)>=0 )
GROUP BY substring(PatternNumBer,1,1), substring(ShopType,1,1), floor(datediff(dd,@StartTime,[Time])/7)+1        --加1
)
GO

Answer 3

CREATE FUNCTION ZBaseTabelByTypeShopWeek(@StartTime datetime,@EndTime datetime)RETURNS TABLE  
AS
RETURN  
(
SELECT substring(PatternNumBer,1,1) as type, 
substring(ShopType,1,1) as shop, 
floor(datediff(dd,@StartTime,[Time])/7)+1 as Week, 
SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos  
WHERE ( datediff(day,Time,@StartTime)<=0 and datediff(day, Time,@EndTime)>=0 )
GROUP BY substring(PatternNumBer,1,1), 
substring(ShopType,1,1), 
[Time] 
)try