可用分押宝游戏火热进行中...	专题改版：Java Web 专题

sql统计比较有挑战性！

楼主anticlimax（）2006-03-01 11:41:09 在 MS-SQL Server / 基础类提问

表a
日期工资额
199601 250
199701 250
199801 300
199805 350
199901 400
199910 450
200205 600
200308 700
200401 900
200510 1200
现在要统计年平均工资，有个问题是某些年份数据缺失，如2000年就没有记录，必须用上一年最后一个月工资代替，如上表中需要用199910的工资450来代替2000的工资
select adv(工资额) as 平均工资,lef(日期,4) group by lef(日期,4)，是不行了，哪位有好办法问题点数：20、回复次数：10Top

1 楼Jane_64（）回复于 2006-03-01 14:11:55 得分 10

2 楼anticlimax（）回复于 2006-03-01 14:26:56 得分 0

还有要求，比如上表数据
199801 300 （从后面数据推出发了4个月）
199805 350
98年平均工资应该是（300*4+350*8）/12
Top

3 楼Jane_64（）回复于 2006-03-01 14:53:08 得分 0

还有什么？一次说完！！！！Top

4 楼anticlimax（）回复于 2006-03-01 15:21:49 得分 0

感谢Jane_64() ，没有了，Top

5 楼Jane_64（）回复于 2006-03-01 16:10:37 得分 0

declare @tb table(日期 char(6),工资额 money)
insert @tb
select '199601',250 union
select '199701',250 union
select '199801',300 union
select '199805',350 union
select '199901',400 union
select '199908',420 union
select '199910',450 union
select '200205',600 union
select '200308',700 union
select '200401',900 union
select '200510',1200

select left(日期,4) 年份,avg(工资额) as 平均工资
into #tmp
from @tb
group by left(日期,4)

/*more the one a year*/
select b.*,identity(int,1,1) id
into #more
from (select left(日期,4) 年份 from @tb group by left(日期,4) having count(left(日期,4))>1) a,@tb b
where a.年份=left(b.日期,4)

update #tmp
set 平均工资 = c.平均工资
from (select left(a.日期,4) 年份,
round(sum(a.工资额*(case when b.日期 is not null then right(b.日期,2) else 13 end -right(a.日期,2)))/12,2) 平均工资
from #more a left join #more b
on left(a.日期,4)=left(b.日期,4) and a.id+1=b.id
group by left(a.日期,4)) c
where #tmp.年份=c.年份

/*the year not exist */
declare @begyear char(4)
declare @endyear char(4)

select @begyear=min(年份) from #tmp
select @endyear=max(年份) from #tmp

