mysql查询问题sql语句_MySQL 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