一个SQL Group问题

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

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

有2种方案.

planA:

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

代码

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 31
GROUP BY a. number
order by DAY

 

 

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

planB:.通过自定义函数.

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

 

 

代码

CREATE function [ dbo ] . [ CN80s_FN_GetOrderType ]
(
@tabName nvarchar ( 2000 ),
@keyOrder nvarchar ( 255 )
) returns nvarchar ( 100 )
as
begin
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 @OrderType
end

GO

 

 

 

函数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

 

 

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

 

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_Count

FROM
(
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

 

 

第一篇blog文..请各位看官多多包涵..!

转载于:https://www.cnblogs.com/willsonchan/archive/2010/08/01/1789980.html