首页 > 试题广场 >

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

[编程题]查询单日多次下订单的用户信息？
  • 热度指数：18442 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
现有某公司部分订单数据及用户会员等级数据，如下所示：
订单信息表：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的订单，故结果如上。
示例1

输入

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);

输出

order_date|user_id|order_nums|vip
2022-09-02|12|2|钻石会员
算法知识视频讲解
添加回答
一叶知秋201910082239620头像 一叶知秋201910082239620
select
    date(o.order_time) as order_date,
    o.user_id,
    count(o.user_id) as order_nums,
    u.vip
from order_tb o 
left join uservip_tb u 
on o.user_id = u.user_id
group by o.user_id, date(o.order_time)
having count(o.user_id) > 1
发表于 2023-07-14 10:35:23 回复(1)
饥饿的牛油刷了100道题头像 饥饿的牛油刷了100道题 
select 
date(order_time) order_date,
o.user_id,
count(order_id) order_nums,
vip
from order_tb o join uservip_tb u
on o.user_id = u.user_id 
group by o.user_id,vip,order_date
having order_nums > 1
order by order_nums desc

发表于 2025-03-24 23:53:38 回复(1)
小肥来摸鱼头像 小肥来摸鱼
select 
date(o.order_time) order_date
,o.user_id
,count(o.user_id) order_nums
,u.vip
from order_tb o
join uservip_tb u on o.user_id = u.user_id
group by o.user_id,date(o.order_time)
having count(o.user_id) > 1
发表于 2025-03-20 16:00:44 回复(0)
I_wanna头像 I_wanna 
select date_format(order_time,"%Y-%m-%d") order_date,a.user_id,count(order_id) order_nums, vip
from order_tb a join uservip_tb b on a.user_id = b.user_id
group by order_date,user_id
having count(order_date)>1
关键在于要知道group by 多个字段的方式是用逗号隔开，第二点就是查询时间是用date_format(日期，需要格式),一般来说四位的年份需要%Y(大写Y)，二位年份就是%y(小写Y)

发表于 2023-07-26 10:29:05 回复(1)
七分糖少冰a头像 七分糖少冰a 
select
    date_format(order_time,'%Y-%m-%d') order_date,
    ot.user_id,
    count(order_id) order_nums,
    vip 
from
    order_tb as ot
    left join uservip_tb as ut on ot.user_id=ut.user_id
group by
    date_format(order_time,'%Y-%m-%d'),
    ot.user_id
having
    count(order_id)>1
order by
    order_nums desc
打卡练习2025.11.05
发表于 2025-11-05 15:29:26 回复(0)
海底有颗星头像 海底有颗星
SELECT DATE(order_time) AS order_date,user_id,
COUNT(*) AS order_nums,uservip_tb.vip
FROM order_tb
JOIN uservip_tb
USING(user_id)
GROUP BY user_id,DATE(order_time)
HAVING order_nums>1
发表于 2025-10-23 14:39:54 回复(0)
Mysql
像初雪一样遇见你头像 像初雪一样遇见你 
select
date(order_time) as order_date
,x.user_id
,count(*) as order_nums
,vip
from order_tb x 
join uservip_tb
using(user_id)
group by 1,2
having count(*)>1
order by 3 desc

发表于 2025-10-16 14:40:50 回复(0)
一飞冲天的包子头像 一飞冲天的包子
select date(order_time) order_date,
user_id,
count(order_id) order_nums,
vip
from order_tb
join uservip_tb
using(user_id)
group by order_date,user_id
having order_nums>1
order by order_nums desc;
发表于 2025-10-04 10:26:40 回复(0)
除却巫山是小婷头像 除却巫山是小婷
SELECT DATE(order_time) order_date,u.user_id,count(order_id) order_nums,vip
FROM `uservip_tb` u,`order_tb` o
where u.user_id=o.user_id
group by DATE(order_time),user_id
having order_nums>1
order by order_nums desc
发表于 2025-09-23 09:25:54 回复(0)
幸福的小熊猫allin春招头像 幸福的小熊猫allin春招
为啥不能用to_date
发表于 2025-09-16 16:22:57 回复(0)
offer多多的小蜗牛超帅啦头像 offer多多的小蜗牛超帅啦
SELECT
    DATE(a.order_time) AS order_date,
    a.user_id,
    if(COUNT(a.user_id)>1,COUNT(a.user_id),0)  AS order_nums, -- 每个用户的总订单数
    b.vip
