MySql调查清晰查询
MySql调查清晰查询
提问于 2015-10-09 16:14:33
嘿，伙计们，我真的很纠结于一个MySql查询，我有一个名为'info‘的表，其中我有一个名为’打分‘的专栏，我的评分在1-10之间。
现在我需要生成一个百分比值，从1-6，7-8，9-10得到多少评级，但我需要它们拼命地显示，然后我需要第二个查询，它可以减去1-6和9-10的结果的百分比值。
下面的查询是我从所有的研究中所能得到的，但是我不知道如何获得1-6的百分比，而不是所有的评级，也不知道如何获得第二个查询来减去1-6和9-10的评级百分比。
SELECT rating,
COUNT(*) AS Count,
(COUNT(*) / _total ) * 100 AS Percentege
FROM info,
(SELECT COUNT(*) AS _total FROM info) AS myTotal
GROUP BY rating回答 2
Stack Overflow用户
回答已采纳
发布于 2015-10-09 16:44:38
select if(rating between 1 and 6, '1-6',
if( rating between 7 and 8, '7-8',
'9-10' )
) as rating_range,
count(1) as num
from info
group by rating_range工作小提琴
编辑:添加舍入和计算，这可以用作子查询。根据小组的不同，您需要分别得到总金额：
select Q.rating_range,
Q.num,
round(Q.num * 100 / Q.total, 2) as percent
from (
select R.*,
(select count(1) from info) as total
from (
select if(rating between 1 and 6, '1-6',
if( rating between 7 and 8, '7-8',
'9-10' )
) as rating_range,
count(1) as num
from info
group by rating_range ) R
) Q
group by Q.rating_range就相对值而言，如果我有一个外部应用程序，我可能会这样做。否则，您可以执行特定的查询，我想：
select Q.rating_range,
Q.num,
round(Q.num * 100 / Q.total, 2) as percent,
round( (Q.num - Q.total_nine_ten) * 100 / Q.total, 2) as diff_from_nine_ten
from (
select R.*,
(select count(1) from info) as total,
(select count(1) from info where rating > 8 ) as total_nine_ten
from (
select if(rating between 1 and 6, '1-6',
if( rating between 7 and 8, '7-8',
'9-10' )
) as rating_range,
count(1) as num
from info
group by rating_range ) R
) Q
group by Q.rating_range以上版本的小提琴
不太优雅，但很有效
Stack Overflow用户
发布于 2015-10-09 16:31:31
我不喜欢这个想法，但如果你需要的话，你可以：
http://sqlfiddle.com/#!9/bd1c5/1
SELECT rating,
COUNT(*) AS Count,
(COUNT(*) / COALESCE ((SELECT COUNT(*) AS _total FROM info),1) ) * 100 AS Percentege
FROM info
GROUP BY rating或者如果我们确定该表不是空的：
SELECT rating,
COUNT(*) AS Count,
(COUNT(*) / (SELECT COUNT(*) FROM info) ) * 100 AS Percentege
FROM info
GROUP BY rating更新更奇怪，但请求的结果：
http://sqlfiddle.com/#!9/4b6bf/4
SELECT
IF(rating>=0 AND rating<=6, '1-6',
IF(rating<=8,'7-8',
IF(rating<=10,'9-10','UNKNOWN')
)
) as pseudo_rating,
COUNT(*) AS Count,
(COUNT(*) / (SELECT COUNT(*) FROM info) ) * 100 AS Percentege
FROM info
GROUP BY pseudo_rating更新 第()轮
http://sqlfiddle.com/#!9/4b6bf/6
SELECT
IF(rating>=0 AND rating<=6, '1-6',
IF(rating<=8,'7-8',
IF(rating<=10,'9-10','UNKNOWN')
)
) as pseudo_rating,
COUNT(*) AS Count,
ROUND((COUNT(*) / (SELECT COUNT(*) FROM info) ) * 100, 2) AS Percentege
FROM info
GROUP BY pseudo_rating页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33042884
复制相关文章
相似问题
腾讯云开发者
