SQL面试题(2)
第一题
(图片来源网络,侵删)
创建trade_orders表: create table `trade_orders`( `trade_id` varchar(255) NULL DEFAULT NULL, `uers_id` varchar(255), `trade_fee` int(20), `product_id` varchar(255), `time` varchar(255) )ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = dynamic 添加数据: INSERT INTO `trade_orders` VALUES ('20171023140109701120.0', 'ovsuXw6-7XI81U049TbjugEz912w', '168', '2017001000030.0', '2017-10-23 22:04:45'); INSERT INTO `trade_orders` VALUES ('20171023220445700096.0', 'ovsuXw2TajDGuHPSF798z9FSy38c', '18', '2017001000031.0', '2017-10-23 11:55:23'); INSERT INTO `trade_orders` VALUES ('20171023110103801856.0', 'ovsuXw6-7XI81U049TbjugEz912w', '', '2017002000130.0', '2017-10-28 21:19:18'); INSERT INTO `trade_orders` VALUES ('20171023184746299392.0', 'ovsuXw6-7XI81U049TbjugEz912w', '19', '2017002000129.0', '2017-10-28 21:19:18'); INSERT INTO `trade_orders` VALUES ('20171023184832901120.0', 'ovsuXw 2114bvpcjiR83R4dVzPw2Gg', '200', '2017002000129.0', '2023-03-30 11:44:07'); INSERT INTO `trade_orders` VALUES ('20171023205014200320.0', 'ovsuXwwqmoj-YC987zDnpE9KaPs8', '300', '2017001000031.0', '2023-03-29 18:48:32'); INSERT INTO `trade_orders` VALUES ('20171023110103801856.0', 'ovsuXw6-7XI81U049TbjugEz912w', '0', '2017002000130.0', '2017-10-28 21:19:18');
题一:每个用户的首次付费时间
select uers_id as 用户id, min(time) as 首次付费时间 from trade_orders GROUP BY uers_id
题二:每个用户首次付费后30天内的总付费次数:
SELECT users_id AS 用户ID, COUNT(distinct trade_fee) AS 总付费次数 FROM trade_orders AS t WHERE time
文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。
还没有评论,来说两句吧...