栏目导航
热点推荐
- 心得:Sql语句绝妙用法
- SQL Server数据转换服务小妙招
- SQL Server数据库常用的T-SQL命
- 详解SQLServer 存储过程
- SQL数据库挂马解决方案
- 分析 SQL Server 的内存为何不断
- SQL Server数据库查询速度慢的原
- 详细讲解删除SQL Server日志的具
- 三步骤快速创建SQL Server数据库
- SQL Server事务日志的几个常用操
- SQLServer应用程序中的高级SQL注
- 使用SQL Server 2008进行服务器
阅览排行
SQL Server中Group查询无法实现解决办法
www.jz123.cn 2010-09-09 来源: 中国建站 责任编辑(袁袁) 我要投递新闻
在前一段时间中遇到一个需求..统计某一种商品在某一天中的销售数量,当天没有销售的时候,数量显示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(这个更长)
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->USE [CN80s.DDPM]GO/****** Object: UserDefinedFunction [dbo].[FormatDateTime] Script Date: 08/01/2010 16:28:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE function [dbo].[FormatDateTime](@Date datetime,@formatStr varchar(20))returns varchar(16)asbegindeclare @tempstr varchar(20),@index int,@retStr varchar(20),@formatLen int,@str1 varchar(6),@str2 varchar(6),@str3 varchar(6),@j intdeclare @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)elsebeginselect @tempformat=@formatStr,@formatLen = len(@formatStr)if @formatLen>8beginset @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=@str2set @str2=left(@str2,@index-1)set @str3=right(@str3,len(@str3)-@index-1)endselect @tempstr = Convert(char(10),@Date,20),@str1=isnull(@str1,''),@str2=isnull(@str2,''),@str3=isnull(@str3,''),@j=0while @index <> 0beginset @index = charindex('-',@tempstr)if @j=0select @retstr=left(@tempstr,@index-1)+@str1,@j=@j+1else set @retstr=@retstr+left(@tempstr,@index-1)+@str2select @tempstr=right(@tempstr,len(@tempstr)-@index)set @index= charindex('-',@tempstr)endset @retstr=@retstr+@tempstr+@str3endreturn @retstrend 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
上一篇:SQL server2000安装文件挂起错误问题解决办法 下一篇:SQL中如何实现行转列,列转行的操作