MYSQL部分面试题型

高二不是病

已于 2022-09-21 20:00:54 修改

阅读量807

点赞数

文章标签： mysql 数据库 sql

于 2022-09-19 20:24:21 首次发布

本文链接：https://blog.csdn.net/qq_45447789/article/details/126903622

版权

1.透视表问题

透视表问题的一大特征就是行列间的转换，核心方法是：case when + group by

Q1:制作一个简单的透视表

创建数据表rowcol

DROP TABLE IF EXISTS `rowcol`;
CREATE TABLE `rowcol` (
  `年` int DEFAULT NULL,
  `月` int DEFAULT NULL,
  `值` decimal(3,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `rowcol` VALUES ('2009', '1', '1.10');
INSERT INTO `rowcol` VALUES ('2009', '2', '1.20');
INSERT INTO `rowcol` VALUES ('2009', '3', '1.30');
INSERT INTO `rowcol` VALUES ('2009', '4', '1.40');
INSERT INTO `rowcol` VALUES ('2010', '1', '2.10');
INSERT INTO `rowcol` VALUES ('2010', '2', '2.20');
INSERT INTO `rowcol` VALUES ('2010', '3', '2.30');

数据表如左表所示，要求做出的透视表如右边所示：

思路：首先按照年份分组，再通过对月进行判断，返回特定的值

SELECT
    年
    ,SUM(CASE WHEN 月=1 THEN 值 ELSE NULL END) 1月
    ,SUM(CASE WHEN 月=2 THEN 值 ELSE NULL END) 2月
    ,SUM(CASE WHEN 月=3 THEN 值 ELSE NULL END) 3月
    ,SUM(CASE WHEN 月=4 THEN 值 ELSE NULL END) 4月
FROM rowcol
    GROUP BY 年;

Q2: 创建购买数量透视表

首先创建商品购买记录表数据表

DROP TABLE IF EXISTS purchase_quantity; 
CREATE TABLE purchase_quantity( 
`year` VARCHAR(8), 
quarter VARCHAR(8), 
amount INT(8) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
purchase_quantity (`year`,quarter,amount) 
VALUE ('2019','1',1200) 
 ,('2019','2',1000) 
 ,('2019','3',800) 
 ,('2019','4',1300) 
 ,('2020','1',1100) 
 ,('2020','2',950) 
 ,('2020','3',700) 
 ,('2020','4',1050);

创建的数据表图示如下，包括年份，季度和数量三个字段。

要求：将数据表转化为以下样式，按照年份和各季度字段进行展示，字段q代表quater

实现代码：

SELECT
    `year`
    ,max(CASE WHEN `quarter`=1 THEN amount ELSE NULL END) q1
    ,max(CASE WHEN `quarter`=2 THEN amount ELSE NULL END) q2
    ,max(CASE WHEN `quarter`=3 THEN amount ELSE NULL END) q3
    ,max(CASE WHEN `quarter`=4 THEN amount ELSE NULL END) q4
FROM purchase_quantity
    GROUP BY `year`;

Q3:留存率问题

计算七日留存率、次月、二月等留存率时，会经常用透视表进行展示

创建数据表：由于数据太多，仅展示一部分（700条）

DROP TABLE IF EXISTS `cohort`;
CREATE TABLE `cohort` (
  `nick_name` varchar(255) NOT NULL,
  `pay_time` datetime DEFAULT NULL,
  `order_status` varchar(50) DEFAULT NULL,
  `pay_amount` varchar(50) DEFAULT NULL,
  `purchase_quantity` varchar(50) DEFAULT NULL,
  `province` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of cohort
-- ----------------------------
INSERT INTO `cohort` VALUES ('入倩出入深', '2019-09-01 00:10:04', '交易成功', '15.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('愛hya爱', '2019-09-01 00:14:52', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('象95象大', '2019-09-01 02:17:15', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('卡哇伊氛十', '2019-09-01 03:37:28', '交易成功', '22', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('一只羊哈阿', '2019-09-01 08:53:50', '交易成功', '85', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('啊别睡别你', '2019-09-01 09:02:39', '交易成功', '22', '1', '安徽省');
INSERT INTO `cohort` VALUES ('henuj', '2019-09-01 09:30:04', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('516yh', '2019-09-01 09:41:45', '交易成功', '101.2', '1', '四川省');
INSERT INTO `cohort` VALUES ('ameag', '2019-09-01 09:49:05', '交易成功', '22', '1', '湖南省');
INSERT INTO `cohort` VALUES ('涵66释于', '2019-09-01 10:36:05', '交易成功', '15.2', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('lsaew', '2019-09-01 10:54:45', '交易成功', '8.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('6081a', '2019-09-01 11:02:14', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('家电器阳元', '2019-09-01 11:09:51', '交易成功', '22', '1', '云南省');
INSERT INTO `cohort` VALUES ('271xl', '2019-09-01 11:19:00', '交易成功', '106.2', '2', '浙江省');
INSERT INTO `cohort` VALUES ('271xl', '2019-09-01 11:25:47', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('zsl01', '2019-09-01 11:45:39', '交易成功', '8.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('823hz', '2019-09-01 13:07:08', '交易成功', '8.4', '1', '重庆');
INSERT INTO `cohort` VALUES ('0281h', '2019-09-01 13:07:09', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('啦啦啦hz', '2019-09-01 13:44:43', '交易成功', '182.8', '2', '上海');
INSERT INTO `cohort` VALUES ('930岱杨', '2019-09-01 14:01:58', '交易成功', '109.6', '2', '广东省');
INSERT INTO `cohort` VALUES ('肥妞哦肥肥', '2019-09-01 14:17:48', '交易成功', '85', '1', '浙江省');
INSERT INTO `cohort` VALUES ('983aw', '2019-09-01 14:27:36', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('414汉翟', '2019-09-01 16:29:14', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('609栋陈', '2019-09-01 17:03:54', '交易成功', '109.7', '1', '江苏省');
INSERT INTO `cohort` VALUES ('摸摸哒哥晨', '2019-09-01 18:05:31', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('再而3而1', '2019-09-01 18:18:54', '交易成功', '101.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('laiix', '2019-09-01 18:52:45', '交易成功', '22', '1', '黑龙江省');
INSERT INTO `cohort` VALUES ('欣欣！雨唐', '2019-09-01 19:03:13', '交易成功', '106.3', '1', '四川省');
INSERT INTO `cohort` VALUES ('bao哲鲲', '2019-09-01 19:46:12', '交易成功', '22', '1', '上海');
INSERT INTO `cohort` VALUES ('记热情记忘', '2019-09-01 20:02:17', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('燕00海禄', '2019-09-01 20:37:57', '交易成功', '89.3', '1', '贵州省');
INSERT INTO `cohort` VALUES ('这世界使天', '2019-09-01 20:40:06', '交易成功', '22', '1', '上海');
INSERT INTO `cohort` VALUES ('520ij', '2019-09-01 21:34:02', '交易成功', '92.7', '1', '上海');
INSERT INTO `cohort` VALUES ('啊啊啊鸣鸣', '2019-09-01 21:45:40', '交易成功', '101.2', '1', '上海');
INSERT INTO `cohort` VALUES ('eam敏郭', '2019-09-01 21:47:19', '交易成功', '8.4', '1', '上海');
INSERT INTO `cohort` VALUES ('是什么火木', '2019-09-01 22:10:42', '交易成功', '15.2', '1', '浙江省');
INSERT INTO `cohort` VALUES ('or琦琪魏', '2019-09-01 22:10:47', '交易成功', '97.8', '1', '安徽省');
INSERT INTO `cohort` VALUES ('王心3有没', '2019-09-01 22:29:16', '交易成功', '97.8', '1', '天津');
INSERT INTO `cohort` VALUES ('章鱼丸炸油', '2019-09-01 22:33:06', '交易成功', '88.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('殇66辰星', '2019-09-01 22:40:38', '交易成功', '85', '1', '福建省');
INSERT INTO `cohort` VALUES ('661tt', '2019-09-01 22:49:17', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('美丽啊姜是', '2019-09-01 23:25:11', '交易成功', '181.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('满星空满溢', '2019-09-01 23:56:51', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-02 00:07:04', '交易成功', '106.2', '2', '广东省');
INSERT INTO `cohort` VALUES ('梁蹄猪蹄梁', '2019-09-02 01:46:07', '交易成功', '8.4', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('y微微l刘', '2019-09-02 08:15:14', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('象象丶象象', '2019-09-02 09:13:14', '交易成功', '85', '1', '北京');
INSERT INTO `cohort` VALUES ('620bt', '2019-09-02 09:16:47', '交易成功', '8.4', '1', '浙江省');
INSERT INTO `cohort` VALUES ('聂阿福阿聂', '2019-09-02 09:44:43', '交易成功', '39', '1', '北京');
INSERT INTO `cohort` VALUES ('333琳琳', '2019-09-02 10:06:48', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('146bt', '2019-09-02 10:54:46', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('laxow', '2019-09-02 11:13:39', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('里的你时在', '2019-09-02 12:17:14', '交易成功', '88.4', '1', '福建省');
INSERT INTO `cohort` VALUES ('眠77未月', '2019-09-02 12:24:02', '交易成功', '39', '1', '上海');
INSERT INTO `cohort` VALUES ('018鹏常', '2019-09-02 12:27:57', '交易成功', '85', '1', '山东省');
INSERT INTO `cohort` VALUES ('121ad', '2019-09-02 12:28:28', '交易成功', '182.8', '2', '辽宁省');
INSERT INTO `cohort` VALUES ('836嘻1', '2019-09-02 12:44:37', '交易成功', '88.4', '1', '上海');
INSERT INTO `cohort` VALUES ('源ii溯江', '2019-09-02 13:00:46', '交易成功', '22', '1', '江西省');
INSERT INTO `cohort` VALUES ('的回忆生人', '2019-09-02 13:59:22', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('_99bt', '2019-09-02 14:02:24', '交易成功', '181.9', '1', '广东省');
INSERT INTO `cohort` VALUES ('ayjas', '2019-09-02 15:29:39', '交易成功', '101.2', '1', '浙江省');
INSERT INTO `cohort` VALUES ('谢啊谢吗谢', '2019-09-02 16:56:28', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('亲笨笨盼靓', '2019-09-02 17:07:12', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('312zz', '2019-09-02 18:16:57', '交易成功', '88.4', '1', '湖南省');
INSERT INTO `cohort` VALUES ('n30am', '2019-09-02 18:42:39', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('阿姨1阿艾', '2019-09-02 18:48:00', '交易成功', '181.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('ou1是我', '2019-09-02 19:31:46', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('011bt', '2019-09-02 19:35:13', '交易成功', '581.4', '1', '湖北省');
INSERT INTO `cohort` VALUES ('181ux', '2019-09-02 19:44:36', '交易成功', '96.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('100九小', '2019-09-02 19:49:04', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('400然浩', '2019-09-02 19:59:09', '交易成功', '15.2', '1', '江苏省');
INSERT INTO `cohort` VALUES ('123龙乌', '2019-09-02 20:02:41', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('狠姐姐姐狠', '2019-09-02 20:08:19', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('仙宝贝仙神', '2019-09-02 20:09:37', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('156身安', '2019-09-02 20:10:36', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('小蠢蠢蠢蠢', '2019-09-02 20:15:05', '交易成功', '109.7', '1', '广东省');
INSERT INTO `cohort` VALUES ('821hs', '2019-09-02 20:41:27', '交易成功', '16.9', '1', '上海');
INSERT INTO `cohort` VALUES ('123神王', '2019-09-02 20:45:50', '交易成功', '97.8', '1', '山东省');
INSERT INTO `cohort` VALUES ('毅大爷嘉谢', '2019-09-02 20:51:39', '交易成功', '97.8', '1', '广东省');
INSERT INTO `cohort` VALUES ('013bt', '2019-09-02 21:05:19', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('5bfek', '2019-09-02 21:13:59', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('356bt', '2019-09-02 21:30:19', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('9贝贝51', '2019-09-02 22:29:13', '交易成功', '22', '1', '湖北省');
INSERT INTO `cohort` VALUES ('807bt', '2019-09-02 22:50:27', '交易成功', '39', '1', '湖南省');
INSERT INTO `cohort` VALUES ('704uj', '2019-09-02 23:06:55', '交易成功', '101.2', '1', '广东省');
INSERT INTO `cohort` VALUES ('的心i勇i', '2019-09-02 23:28:40', '交易成功', '190.4', '1', '福建省');
INSERT INTO `cohort` VALUES ('666年伍', '2019-09-02 23:56:52', '交易成功', '97.8', '1', '浙江省');
INSERT INTO `cohort` VALUES ('思妞妞宝宝', '2019-09-03 00:50:21', '交易成功', '16.9', '1', '福建省');
INSERT INTO `cohort` VALUES ('的女人加撒', '2019-09-03 02:07:38', '交易成功', '88.4', '1', '江苏省');
INSERT INTO `cohort` VALUES ('很想睡iv', '2019-09-03 08:07:56', '交易成功', '106.3', '1', '河北省');
INSERT INTO `cohort` VALUES ('走伙伴啊走', '2019-09-03 08:29:13', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('521颗一', '2019-09-03 08:43:47', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('vxm小洪', '2019-09-03 09:09:48', '交易成功', '16.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('呆西仔呆呆', '2019-09-03 09:15:08', '交易成功', '16.9', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('291晴林', '2019-09-03 09:30:42', '交易成功', '88.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('花lu些那', '2019-09-03 10:23:25', '交易成功', '8.4', '1', '河北省');
INSERT INTO `cohort` VALUES ('317bt', '2019-09-03 10:33:52', '交易成功', '22', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('299hh', '2019-09-03 10:35:05', '交易成功', '22', '1', '广东省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-03 10:54:57', '交易成功', '15.2', '1', '广西壮族自治区');
INSERT INTO `cohort` VALUES ('662之梦', '2019-09-03 10:55:54', '交易成功', '8.4', '1', '广东省');
INSERT INTO `cohort` VALUES ('311事百', '2019-09-03 11:19:59', '交易成功', '22', '1', '浙江省');
INSERT INTO `cohort` VALUES ('550健小', '2019-09-03 12:07:36', '交易成功', '126.7', '1', '浙江省');
INSERT INTO `cohort` VALUES ('614仔杨', '2019-09-03 13:20:30', '交易成功', '13.5', '1', '内蒙古自治区');
INSERT INTO `cohort` VALUES ('鱼臭猫鱼懒', '2019-09-03 13:22:48', '交易成功', '97.8', '1', '湖北省');
INSERT INTO `cohort` VALUES ('ice香花', '2019-09-03 13:29:14', '交易成功', '181.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('123亚徐', '2019-09-03 13:34:44', '交易成功', '8.4', '1', '四川省');
INSERT INTO `cohort` VALUES ('待下去这就', '2019-09-03 14:19:09', '交易成功', '101.2', '1', '山东省');
INSERT INTO `cohort` VALUES ('打12柠青', '2019-09-03 15:05:29', '交易成功', '22', '1', '福建省');
INSERT INTO `cohort` VALUES ('gp6at', '2019-09-03 15:41:16', '交易成功', '22', '1', '江苏省');
INSERT INTO `cohort` VALUES ('华尔兹舞珺', '2019-09-03 15:51:35', '交易成功', '33.8', '3', '广东省');
INSERT INTO `cohort` VALUES ('802ul', '2019-09-03 17:29:26', '交易成功', '16.9', '1', '湖北省');
INSERT INTO `cohort` VALUES ('222um', '2019-09-03 17:30:07', '交易成功', '97.8', '1', '河南省');
INSERT INTO `cohort` VALUES ('959女天', '2019-09-03 17:48:49', '交易成功', '8.4', '1', '河南省');
INSERT INTO `cohort` VALUES ('339唧唧', '2019-09-03 17:55:46', '交易成功', '84.6', '6', '浙江省');
INSERT INTO `cohort` VALUES ('ianul', '2019-09-03 17:59:55', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('小确信慧佳', '2019-09-03 18:01:49', '交易成功', '84.6', '6', '浙江省');
INSERT INTO `cohort` VALUES ('333么什', '2019-09-03 18:26:24', '交易成功', '8.4', '1', '北京');
INSERT INTO `cohort` VALUES ('yanhz', '2019-09-03 18:35:19', '交易成功', '15.2', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('230yc', '2019-09-03 20:07:31', '交易成功', '85', '1', '广东省');
INSERT INTO `cohort` VALUES ('sun方陈', '2019-09-03 20:40:31', '交易成功', '181.9', '1', '江苏省');
INSERT INTO `cohort` VALUES ('202ot', '2019-09-03 21:07:52', '交易成功', '8.4', '1', '辽宁省');
INSERT INTO `cohort` VALUES ('打喷嚏麦闭', '2019-09-03 21:14:10', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('花野裕野花', '2019-09-03 21:15:40', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('897bt', '2019-09-03 21:18:48', '交易成功', '16.9', '1', '河北省');
INSERT INTO `cohort` VALUES ('519ow', '2019-09-03 21:18:52', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('148bt', '2019-09-03 21:19:33', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('e77hs', '2019-09-03 21:20:37', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('012bt', '2019-09-03 21:29:17', '交易成功', '16.9', '1', '浙江省');
INSERT INTO `cohort` VALUES ('大盒子大何', '2019-09-03 21:31:08', '交易成功', '97.8', '1', '四川省');
INSERT INTO `cohort` VALUES ('迟永铠永迟', '2019-09-03 21:32:33', '交易成功', '8.4', '1', '山东省');
INSERT INTO `cohort` VALUES ('828bt', '2019-09-03 21:32:41', '交易成功', '16.9', '1', '四川省');
INSERT INTO `cohort` VALUES ('发桔

最低0.47元/天解锁文章