神马文学网SQL
来源：百度文库 编辑：神马文学网 时间：2024/04/28 17:21:14
二：
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
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
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
--*/