解决SQL Server中Group无法实现的问题

在前一段时间中遇到一个需求..统计某一种商品在某一天中的销售数量,当天没有销售的时候,数量显示0.

这个不能用一般的Group来实现.所以需要变通一下,跟一个有1-31的一个集合来Group.

有2种方案.

planA:

SELECT SUM(ISNULL(BidsTrade_Money, 0))   
AS [MONEY], a.number   
AS [DAY]FROM MASTER..spt_values a      
LEFT JOIN DDPM_T_Comm_BidsTrade b          
ON  a.type = 'p'             
 AND month([BidsTrade_DateCreated])='5'              
AND a.number = DAY(b.[BidsTrade_DateCreated])              
AND YEAR([BidsTrade_DateCreated]) = '2010'              
WHERE a.number BETWEEN 1 AND 31GROUP BY a.numberorder by DAY

使用MASTER..spt_values(产生一定范围的数字的数字，这里需要产生1-31的数字.)

但是这一种方法有缺陷,每一个月不一定都是31天.并且我们配置的SQL账号不一定有权限来访问这个函数.

planB:.通过自定义函数.

自定义函数GetOrderType(比较长- -..)

CREATE function [dbo].[CN80s_FN_GetOrderType](    @tabName nvarchar(2000),      
@keyOrder nvarchar(255))returns nvarchar(100)asbegin    declare @OrderTable nvarchar(255)  --表名     
 declare @OrderName nvarchar(255)  --字段名      
declare @OrderType nvarchar(255)  --字段类型      
declare @OrderPrec nvarchar(50)  --字段长度      
declare @OrderDot int  --点的位置      
declare @s1 nvarchar(100) -- 临时变量1      
declare @s2 nvarchar(100) -- 临时变量2    --去除排序规则    
set @keyorder=REPLACE(@keyorder, ' asc', '')    --求表名、字段名      
set @OrderDot=CHARINDEX('.', @keyorder)      
IF @OrderDot > 0          
BEGIN              
SET @OrderTable = SUBSTRING(@keyorder, 0, @OrderDot)              
SET @OrderName = SUBSTRING(@keyorder, @OrderDot + 1, LEN(@keyorder))         
END      
ELSE          
BEGIN              
SET @OrderTable = @tabName              
SET @OrderName = @keyorder          
END    --去除方括号    set @s1=REPLACE(REPLACE (@OrderTable,'[',''),']','')      
set @s2=REPLACE(REPLACE (@OrderName,'[',''),']','')    --求字段类型、字段长度      
SELECT @OrderType=t.[name], @OrderPrec=c.prec      
FROM sysobjects o       
JOIN syscolumns c on o.id=c.id      
JOIN systypes t on c.xusertype=t.xusertype      
WHERE o.name = @s1 AND c.[name] = @s2      
if @OrderType is null    begin          
SET @OrderType='Sql_Variant'      
end    else    begin          
IF CHARINDEX('char', @OrderType) > 0             
SET @OrderType = @OrderType + '(' + CAST(@OrderPrec AS nvarchar) + ')'      
end    return @OrderTypeendGO

函数2(这个更长)

USE[CN80s.DDPM]
GO

/****** Object: UserDefinedFunction [dbo].[FormatDateTime] Script Date: 08/01/2010 16:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATEfunction[dbo].[FormatDateTime](@Datedatetime,@formatStrvarchar(20))
returnsvarchar(16)
as
begin
declare@tempstrvarchar(20),@indexint,@retStrvarchar(20),@formatLenint,@str1varchar(6),@str2varchar(6),@str3varchar(6),@jint
declare@tempformatvarchar(20)
select@tempformat=@formatStr,@formatStr=Upper(@formatStr),@index=-1,@retstr=''
if@formatStr='MM/DD/YYYY'
set@retstr=convert(varchar(10),@date,101)
elseif@formatstr='YYYY-MM-DD'
set@retstr=Convert(char(10),@Date,20)
elseif@formatStr='YYYY.MM.DD'
set@retstr=Convert(varchar(10),@Date,102)
elseif@formatStr='YYYY/MM/DD'
set@retstr=Convert(varchar(10),@Date,111)
elseif@formatStr='DD/MM/YYYY'
set@retstr=Convert(varchar(10),@Date,103)
elseif@formatStr='DD.MM.YYYY'
set@retstr=Convert(varchar(10),@Date,104)
elseif@formatStr='DD-MM-YYYY'
set@retstr=Convert(varchar(10),@Date,105)
elseif@formatStr='YYYYMMDD'
set@retstr=Convert(varchar(10),@Date,112)
else
begin
select@tempformat=@formatStr,@formatLen=len(@formatStr)
if@formatLen>8
begin
set@index=charindex('M',@tempformat)
select@str1=right(left(@tempformat,@index-1),@index-5),@str2=right(@tempformat,@formatLen-@index-1)
select@index=charindex('D',@str2),@str3=@str2
set@str2=left(@str2,@index-1)
set@str3=right(@str3,len(@str3)-@index-1)
end
select@tempstr=Convert(char(10),@Date,20),@str1=isnull(@str1,''),@str2=isnull(@str2,''),@str3=isnull(@str3,''),@j=0
while@index<>0
begin
set@index=charindex('-',@tempstr)
if@j=0
select@retstr=left(@tempstr,@index-1)+@str1,@j=@j+1
elseset@retstr=@retstr+left(@tempstr,@index-1)+@str2
select@tempstr=right(@tempstr,len(@tempstr)-@index)
set@index=charindex('-',@tempstr)
end
set@retstr=@retstr+@tempstr+@str3
end
return@retstr
end


GO

可以看看调用这个函数的结果.

SELECT * FROM CN80s_DDPM_FN_GETDATE('2010-05-1','2010-05-31',null)

参数依次为:开始时间,结束时间,显示状态(null:显示所有日期,0显示所有工作日(星期一~星期五),1:显示周末,2显示周末)

当然这个函数有其他的扩展应用请各位看官天马行空.~

实际应用,这里写了一个存储过程

SELECT a.day , isnull(BidsCombo_Price,0) as   
BidsCombo_Price, isnull( BidsTrade_Count,0) as    
BidsTrade_CountFROM (          
SELECT year(Date) as year,month(Date) as month,day(Date) as [day] 
FROM dbo.CN80s_DDPM_FN_GETDATE(@beginTime,@endTime,NULL) ) a       
LEFT JOIN          
DDPM_V_BidsTradeRecount b         
 ON  a.day = b.day          
AND a.year=b.year          
AND a.month=b.month    GO