如图所示,result是结果的意思,tinyint型,只有0或1两种数值[0失败,1成功],如何一次性查询出0和1各有多少,并显示比例。
查询结果显示为:
成功,失败,成功率
1900,6000,24.05%
这样显示
sql
select result,count('result') as total from table GROUP BY result
成功率前台输出的时候算一下就行了 , 没必要这么纠结用sql直接算出来吧,sql语句里面最好不使用函数
这个我会。。。。
mysql> select * from test.user_result;
+-------+------------+--------+--------+------+------------+---------------------+
| id | user | pwd | result | file | time | date |
+-------+------------+--------+--------+------+------------+---------------------+
| 11001 | 2014951024 | 951024 | 0 | NULL | 1437975507 | 2015-07-27 13:38:27 |
| 11002 | 2013642014 | 642014 | 0 | NULL | 1437975511 | 2015-07-27 13:38:31 |
| 11003 | 2013642014 | 642014 | 0 | NULL | 1437975522 | 2015-07-27 13:38:42 |
| 11004 | 2012953005 | 953005 | 0 | NULL | 1437975561 | 2015-07-27 13:39:21 |
| 11005 | 2014943019 | 943019 | 0 | NULL | 1437975572 | 2015-07-27 13:39:32 |
| 11006 | 2014943020 | 943020 | 1 | NULL | 1437975572 | 2015-07-27 13:39:32 |
+-------+------------+--------+--------+------+------------+---------------------+
mysql> SELECT t.`success` AS `成功`, t.`failed` AS `失败`,
CONCAT(FORMAT(t.`success`/t.`total`*100,2), '%') AS `成功率`
FROM (
SELECT SUM(CASE WHEN result = 1 THEN 1 ELSE 0 END) AS `success`,
SUM( CASE WHEN result = 0 THEN 1 ELSE 0 END) AS `failed`,
COUNT(1) AS `total`
FROM user_result
) t;
+------+------+--------+
| 成功 | 失败 | 成功率 |
+------+------+--------+
| 1 | 5 | 16.67% |
+------+------+--------+
1 row in set
SELECT result, SUM,total,CONCAT(SUM/total*100, '%') AS percent
FROM (
SELECT result,COUNT(result) AS SUM ,total
FROM `table` t,
(
SELECT COUNT(result) AS total FROM `table`
) a
GROUP BY result
) b
这种情况虽然可以通过SQL搞定,但是还是不要这样来搞统计,SQL看着别扭不说,效率不咋地。