表:
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分组.
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
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
insert into tb values(2, 20)
insert into tb values(2, 30)
insert into tb values(1, 50)
insert into tb values(3, 5)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.iddrop table tb/*
id num/numtotal
----------- -------------------------------------------------------------
1 10/60
1 50/60
2 30/50
2 20/50
3 5/5(所影响的行数为 5 行)
*/
select @a=coalesce(@a+'/','')+PNAME from HPINF where perid like'207%'
select @a
insert into tb values(2, 20)
insert into tb values(2, 30)
insert into tb values(1, 50)
insert into tb values(3, 5)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,'num/numtotal'drop table tb/*
id num/numtotal
----------- -------------------------------------------------------------
1 10/60
1 50/60
2 20/50
2 30/50
3 5/5(所影响的行数为 5 行)
*/
from 表 a inner join (select id,num=sum(num) from 表 group by id) b on a.id.b.id
order by a.id
(select id,totalnum = sum(num) from tb1 group by id )A left join tb1 B on A.id = B.id
order by B.id
insert into tb values(2, 20)
insert into tb values(2, 30)
insert into tb values(1, 50)
insert into tb values(3, 5)
select a.id,[num/numtotal]=rtrim(a.num)+'/'+rtrim(b.num)
from tb a inner join (select id,num=sum(num) from tb group by id) b on a.id=b.id
order by a.iddrop table tb/*
id num/numtotal
----------- -------------------------
1 10/60
1 50/60
2 30/50
2 20/50
3 5/5(5 row(s) affected)
*/