一个有关分组的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
