ID NAME
3 方芳芳
3 凤飞飞
15 傅芬芳根据ID算出NAME占的百分比,结果也就是:
3 方芳芳 50.00%
3 凤飞飞 50.00%
15 傅芬芳 100.00%SELECT STR(COUNT(1)*100/B.Con,5,2)+'%' AS Share
FROM Tab AS a,(SELECT COUNT(1) AS Con FROM Tab) AS B
GROUP BY B.Con
我写的SQL只能算整个的百分比,不能跟ID分开。怎么写呢?
3 方芳芳
3 凤飞飞
15 傅芬芳根据ID算出NAME占的百分比,结果也就是:
3 方芳芳 50.00%
3 凤飞飞 50.00%
15 傅芬芳 100.00%SELECT STR(COUNT(1)*100/B.Con,5,2)+'%' AS Share
FROM Tab AS a,(SELECT COUNT(1) AS Con FROM Tab) AS B
GROUP BY B.Con
我写的SQL只能算整个的百分比,不能跟ID分开。怎么写呢?
FROM TAB AS A
FROM Tab AS a inner join (SELECT ID,COUNT(1) AS Con FROM Tab group by ID) AS B on a.ID=b.ID
GROUP BY B.Con
以下两个位置要改改
100.0
(SELECT ID,COUNT(1) AS Con FROM Tab group by ID)
if object_id('tb','u') is not null
drop table tb
go
create table tb
(
id int,
name varchar(10)
)
go
insert into tb
select 3,'方芳芳' union all
select 3,'凤飞飞' union all
select 15,'傅芬芳'
go
select id,name,百分比=cast((select count(1) from tb where id=a.id and name=a.name)*100/count(1) over(partition by id) as varchar)+'%' from tb a
/*
id name 百分比
----------- ---------- -------------------------------
3 方芳芳 50%
3 凤飞飞 50%
15 傅芬芳 100%(3 行受影响)*/
FROM TAB AS A