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

Question

问题补充
大概就是这种数据 , 麻烦大家看一下 谢谢大佬们

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');

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

Answer 1

SELECT * FROM (
SELECT COUNT(IF(value=1,1,NULL))/COUNT(type) AS pre,type FROM game_table group by type
) AS temp order by pre DESC limit 10