mysql中查询 , 满足条件的分类 , 占总分类的百分比前十的数据

[烛未]

想实现的效果 这个是简化的数据库 , 需要查出装备名称 , 以及value为1的装备占该装备和的百分比 , 并只需要排名靠前的十条 , 弄了一天了没搞出来 , 真的好急 谢谢大佬帮忙

type proportion
幻影神斧 80
丈八蛇矛 75
方天画戟 71
海神剑 67
月下美人 67
亮银枪 50
... ...

前十条数据就可以了 谢谢大佬

/*
Navicat MySQL Data Transfer

Source Server         : 127.0.0.1
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : alarm_tabel

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2019-06-17 16:12:19
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for game_table
-- ----------------------------
DROP TABLE IF EXISTS `game_table`;
CREATE TABLE `game_table` (
  `type` varchar(255) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of game_table
-- ----------------------------
INSERT INTO `game_table` VALUES ('丈八蛇矛', '1');
INSERT INTO `game_table` VALUES ('丈八蛇矛', '1');
INSERT INTO `game_table` VALUES ('丈八蛇矛', '1');
INSERT INTO `game_table` VALUES ('丈八蛇矛', '1');
INSERT INTO `game_table` VALUES ('丈八蛇矛', '1');
INSERT INTO `game_table` VALUES ('丈八蛇矛', '1');
INSERT INTO `game_table` VALUES ('丈八蛇矛', '0');
INSERT INTO `game_table` VALUES ('丈八蛇矛', '0');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '0');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '0');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '0');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '0');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '1');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '1');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '1');
INSERT INTO `game_table` VALUES ('青龙偃月刀', '1');
INSERT INTO `game_table` VALUES ('双股剑', '1');
INSERT INTO `game_table` VALUES ('双股剑', '1');
INSERT INTO `game_table` VALUES ('双股剑', '0');
INSERT INTO `game_table` VALUES ('双股剑', '0');
INSERT INTO `game_table` VALUES ('双股剑', '0');
INSERT INTO `game_table` VALUES ('双股剑', '0');
INSERT INTO `game_table` VALUES ('方天画戟', '0');
INSERT INTO `game_table` VALUES ('方天画戟', '0');
INSERT INTO `game_table` VALUES ('方天画戟', '0');
INSERT INTO `game_table` VALUES ('方天画戟', '1');
INSERT INTO `game_table` VALUES ('方天画戟', '1');
INSERT INTO `game_table` VALUES ('方天画戟', '1');
INSERT INTO `game_table` VALUES ('方天画戟', '1');
INSERT INTO `game_table` VALUES ('青钢剑', '1');
INSERT INTO `game_table` VALUES ('青钢剑', '1');
INSERT INTO `game_table` VALUES ('青钢剑', '0');
INSERT INTO `game_table` VALUES ('青钢剑', '0');
INSERT INTO `game_table` VALUES ('青钢剑', '0');
INSERT INTO `game_table` VALUES ('青钢剑', '0');
INSERT INTO `game_table` VALUES ('青钢剑', '0');
INSERT INTO `game_table` VALUES ('幻影神斧', '0');
INSERT INTO `game_table` VALUES ('幻影神斧', '1');
INSERT INTO `game_table` VALUES ('幻影神斧', '1');
INSERT INTO `game_table` VALUES ('幻影神斧', '1');
INSERT INTO `game_table` VALUES ('幻影神斧', '1');
INSERT INTO `game_table` VALUES ('月下美人', '0');
INSERT INTO `game_table` VALUES ('月下美人', '0');
INSERT INTO `game_table` VALUES ('月下美人', '1');
INSERT INTO `game_table` VALUES ('月下美人', '1');
INSERT INTO `game_table` VALUES ('月下美人', '1');
INSERT INTO `game_table` VALUES ('月下美人', '1');
INSERT INTO `game_table` VALUES ('海神剑', '1');
INSERT INTO `game_table` VALUES ('海神剑', '1');
INSERT INTO `game_table` VALUES ('海神剑', '1');
INSERT INTO `game_table` VALUES ('海神剑', '0');
INSERT INTO `game_table` VALUES ('海神剑', '0');
INSERT INTO `game_table` VALUES ('海神剑', '0');
INSERT INTO `game_table` VALUES ('白银枪', '0');
INSERT INTO `game_table` VALUES ('白银枪', '0');
INSERT INTO `game_table` VALUES ('白银枪', '0');
INSERT INTO `game_table` VALUES ('白银枪', '0');
INSERT INTO `game_table` VALUES ('白银枪', '1');
INSERT INTO `game_table` VALUES ('白银枪', '1');
INSERT INTO `game_table` VALUES ('白银枪', '1');
INSERT INTO `game_table` VALUES ('亮银枪', '0');
INSERT INTO `game_table` VALUES ('亮银枪', '0');
INSERT INTO `game_table` VALUES ('亮银枪', '0');
INSERT INTO `game_table` VALUES ('亮银枪', '0');
INSERT INTO `game_table` VALUES ('亮银枪', '1');
INSERT INTO `game_table` VALUES ('亮银枪', '1');
INSERT INTO `game_table` VALUES ('亮银枪', '1');
INSERT INTO `game_table` VALUES ('亮银枪', '1');
INSERT INTO `game_table` VALUES ('毒棘头', '1');
INSERT INTO `game_table` VALUES ('毒棘头', '1');
INSERT INTO `game_table` VALUES ('毒棘头', '1');
INSERT INTO `game_table` VALUES ('毒棘头', '1');
INSERT INTO `game_table` VALUES ('毒棘头', '0');
INSERT INTO `game_table` VALUES ('毒棘头', '0');
INSERT INTO `game_table` VALUES ('毒棘头', '0');
INSERT INTO `game_table` VALUES ('毒棘头', '0');

[烛未]

@zoyation

[ZJHZ_叶]

select type,sum(case value when 1 then 1 else 0 end) /count(*)*100
from game_table
group by type
order by 2 desc limit 10

[过眼浮云866]

楼上的做法能达到效果，只是效率太低了；
这样写效率高一点，当然，对于楼主这点数据量来说，能出来结果就行
SELECT a.type,ROUND(a.num / b.num*100,2) occ FROM
(SELECT TYPE,COUNT(1) num FROM game_table WHERE VALUE = 1 GROUP BY TYPE) a
JOIN (SELECT TYPE,COUNT(1) num FROM game_table GROUP BY TYPE) b
WHERE a.type = b.type ORDER BY occ DESC LIMIT 0,10;

[AHUA1001]

引用 2 楼 ZJHZ_叶 的回复:

select type,sum(case value when 1 then 1 else 0 end) /count(*)*100 from game_table group by type order by 2 desc limit 10

我还是觉得查询，效率更高。