一个有关分组的SQL问题
表:
id       num
1           10
2           20
2           30
1           50
3           5
希望得到的结果:
id         num/numtotal
1             10/60
1             50/60
2             20/50
2             30/50
3             5/5
现在检索出每条记录的num在与它相同的id的所有num总得数的比,结果按id分组.


------解决方案--------------------

select a.id , cast(a.num as varchar) + '/ ' + cast(t.num as varchar) 'num/numtotal ' from tb a,
(select id,sum(num) num from tb group by id) t
where a.id = t.id
order by a.id
------解决方案--------------------
参考（2005）
Select *
FROM(
Select DISTINCT
id
FROM @t
)A
OUTER APPLY(
Select [values]= STUFF(REPLACE(REPLACE(
(
Select value FROM @t N
Where id = A.id
FOR XML AUTO
), ' <N value= " ', ', '), ' "/> ', ' '), 1, 1, ' ')
)N
------解决方案--------------------
declare @a varchar(100)
select @a=coalesce(@a+ '/ ', ' ')+PNAME from HPINF where perid like '207% '
select @a

------解决方案--------------------
select a.id,num/numtotal=a.num+ '/ '+b.num
from 表 a inner join (select id,num=sum(num) from 表 group by id) b on a.id.b.id
order by a.id