解決SQL Server中Group無法實(shí)現(xiàn)的問題

作者：wilsonChan 2010-08-03 09:41:14

本文將解決SQL Server中一個SQL Group不能解決的問題，提出了兩種不同的方案供大家來參考。

在前一段時間中遇到一個需求..統(tǒng)計(jì)某一種商品在某一天中的銷售數(shù)量,當(dāng)天沒有銷售的時候,數(shù)量顯示0.

這個不能用一般的Group來實(shí)現(xiàn).所以需要變通一下,跟一個有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(產(chǎn)生一定范圍的數(shù)字的數(shù)字，這里需要產(chǎn)生1-31的數(shù)字.)

但是這一種方法有缺陷,每一個月不一定都是31天.并且我們配置的SQL賬號不一定有權(quán)限來訪問這個函數(shù).

planB:.通過自定義函數(shù).

自定義函數(shù)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  --點(diǎn)的位置      
declare @s1 nvarchar(100) -- 臨時變量1      
declare @s2 nvarchar(100) -- 臨時變量2    --去除排序規(guī)則    
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

函數(shù)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



CREATE function [dbo].[FormatDateTime](@Date datetime,@formatStr varchar(20))
returns varchar(16)
as
begin
declare @tempstr varchar(20),@index int,@retStr varchar(20),@formatLen int,@str1 varchar(6),@str2 varchar(6),@str3 varchar(6),@j int
declare @tempformat varchar(20)
select @tempformat=@formatStr,@formatStr = Upper(@formatStr),@index=-1,@retstr=''
if @formatStr='MM/DD/YYYY'
set @retstr= convert(varchar(10),@date,101)
else if @formatstr='YYYY-MM-DD'
set @retstr = Convert(char(10),@Date,20)
else if @formatStr='YYYY.MM.DD'
set @retstr= Convert(varchar(10),@Date,102)
else if @formatStr='YYYY/MM/DD'
set @retstr= Convert(varchar(10),@Date,111)
else if @formatStr='DD/MM/YYYY'
set @retstr= Convert(varchar(10),@Date,103)
else if @formatStr='DD.MM.YYYY'
set @retstr= Convert(varchar(10),@Date,104)
else if @formatStr='DD-MM-YYYY'
set @retstr= Convert(varchar(10),@Date,105)
else if @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
else set @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

可以看看調(diào)用這個函數(shù)的結(jié)果.

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

參數(shù)依次為:開始時間,結(jié)束時間,顯示狀態(tài)(null:顯示所有日期,0顯示所有工作日(星期一~星期五),1:顯示周末,2顯示周末)

函數(shù)結(jié)果

當(dāng)然這個函數(shù)有其他的擴(kuò)展應(yīng)用請各位看官天馬行空.~

實(shí)際應(yīng)用,這里寫了一個存儲過程

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