mysql - 从 MySQL 获取多列百分比

Question

我有一个像这样的表:

schoolId | string1 | string2
   1     |  yes    | no
   1     |  no     | yes
   1     |  null   | yes
   1     |  yes    | no

我正在尝试获取如下结果表:

value | string1 | string2
yes   |   50%   |  50%
no    |   25%   |  50%
null  |   25%   |  0%

到目前为止我已经将其放在一起:

select (table.string1/string1_sum)*100 as 'string1 %'
from table , (SELECT SUM(string1) string1_sum FROM table) as t
where schoolId = 4

Answer 1

以下查询将为您提供 string1 和 string2 中每个值的计数和总计:

SELECT a.value, 
(SELECT count(*) FROM school WHERE ifnull(string1, 'null') = a.value AND schoolid = 1) AS s1,
(SELECT count(*) FROM school WHERE ifnull(string2, 'null') = a.value AND schoolid = 1) AS s2,
(SELECT count(*) FROM school WHERE schoolid = 1) AS total
FROM
(SELECT DISTINCT IFNULL(string1, 'null') AS value FROM school WHERE schoolid = 1
UNION
SELECT DISTINCT IFNULL(string2, 'null') AS value FROM school WHERE schoolid = 1) a

根据这些值，您可以计算应用程序中的百分比。

这是SQL Fiddle 。

更新

要计算查询本身的计数，您可以将上述查询包装到另一个查询中并执行除法:

SELECT b.value, (b.s1/b.total)*100 AS string1, (b.s2/b.total)*100 AS string2
FROM (
SELECT a.value, 
(SELECT count(*) FROM school WHERE ifnull(string1, 'null') = a.value AND schoolid = 1) AS s1,
(SELECT count(*) FROM school WHERE ifnull(string2, 'null') = a.value AND schoolid = 1) AS s2,
(SELECT count(*) FROM school WHERE schoolid = 1) AS total
FROM
(SELECT DISTINCT IFNULL(string1, 'null') AS value FROM school WHERE schoolid = 1
UNION
SELECT DISTINCT IFNULL(string2, 'null') AS value FROM school WHERE schoolid = 1) a
) b

这是更新后的SQL Fiddle 。