求教 ： 关于下面的 SQL 语句应该怎么写的?
本帖最后由 jiangling_0103 于 2016-06-15 12:02 编辑我使用下面的语句可以查询出某个项目中Bug 对应的状态的数量，
select bug_status Name , COUNT(*) Count from bugs INNER JOIN products p ON bugs.product_id=p.id WHERE p.name = 'SC7702' GROUP BY bug_status WITH ROLLUP;
如果我想再加一列 Percentage 对应各个状态的Bug占总数的百分比的,如下图的，SQL语句应该怎么写合适的？求教啊～
回复 1# jiangling_0103
这个情况我是先算一个所有的 COUNT(*) 赋值给临时变量，然后再计算。分两条 select 操作。 存储过程可以的 select bug_status Name , COUNT(*) Count,Count(*)/(select count(*) from bugs INNER JOIN products p ON bugs.product_id=p.id WHERE p.name = 'SC7702' GROUP BY bug_status WITH ROLLUP) as per from bugs INNER JOIN products p ON bugs.product_id=p.id WHERE p.name = 'SC7702' GROUP BY bug_status WITH ROLLUP;
大概可以这样，效率可能低点，具体debug一下 mysql> select * from tb1;
+----+-------+
| id | name|
+----+-------+
|1 | aaaaa |
|2 | aaabb |
|3 | aabbc |
|4 | bbbbb |
|5 | ddddd |
+----+-------+
5 rows in set
mysql> select name, count(id), count(id) / (select count(*) as total from tb1) from tb1 as t group by name;
+-------+-----------+-------------------------------------------------+
| name| count(id) | count(id) / (select count(*) as total from tb1) |
+-------+-----------+-------------------------------------------------+
| aaaaa | 1 | 0.2000 |
| aaabb | 1 | 0.2000 |
| aabbc | 1 | 0.2000 |
| bbbbb | 1 | 0.2000 |
| ddddd | 1 | 0.2000 |
+-------+-----------+-------------------------------------------------+
5 rows in set
mysql> select name, count(id), count(id) / total from tb1, (select count(*) as total from tb1) as t group by name;
+-------+-----------+-------------------+
| name| count(id) | count(id) / total |
+-------+-----------+-------------------+
| aaaaa | 1 | 0.2000 |
| aaabb | 1 | 0.2000 |
| aabbc | 1 | 0.2000 |
| bbbbb | 1 | 0.2000 |
| ddddd | 1 | 0.2000 |
+-------+-----------+-------------------+
5 rows in set
页:
[1]