请教一下MySQL计算百分比的问题
请教一下大家，目前遇到个问题不知如何处理，问题描述如下：假设有一个表，有四列,前两列mm mn为varchar，后两列为int：
#mm mn value count
aaabbb1 5
aaabbb2 3
aaabbb3 2
cccddd1 2
cccddd2 4
cccddd3 4
这时想要加一列，比如叫persent，就是计算相同mm,mn，但是value不同的，count次数的百分比，SQL语句应该如何写？
就是得到类似如下的表,或者persent用小数表示也行：
#mm mn value countpersent
aaabbb1 5 50%
aaabbb2 3 30%
aaabbb3 2 20%
cccddd1 2 20%
cccddd2 4 40%
cccddd3 4 40%
感谢大家！ 这只是select语句，有需要加上create table就可以了：mysql> select * from test;
+-----+-----+-------+-------+
| mm| mn| value | count |
+-----+-----+-------+-------+
| aaa | bbb | 1 | 5 |
| aaa | bbb | 2 | 3 |
| aaa | bbb | 3 | 2 |
| ccc | ddd | 1 | 2 |
| ccc | ddd | 2 | 4 |
| ccc | ddd | 3 | 4 |
+-----+-----+-------+-------+
6 rows in set
mysql> select test.*, concat(format(count / s * 100, 2), "%") as percent
from test
left join
(
select mm, mn, sum(count) as s
from test
group by mm, mn
) as tmp
on test.mm = tmp.mm and test.mn = tmp.mn;
+-----+-----+-------+-------+---------+
| mm| mn| value | count | percent |
+-----+-----+-------+-------+---------+
| aaa | bbb | 1 | 5 | 50.00%|
| aaa | bbb | 2 | 3 | 30.00%|
| aaa | bbb | 3 | 2 | 20.00%|
| ccc | ddd | 1 | 2 | 20.00%|
| ccc | ddd | 2 | 4 | 40.00%|
| ccc | ddd | 3 | 4 | 40.00%|
+-----+-----+-------+-------+---------+
6 rows in set 感谢大神，经测试可用，谢谢！回复 2# seesea2517
回复 3# bingdele
加油，这个不厉害啦。
页:
[1]