mysql 查询案例(1)

-- 建表

CREATE TABLE `t_award_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ids',
  `u_name` varchar(30) DEFAULT NULL COMMENT '获奖者名称',
  `award_level` varchar(1) DEFAULT NULL COMMENT '获奖等级：1 金牌 2银牌 3铜牌',
  `award_time` datetime DEFAULT NULL COMMENT '获奖时间',
  `item` varchar(20) DEFAULT NULL COMMENT '参赛项目',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

-- 插入数据

INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (1, '张三', '1', '2023-03-13 09:26:14', '铅球');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (2, '张三', '1', '2023-03-07 09:26:52', '百米跑步');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (3, '张三', '3', '2023-03-22 09:27:27', '乒乓');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (4, '李四', '2', '2023-03-14 09:27:51', '体操');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (5, '李四', '3', '2023-03-21 09:28:10', '溜冰');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (6, '李四', '2', '2023-03-26 09:28:37', '羽毛球');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (7, '王五', '2', '2023-03-27 09:55:44', '标枪');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (8, '王五', '1', '2023-03-20 09:56:19', '体操');
INSERT INTO `garbage_sort`.`t_award_record`(`id`, `u_name`, `award_level`, `award_time`, `item`) VALUES (9, '刘六', '3', '2023-03-26 09:56:39', '百米赛跑');

-- 1.查询出每个人 获奖总次数 和 其获得的各类奖牌的数量

SELECT 
u_name,
count(*) as "总次数",
SUM(CASE award_level WHEN 1 THEN 1 ELSE 0 END ) as "JPCount",
SUM(CASE award_level WHEN 2 THEN 1 ELSE 0 END ) as "YPCount",
SUM(CASE award_level WHEN 3 THEN 1 ELSE 0 END ) as "TPCount"
FROM t_award_record
where 1 = 1
GROUP BY u_name

-- 2.查询每个等级(每种奖牌)的数量

select 
(case award_level when 1 then '金牌' when 2 then '银牌' when 3 then '铜牌' else '其他' end) as '奖牌类型',
count(*) as "数量"
from t_award_record
group by award_level

-- 3. 查询每个人的总奖牌数，获取前三名

select
u_name,
count(*) as "num"
from t_award_record
group by u_name
order by num desc
limit 3