当前位置：首页 > news >正文

SQL面试题（2）

news 2025/8/25 8:28:56

第一题

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

题一：每个用户的首次付费时间

selectuers_id as 用户id,min(time) as 首次付费时间
from trade_orders
GROUP BYuers_id

题二：每个用户首次付费后30天内的总付费次数：

SELECT users_id AS 用户ID, COUNT(distinct trade_fee) AS 总付费次数 
FROM trade_orders AS t
WHERE time <= DATE_ADD((SELECT MIN(time) FROM trade_orders AS min_time WHERE min_time.users_id = t.users_id and t.trade_fee != ""), INTERVAL 30 DAY) 
GROUP BY users_id
ORDER BY总付费次数 desc;

解释：