sql中求百分比和与平均值的差

Question

这样的一个表：
id        num    date      
1          3      2011-06-01
1          5      2010-02-03
2          6      2010-12-08
5          2      2010-11-11
3          8      2010-05-25
2          2      2010-03-15我得到下面的结果：（根据id分组，百分比：（3+5）/5.  平均差：(3+5)/2-5)
id         num     date              百分比           与平均值的差
1           3      2011-06-01        37.5%                  1
1           5      2010-02-03        62.5%                 -1
2           6      2010-12-08        75%                   -2
2           2      2010-03-15         25%                   2
3           8      2010-05-25         100%                  0
5           2      2010-11-11        100%                   0我想好久，但没想出来，请大家指点。

Answer 1

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (id int,num int,date datetime)
insert into #tb
select 1,3,'2011-06-01' union all
select 1,5,'2010-02-03' union all
select 2,6,'2010-12-08' union all
select 5,2,'2010-11-11' union all
select 3,8,'2010-05-25' union all
select 2,2,'2010-03-15'select *,百分比=ltrim(cast(num*100.0/(select SUM(num) from #tb where id=t.id) as decimal(5,1)))+'%',
与平均值的差=(select AVG(num) from #tb where id=t.ID)-num
 from #tb tid          num         date                    百分比                                        与平均值的差
----------- ----------- ----------------------- ------------------------------------------ -----------
1           3           2011-06-01 00:00:00.000 37.5%                                      1
1           5           2010-02-03 00:00:00.000 62.5%                                      -1
2           6           2010-12-08 00:00:00.000 75.0%                                      -2
5           2           2010-11-11 00:00:00.000 100.0%                                     0
3           8           2010-05-25 00:00:00.000 100.0%                                     0
2           2           2010-03-15 00:00:00.000 25.0%                                      2(6 row(s) affected)

Answer 2

select a.id,a.num,a.date,ltrim(cast(a.num*1./b.cnt as decimal(12,2))) + '%' as per,(a.num - b.del) as cha
from tb a left join (select id,sum(num)cnt,(sum(num)/count(*))del from tb group by id)b on a.id = b.id

Answer 3

create table tb (id int,num int,date datetime)
insert into tb
select 1,3,'2011-06-01' union all
select 1,5,'2010-02-03' union all
select 2,6,'2010-12-08' union all
select 5,2,'2010-11-11' union all
select 3,8,'2010-05-25' union all
select 2,2,'2010-03-15'
goselect a.id,a.num,a.date,ltrim(cast(a.num*100./b.cnt as decimal(12,2))) + '%' as per,(a.num - b.del) as cha
from tb a left join (select id,sum(num)cnt,(sum(num)/count(*))del from tb group by id)b on a.id = b.iddrop table tb
/*id          num         date                    per                                        cha
----------- ----------- ----------------------- ------------------------------------------ -----------
1           3           2011-06-01 00:00:00.000 37.50%                                     -1
1           5           2010-02-03 00:00:00.000 62.50%                                     1
2           6           2010-12-08 00:00:00.000 75.00%                                     2
5           2           2010-11-11 00:00:00.000 100.00%                                    0
3           8           2010-05-25 00:00:00.000 100.00%                                    0
2           2           2010-03-15 00:00:00.000 25.00%                                     -2(6 行受影响)