MySql查询不同状态所占比率

来源：互联网发布：黑马人工智能视频教程编辑：程序博客网时间：2024/04/29 19:21

原型：查询数据库中不同类型的激活码领取比率

建表：

DROP TABLE IF EXISTS `game_gift`;CREATE TABLE `game_gift` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `code` varchar(64) NOT NULL,  `receive_time` datetime DEFAULT NULL,  `status` int(1) NOT NULL DEFAULT '0' COMMENT '是否领取（0：否，1：是）',  `type` int(1) NOT NULL DEFAULT '1' COMMENT '激活码类型',  `account_id` varchar(50) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1601 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `game_gift` VALUES ('11', 'XXXXXXXXXXXXXXXXX', null, '1', '1', null);INSERT INTO `game_gift` VALUES ('12', 'XXXXXXXXXXXXXXXXX', null, '1', '1', null);INSERT INTO `game_gift` VALUES ('13', 'XXXXXXXXXXXXXXXXX', null, '1', '1', null);INSERT INTO `game_gift` VALUES ('14', 'XXXXXXXXXXXXXXXXX', null, '1', '1', null);INSERT INTO `game_gift` VALUES ('15', 'XXXXXXXXXXXXXXXXX', null, '0', '1', null);INSERT INTO `game_gift` VALUES ('16', 'XXXXXXXXXXXXXXXXX', null, '0', '1', null);INSERT INTO `game_gift` VALUES ('17', 'XXXXXXXXXXXXXXXXX', null, '0', '1', null);INSERT INTO `game_gift` VALUES ('18', 'XXXXXXXXXXXXXXXXX', null, '0', '1', null);INSERT INTO `game_gift` VALUES ('19', 'XXXXXXXXXXXXXXXXX', null, '0', '1', null);INSERT INTO `game_gift` VALUES ('20', 'XXXXXXXXXXXXXXXXX', null, '0', '1', null);INSERT INTO `game_gift` VALUES ('21', 'XXXXXXXXXXXXXXXXX', null, '1', '2', null);INSERT INTO `game_gift` VALUES ('22', 'XXXXXXXXXXXXXXXXX', null, '1', '2', null);INSERT INTO `game_gift` VALUES ('23', 'XXXXXXXXXXXXXXXXX', null, '1', '2', null);INSERT INTO `game_gift` VALUES ('24', 'XXXXXXXXXXXXXXXXX', null, '1', '2', null);INSERT INTO `game_gift` VALUES ('25', 'XXXXXXXXXXXXXXXXX', null, '1', '2', null);INSERT INTO `game_gift` VALUES ('26', 'XXXXXXXXXXXXXXXXX', null, '1', '2', null);INSERT INTO `game_gift` VALUES ('27', 'XXXXXXXXXXXXXXXXX', null, '1', '2', null);INSERT INTO `game_gift` VALUES ('28', 'XXXXXXXXXXXXXXXXX', null, '0', '2', null);INSERT INTO `game_gift` VALUES ('29', 'XXXXXXXXXXXXXXXXX', null, '0', '2', null);INSERT INTO `game_gift` VALUES ('30', 'XXXXXXXXXXXXXXXXX', null, '0', '2', null);INSERT INTO `game_gift` VALUES ('31', 'XXXXXXXXXXXXXXXXX', null, '0', '2', null);INSERT INTO `game_gift` VALUES ('32', 'XXXXXXXXXXXXXXXXX', null, '0', '2', null);INSERT INTO `game_gift` VALUES ('33', 'XXXXXXXXXXXXXXXXX', null, '0', '2', null);INSERT INTO `game_gift` VALUES ('34', 'XXXXXXXXXXXXXXXXX', null, '0', '2', null);INSERT INTO `game_gift` VALUES ('35', 'XXXXXXXXXXXXXXXXX', null, '1', '3', null);INSERT INTO `game_gift` VALUES ('36', 'XXXXXXXXXXXXXXXXX', null, '1', '3', null);INSERT INTO `game_gift` VALUES ('37', 'XXXXXXXXXXXXXXXXX', null, '0', '3', null);INSERT INTO `game_gift` VALUES ('38', 'XXXXXXXXXXXXXXXXX', null, '0', '3', null);INSERT INTO `game_gift` VALUES ('39', 'XXXXXXXXXXXXXXXXX', null, '0', '3', null);INSERT INTO `game_gift` VALUES ('40', 'XXXXXXXXXXXXXXXXX', null, '0', '3', null);INSERT INTO `game_gift` VALUES ('41', 'XXXXXXXXXXXXXXXXX', null, '0', '3', null);INSERT INTO `game_gift` VALUES ('43', 'XXXXXXXXXXXXXXXXX', null, '1', '4', null);INSERT INTO `game_gift` VALUES ('44', 'XXXXXXXXXXXXXXXXX', null, '0', '4', null);INSERT INTO `game_gift` VALUES ('45', 'XXXXXXXXXXXXXXXXX', null, '0', '4', null);INSERT INTO `game_gift` VALUES ('46', 'XXXXXXXXXXXXXXXXX', null, '0', '4', null);INSERT INTO `game_gift` VALUES ('47', 'XXXXXXXXXXXXXXXXX', null, '0', '4', null);INSERT INTO `game_gift` VALUES ('48', 'XXXXXXXXXXXXXXXXX', null, '0', '4', null);INSERT INTO `game_gift` VALUES ('49', 'XXXXXXXXXXXXXXXXX', null, '0', '4', null);INSERT INTO `game_gift` VALUES ('50', 'XXXXXXXXXXXXXXXXX', null, '0', '4', null);

需要查询的结果：

查询SQL语句：

SELECT  gf.type AS '类型编号',  CASE typeWHEN 1 THEN '激活码'  WHEN 2 THEN '高级签到新兵礼包'  WHEN 3 THEN '豪华签到新兵礼包'  ELSE '至尊签到新兵礼包'  END AS '类型名称',  COUNT(0) AS '总数',  SUM(IF(gf.`status` = 1, 1, 0)) AS '已领取', SUM(IF(gf.`status` = 0, 1, 0)) AS '未领取',  CONCAT(FORMAT(SUM(IF(gf.`status` = 1, 1, 0))/ COUNT(0)*100, 2), '%') AS "领取比例"  FROMgame_gift gf  GROUP BYgf.type;

MySql常用函数介绍：

(a) CASE WHEN THEN函数
语法：
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ……] [ELSE result ] END
CASE WHEN [condition] THEN result [WHEN[condition] THEN result ……] [ELSE result] END
函数用法说明：在第一个方案的返回结果中， value =compare-value 。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回
结果为 ELSE 后的结果，如果没有 ELSE 部分，则返回值为 NULL

(b) IF函数
语法：
IF(expr1,expr2,expr3)
函数用法说明：如果 expr1 是 TRUE (expr1 <> 0 and expr1 <> NULL) ，则 IF() 的返回值为 expr2 ; 否则返回值则为 expr3 。 IF() 的返回值为数字值或字符串
值，具体情况视其所在语境而定