MySQL SQL查询语句问题

Question

如图所示,result是结果的意思，tinyint型,只有0或1两种数值[0失败,1成功],如何一次性查询出0和1各有多少,并显示比例。

查询结果显示为:

成功,失败,成功率
1900,6000，24.05%

这样显示

Answer 1

这个我会。。。。

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

Answer 2

sqlselect result,count('result') as total from table GROUP BY result

成功率前台输出的时候算一下就行了 ， 没必要这么纠结用sql直接算出来吧，sql语句里面最好不使用函数

Answer 3

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看着别扭不说，效率不咋地。