查询单日多次下订单的用户信息？_

现有某公司部分订单数据及用户会员等级数据，如下所示：

订单信息表：order_tb（订单id-order_id,用户id-user_id,订单金额-order_price,订单创建时间-order_time）

order_id	user_id	order_price	order_time
101	11	380	2022-09-01 09:00:00
102	12	200	2022-09-01 10:00:00
103	13	260	2022-09-01 12:00:00
104	11	100	2022-09-02 11:00:00
105	12	150	2022-09-02 12:00:00
106	12	1200	2022-09-02 13:00:00
107	11	60	2022-09-03 09:00:00
108	13	380	2022-09-03 09:30:00

会员等级信息表：uservip_tb（用户id-user_id,会员等级-vip,积分-point）

user_id	vip	point
10	银卡会员	530
11	银卡会员	1555
12	钻石会员	12000
13	金卡会员	6115
14	普通会员	230
15	银卡会员	810
16	普通会员	330

请查询单日下单多次的用户信息？
要求输出：订单日期，user_id，下单次数，会员等级
注：单日多次下订单指该日同一用户下单次数大于1次，结果按照下单次数降序排序

示例数据结果如下：

order_date	user_id	order_nums	vip
2022-09-02	12	2	钻石会员

结果解释：

user_id为12的用户在9月2日分别下了order_id为105、106的订单，故结果如上。

输入

drop table if exists  `order_tb` ; 
CREATE TABLE `order_tb` (
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`order_price` int(11) NOT NULL,
`order_time` datetime NOT NULL,
PRIMARY KEY (`order_id`));
INSERT INTO order_tb VALUES(101,11,380,'2022-09-01 09:00:00'); 
INSERT INTO order_tb VALUES(102,12,200,'2022-09-01 10:00:00'); 
INSERT INTO order_tb VALUES(103,13,260,'2022-09-01 12:00:00'); 
INSERT INTO order_tb VALUES(104,11,100,'2022-09-02 11:00:00'); 
INSERT INTO order_tb VALUES(105,12,150,'2022-09-02 12:00:00'); 
INSERT INTO order_tb VALUES(106,12,1200,'2022-09-02 13:00:00'); 
INSERT INTO order_tb VALUES(107,11,60,'2022-09-03 09:00:00'); 
INSERT INTO order_tb VALUES(108,13,380,'2022-09-03 09:30:00'); 

drop table if exists  `uservip_tb` ; 
CREATE TABLE `uservip_tb` (
`user_id` int(11) NOT NULL,
`vip` varchar(16) NOT NULL,
`point` int(11) NOT NULL,
PRIMARY KEY (`user_id`));
INSERT INTO uservip_tb VALUES(10,'银卡会员',530); 
INSERT INTO uservip_tb VALUES(11,'银卡会员',1555); 
INSERT INTO uservip_tb VALUES(12,'钻石会员',12000); 
INSERT INTO uservip_tb VALUES(13,'金卡会员',6115); 
INSERT INTO uservip_tb VALUES(14,'普通会员',230); 
INSERT INTO uservip_tb VALUES(15,'银卡会员',810); 
INSERT INTO uservip_tb VALUES(16,'普通会员',330);

with t1 as ( select date(order_time) as order_date, user_id, count(day(order_time)) as order_nums from order_tb group by user_id, date(order_time) having count(day(order_time)) > 1 ) select t1.order_date, t1.user_id, t1.order_nums, u.vip from t1 join uservip_tb u on t1.user_id = u.user_id

select date_format (order_time, '%Y-%m-%d') as order_date, o.user_id as user_id, count(order_id) as order_nums, u.vip as vip from order_tb o, uservip_tb u where o.user_id = u.user_id group by o.user_id, order_date having order_nums > 1 order by order_nums desc

查询单日多次下订单的用户信息？

输入

输出

问题信息

热门推荐

相关试题