|
二： declare @a table(id int Primary key ,col decimal(10,2)) insert @a select 1,26.21 union all select 2,88.19 union all select 3,4.21 union all select 4,76.58 union all select 5,58.06 union all select 6,53.01 union all select 7,18.55 union all select 8,84.90 union all select 9,95.60 declare @tm table(sid int primary key,a int ,b int ,description varchar(50)) insert @tm select sid=1,a=null,b=30 ,description='<30' union all select sid=2,a=30 ,b=60 ,description='>=30 and <60' union all select sid=3,a=60 ,b=75 ,description='>=60 and <75' union all select sid=4,a=75 ,b=95 ,description='>=75 and <95' union all select sid=5,a=95 ,b=null ,description='>95' select aa.description, aa.recode_count, convert(decimal(20,2),(convert(decimal(20,2),aa.recode_count)/bb.cnt*100)) recode_percent from ( select a.description,count(*) recode_count from @tm a inner join @a b on b.col>=isnull(a.a,0) and b.col<isnull(a.b,100) group by a.description ) aa cross join ( select count(*) cnt from @a )bb 三： declare @a table(id int Primary key ,col decimal(10,2)) insert @a select 1,26.21 union all select 2,88.19 union all select 3,4.21 union all select 4,76.58 union all select 5,58.06 union all select 6,53.01 union all select 7,18.55 union all select 8,84.90 union all select 9,95.60 declare @tm table(sid int primary key,a int ,b int ,description varchar(50)) insert @tm select sid=1,a=null,b=30 ,description='<30' union all select sid=2,a=30 ,b=60 ,description='>=30 and <60' union all select sid=3,a=60 ,b=75 ,description='>=60 and <75' union all select sid=4,a=75 ,b=95 ,description='>=75 and <95' union all select sid=5,a=95 ,b=null ,description='>=95' select aa.description, recode_count =count(bb.id), [percent]=case when counts=0 then '0.00%' else cast( cast (count(bb.id)*100/counts as decimal(20,2)) as varchar)+'%' end from @tm aa left join @a bb on (bb.col<aa.b or aa.b is null)and (bb.col>=aa.a or aa.a is null) cross join (select counts=count(*) from @a)cc group by aa.description,cc.counts
四：create function sipte(@str varchar(8000) ,@spite varchar(10)) returns @temp table(fi varchar(100)) as begin declare @i int set @str=rtrim(ltrim(@str)) set @i=charindex(@spite,@str) while @i>=1 begin insert @temp values(left(@str,@i-1)) set @str=substring(@str,@i+1,len(@str)-@i) set @i=charindex(@spite,@str) end if @str<>'' insert @temp values(@str) return end select * from dbo.sipte('df,abfc,dkfd,adf',',') 一： declare @a table(year int ,quarter int , amount decimal(10,1)) insert @a select 1990,1,3.1 union all select 1990,2,3.5 union all select 1990,3,5.5 union all select 1990,4,4.5 union all select 1991,1,8.5 union all select 1991,2,6.5 union all select 1991,3,9.5 union all select 1991,4,7.5 select year ,sum(case quarter when 1 then amount else 0 end )as Q1 ,sum(case quarter when 2 then amount else 0 end )as Q2 ,sum(case quarter when 3 then amount else 0 end )as Q3 ,sum(case quarter when 4 then amount else 0 end )as Q4 from @a a group by year create function week ( @from_dt varchar(8) ,@to_dt varchar(8)) returns @wk table ( wk varchar(10) ,wk_dt varchar(20) ,R_id decimal identity ) as begin declare @week varchar(10) ,@now_dt datetime ,@wk_dt varchar(12) select @week='',@wk_dt='' select @now_dt=convert(datetime,@from_dt) select @week=convert(varchar,datepart(year,@now_dt))+'.'+convert (varchar,datepart(wk,@now_dt)) --取得开始日期的星期号 select @wk_dt=convert(varchar,datepart(month,@now_dt))+'.'+convert (varchar,datepart(day,@now_dt))+'-' --星期的开始日期 while datediff(day,@to_dt,@now_dt)<=0 begin if @week<> convert(varchar,datepart(year,@now_dt))+'.'+convert (varchar,datepart(wk,@now_dt)) begin select @wk_dt=@wk_dt+convert(varchar,datepart(month,dateadd(day,-1,@now_dt)))+'.'+ convert(varchar,datepart(day ,dateadd(day,-1,@now_dt))) insert @wk(wk_dt,wk)select @wk_dt,@week select @week=convert(varchar,datepart(year,@now_dt))+'.'+convert(varchar,datepart(wk,@now_dt)) select @wk_dt=convert(varchar,datepart(month,@now_dt))+'.'+convert(varchar,datepart(day,@now_dt))+'-' end select @now_dt=dateadd(day,1,@now_dt) end select @wk_dt=@wk_dt+convert(varchar,datepart(month,dateadd(day,-1,@now_dt)))+'.'+ convert(varchar,datepart(day ,dateadd(day,-1,@now_dt))) insert @wk(wk_dt,wk)select @wk_dt,@week return end select * from dbo.week('20100701','20100731') --期初数据 DECLARE @stock TABLE(id int,num decimal(10,2)) INSERT @stock SELECT 1,100 --入库数据 DECLARE @in TABLE(id int,num decimal(10,2)) INSERT @in SELECT 1,100 --出库数据 DECLARE @out TABLE(id int,num decimal(10,2)) INSERT @out SELECT 2,100 --统计 SELECT id, stock_opening=SUM(stock_opening), stock_in=SUM(stock_in), stock_out=SUM(stock_out), stock_closing=SUM(stock_closing) FROM( SELECT id,stock_opening=num,stock_in=0,stock_out=0,stock_closing=num FROM @stock UNION ALL SELECT id,stock_opening=0,stock_in=num,stock_out=0,stock_closing=num FROM @in UNION ALL SELECT id,stock_opening=0,stock_in=0,stock_out=num,stock_closing=-num FROM @out )a GROUP BY id /*--结果 id stock_opening stock_in stock_out stock_closing ---------------- ----------------------- ----------------- -------------------- -------------------- 1 100.00 180.00 .00 280.00 2 .00 800.00 100.00 700.00 3 500.00 .00 300.00 200.00 4 800.00 .00 .00 800.00 --*/ |
|
|