while (@begyear<@endyear)
begin
select @begyear = convert(char(4),convert(int,@begyear) +1)
if (select count(*) from #tmp where 年份=@begyear)=0
insert #tmp
select @begyear,b.工资额
from (select max(日期) 日期 from @tb where left(日期,4)<@begyear) a,@tb b
where a.日期=b.日期
end

select * from #tmp order by 年份

drop table #tmp
drop table #more
Top

6 楼Jane_64（）回复于 2006-03-01 16:52:53 得分 0

对不起，没有考虑1月份没有的Top

7 楼fengyaner（风颜儿）回复于 2006-03-01 17:09:04 得分 10

declare @MaxYear int,@MinYear int

Select @MaxYear=max(cast(left(riqi,4) as int)) from csdn

Select @MinYear=min(cast(left(riqi,4) as int)) from csdn

--select @MaxYear,@MinYear

declare @Month char(2),@gongzi money,@AllMoney money,@TempMonth int,@TempYear int

set @AllMoney=0
set @TempMonth=1

declare @x money
set @x=20

declare @loop int

set @loop=@MinYear
set @TempYear=@MinYear

while @loop<=@MaxYear
begin
DECLARE authors_cursor CURSOR FOR
SELECT right(riqi,2), gongzi FROM csdn WHERE cast(left(riqi,2) as int)=@loop

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @Month, @gongzi
WHILE @@FETCH_STATUS = 0
BEGIN

-- Concatenate and display the current values in the variables.

set @AllMoney=@AllMoney+@gongzi*(cast(@Month as int)-@TempMonth)
set @TempMonth=@Month

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @Month, @gongzi
END

CLOSE authors_cursor
DEALLOCATE authors_cursor

if (@AllMoney=0)
begin
set @AllMoney=12*@gongzi
end
else
if (@TempMonth<>12)
begin
set @AllMoney=@AllMoney+(12-@TempMonth)*@gongzi
end

insert into csdn values(@loop,@AllMoney)
Set @AllMoney=0
Set @TempMonth=1
Set @loop=@loop+1

endTop

8 楼Jane_64（）回复于 2006-03-01 17:34:24 得分 0

declare @tb table(日期 char(6),工资额 money)
insert @tb
select '199601',250 union
select '199701',250 union
select '199801',300 union
select '199805',350 union
select '199901',400 union
select '199908',420 union
select '199910',450 union
select '200205',600 union
select '200308',700 union
select '200401',900 union
select '200510',1200

select b.*,identity(int,1,1) id
into #tmp
from @tb b

select min(日期) 日期
into #noone
from #tmp where left(日期,4) not in (select left(日期,4) from #tmp where right(日期,2)='01')
group by left(日期,4)

select left(a.日期,4) 年份,
a.工资额*(case when b.日期 is not null then right(b.日期,2) else 13 end -right(a.日期,2)) 工资额
into #tmp1
from #tmp a left join #tmp b
on left(a.日期,4)=left(b.日期,4) and a.id+1=b.id
union
select c.年份,d.工资额*(right(e.日期,2)-1)
from (select max(b.日期) 日期,left(a.日期,4) 年份
from #noone a, #tmp b
where a.日期>b.日期
group by left(a.日期,4)) c,#tmp d,#noone e
where c.日期=d.日期 and left(e.日期,4)=c.年份

select 年份,round(sum(工资额)/12,2) 工资额
into #tmp2
from #tmp1
group by 年份

/*the year not exist */
declare @begyear char(4)
declare @endyear char(4)

select @begyear=min(年份) from #tmp2
select @endyear=max(年份) from #tmp2

while (@begyear<@endyear)
begin
select @begyear = convert(char(4),convert(int,@begyear) +1)
if (select count(*) from #tmp2 where 年份=@begyear)=0
insert #tmp2
select @begyear,b.工资额
from (select max(日期) 日期 from #tmp where left(日期,4)<@begyear) a,#tmp b
where a.日期=b.日期
end

select * from #tmp2 order by 年份
Top

9 楼anticlimax（）回复于 2006-03-01 19:50:52 得分 0

老大们都很强，我自己写了个，创建了个表b，里面写上年
年
1996
1997
...
2005
插入临时表 c
select a.日期,a.工资,(select case when left(a.日期,4) in(select left(b.日期,4) from a b where b.日期<>a.日期 ) then (case when a.日期 in(select max(b.日期) from a b where left(a.日期,4)=left(b.日期,4) ) then (12-CAST(SUBSTRING(日期, 5, 2) AS int)) else (convert(decimal(12,2),((select top 1 substring(b.日期,5,2) from a b where b.日期>a.日期 and left(b.日期,4)= left(a.日期,4) order by b.日期 asc))-convert(decimal(12,2),substring(a.日期,5,2)))) end) else 12 end ) as 月 into c from a where order
by a.日期

'得到结果
select 年,[总]=case when 年 in(select left(c.日期,4) from c) then (select sum(c.工资*c.月)/12 from c where b.年=left(c.日期,4)) else (select top 1 c.工资 from c where 年>left(c.日期,4) order by c.日期 desc ) end from b

Top

10 楼ReViSion（和尚）回复于 2006-03-01 20:36:21 得分 0

还不如这样，把每年的每个人做为一条记录，
第一条就表中年份最低的一条记录，
然后用Update来更新记录，

最后SUM（工资)/12 group by 年份
Top