FROM
    order_tb a
    LEFT JOIN (
        SELECT
            user_id,
            vip
        FROM
            uservip_tb
    ) b ON a.user_id = b.user_id
    -- 先通过子查询过滤订单数大于1的用户
WHERE
    a.user_id IN (
        SELECT
            user_id
        FROM
            order_tb
        GROUP BY
            user_id
        HAVING
            COUNT(*) > 1
    )
GROUP BY
    DATE(a.order_time),
    a.user_id
having order_nums >1
ORDER BY
    order_nums DESC;

发表于 2025-09-08 22:37:14 回复(0)
牛客342889858号头像 牛客342889858号

select order_date, t2.user_id, order_nums,vip
from uservip_tb as t1
left join
(
select user_id,date(order_time) order_date, count(*) order_nums
from order_tb
group by user_id,date(order_time)
having order_nums>1) as t2
on t1.user_id=t2.user_id
where t2.user_id is not null
order by order_nums desc;
发表于 2025-09-08 17:08:09 回复(0)
Mysql
牛客199132737号头像 牛客199132737号 
SELECT
    DATE(o.order_time) AS order_date,
    o.user_id,
    COUNT(*) AS order_nums,
    u.vip
FROM order_tb AS o
JOIN uservip_tb AS u 
ON o.user_id = u.user_id
GROUP BY DATE(o.order_time), o.user_id
HAVING COUNT(*) > 1
ORDER BY order_nums DESC;

发表于 2025-09-04 15:02:51 回复(0)
牛客765007087号头像 牛客765007087号 
select
    date(order_time) as order_date ,
    o.user_id,
    count(order_id) as order_nums,
    vip
from order_tb o
join uservip_tb using(user_id)
group by order_date,o.user_id
having count(order_id) > 1
order by order_nums desc

发表于 2025-08-27 15:20:01 回复(0)
爱吃鱼的大学生有点心碎头像 爱吃鱼的大学生有点心碎
SELECT
 O.order_date as order_date,
 O.user_id as   user_id ,
 count(*)  as order_nums,  
 U.vip as   vip
FROM (select
       user_id,
       date_format(order_tb.order_time,'%Y-%m-%d')  as order_date
      from order_tb) AS O
left JOIN uservip_tb AS U ON O.user_id=U.user_id
group by O.order_date , O.user_id
having order_nums>1
ORDER BY 3 DESC

发表于 2025-08-11 20:55:51 回复(0)
追赶太阳的雪碧很可爱头像 追赶太阳的雪碧很可爱
select
date_format(order_time,"%Y-%m-%d") AS order_date
,o.user_id
,count(o.user_id) as order_nums
,vip
from order_tb o
join uservip_tb u
using (user_id)
group by order_date,o.user_id
order by order_nums desc
limit 1
发表于 2025-08-11 14:52:34 回复(0)
DeerChaim头像 DeerChaim
select date(order_time) as order_date,
user_id,count(distinct order_time) as order_nums,
vip
from order_tb
join uservip_tb
using(user_id)
group by order_date,user_id,vip
having count(distinct order_time)>1
order by order_nums desc
发表于 2025-07-31 02:30:49 回复(0)
牛客482350188号头像 牛客482350188号
发表于 2025-07-29 15:26:48 回复(0)
抱着太阳看月亮头像 抱着太阳看月亮
with a as (
    select date(order_time) as order_date,b.user_id,count(order_time) as order_nums,vip
    from order_tb b
    left join uservip_tb c on b.user_id = c.user_id
    group by user_id,date(order_time)
    having order_nums >= 2
    order by order_nums desc
)
select * from a
发表于 2025-07-29 13:42:12 回复(0)
你是最棒的zy头像 你是最棒的zy 
select date_format(order_time,'%Y-%m-%d') as order_date,
ut.user_id,count(*) as order_nums,vip 
from order_tb ot 
left join uservip_tb ut on ut.user_id=ot.user_id
group by order_date,ut.user_id
having order_nums>1
order by order_nums desc

发表于 2025-07-14 16:06:30 回复(0)
提交观点

问题信息

SQL
来自：2023年携程秋招技术...
难度：
70条回答 1429浏览

热门推荐

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号