建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
解決SQLServer中Group無法實(shí)現(xiàn)的問題
在前一段時間中遇到一個需求..統(tǒng)計某一種商品在某一天中的銷售數(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顯示周末)
當(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
網(wǎng)站題目：解決SQLServer中Group無法實(shí)現(xiàn)的問題
文章網(wǎng)址：http://www.fisionsoft.com.cn/article/cdsecpe.html
