189 8069 5689
在前一段时间中遇到一个需求..统计某一种商品在某一天中的销售数量,当天没有销售的时候,数量显示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
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_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