34,587
社区成员
发帖
与我相关
我的任务
分享
select
case when
isnull(fSize,'')='' then null
else
cast(left(fSize,charindex('|',fSize)-1) as numeric(5,2))*cast(stuff(fSize,1,charindex('|',fSize),'') as numeric(5,2))
end
from
(select '1.5|2' as fSize) t
--自定义函数
create function f_xy(@str varchar(3))
returns int
as
begin
declare @num int
if len(@str)=0
set @num=1
else
set @num=cast(left(@str,1) as int)*cast(right(@str,1) as int)
return @num
end
--查询
select dbo.f_xy(''),dbo.f_xy('3|5')
--结果
/*
1 15
*/
case when
isnull(fSize,'')='' then ''
else
left(fSize,charindex('|',fSize)-1)*stuff(fSize,1,charindex('|',fSize),'')
end
--自定义函数
Create function f_getm(@date datetime)
returns int
as
begin
declare @monthid int
if day(@date)>=26
set @monthid=month(@date)+1
else
set @monthid=month(@date)
return @monthid
end
--测试
select dbo.f_getm('2008-4-26'),dbo.f_getm('2008-4-25')
--结果
/*
5 4
*/
select
sum(case month(OrderTime) when 1 then quantity else 0 end) AS 'Jan',
sum(case month(OrderTime) when 2 then quantity else 0 end) AS 'Feb',
...
sum(case month(OrderTime) when 12 then quantity else 0 end) AS 'Dec'
from
(select (case when datepart(dd,OrderTime)>25 then dateadd(dd,8,OrderTime) else OrderTime end) as OrderTime,quantity from 表) t
group by
...