MySQL 不带回数据

Question

我有以下代码:

SELECT q1, COUNT(q1) as Responses, 
(SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1) AS Total, 
(COUNT(q1) / (SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1 )) *100 AS Percentage
FROM `acme` WHERE q1  >= 1 GROUP BY q1 DESC

很简单，q1 字段中只能有四个响应 - 它们是 1、2、3 或 4

查看下面的输出:

q1  Responses   Total   percentage
4   109         362     30.1105
3   224         362     61.8785
2   25          362     6.9061
1   4           362     1.1050

现在，如果选项 1、2、3 或 4 在 q1 中，这会起作用 但是，我遇到的问题是如果 Q1 中没有 4 -该代码将返回 3,2 和 1 而不是 4 的行(我猜这实际上是正确的)。

所以，如果没有 4，我需要它来产生这样的东西......

q1  Responses   Total   percentage
4   0           253     0
3   224         253     88.53754
2   25          253     9.88142
1   4           253     1.58102

希望这是有道理的。

Answer 1

试试这个:

select * from (SELECT q1, COUNT(q1) as Responses, 
(SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1) AS Total, 
(COUNT(q1) / (SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1 )) *100 AS Percentage
FROM `acme` WHERE q1  >= 1 GROUP BY q1 DESC
union
select 1,0,0,0 from dual
union
select 2,0,0,0 from dual
union
select 3,0,0,0 from dual
union
select 4,0,0,0 from dual) as t group by q1

http://www.sqlfiddle.com/#!2/84b31/6

或者你可以不使用 DUAL

试试

select * from (SELECT q1, COUNT(q1) as Responses, 
(SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1) AS Total, 
(COUNT(q1) / (SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1 )) *100 AS Percentage
FROM `acme` WHERE q1  >= 1 GROUP BY q1 DESC
union
select 1,0,0,0
union
select 2,0,0,0
union
select 3,0,0,0
union
select 4,0,0,0) as t group by q1;

http://www.sqlfiddle.com/#!2/97031/19