有一张表tableA,有两个属性,分别为username,status,如下:
username status
A1 1
A2 3
A1 3
A3 1
A2 3
A4 2我想求出:以username作为分组,status的值为3所占的百分比,
理想结果如下:
A1 50%
A2 100%
A3 0%
A4 0%
username status
A1 1
A2 3
A1 3
A3 1
A2 3
A4 2我想求出:以username作为分组,status的值为3所占的百分比,
理想结果如下:
A1 50%
A2 100%
A3 0%
A4 0%
+----------+--------+
| username | status |
+----------+--------+
| a1 | 1 |
| a2 | 3 |
| a2 | 5 |
+----------+--------+
3 rows in set (0.01 sec)mysql>
select username, sums/sumall
from (select username ,sum(status) as sums from table1 group by username ) tb ,
(select sum(status) as sumall from table1 ) tbs ;
+----------+-------------+
| username | sums/sumall |
+----------+-------------+
| a1 | 0.1111 |
| a2 | 0.8889 |
+----------+-------------+
2 rows in set (0.00 sec)
select 'A1',1 union all
select 'A2',3 union all
select 'A1',3 union all
select 'A3',1 union all
select 'A2',3 union all
select 'A4',2select
username,
cast(cast((select count(*) from #t where username=A.username and status=3 ) *1.0
/(select count(*) from #t where username=A.username) as decimal(10,1)) * 100 as varchar(10)) + '%' as 占比
from #t as A
group by usernamedrop table #t
from tableA,(select username,count(status) s from tableA where status = 3 group by username) t
where tableA.username = t.username group by num;