首页 > 试题广场 >

订单表tbl_order用户表tbl_user1、查询201

[问答题]
订单表tbl_order

用户表tbl_user


1、查询2017年上半年（1-6月），上海地区销售额排名前10的商品ID。需要的字段：商品ID

2、查询2017年7月所有订单中，有且仅有轮胎和保养两个品类的订单数
添加回答
1064丶Notfound头像 1064丶Notfound
1、select pid
from tbl_order o 
JOIN tbl_user u 
on o.userid=u.userid
where City = '上海市' and orderdate between '2017/1/1 00:00' and '2017/6/30 23:59'
group by pid
order by sum(salesamout)
limit 10

2、SELECT pid, COUNT(*)

FROM tbl_order 
WHERE category NOT IN ('车品','美容'）and orderdate between '2017/7/1 00:00' and '2017/7/31 23:59'
GROUP BY pid


发表于 2021-02-08 17:49:14 回复(4)
牛客875451317号头像 牛客875451317号 
1. 
selcet pid from tbl_order o left join tbl_user u on o.user_id=u.user_id
where u.city='上海' and month(o.order_date)<=6 
order by sum(salesamout)
group by pid
limit 10

2.


	select count(*) from  tbl_user





	where category in ('轮胎','保养') and month(order date)=7 and year(order date)=2017

编辑于 2021-08-16 15:14:13 回复(0)
牛客60935522号头像 牛客60935522号
  第2题            select 
        count(1)
        from (
               select 
                    orderid
                    ,count( distinct category ) as cat_cnt
                    ,count( distinct case when category = '轮胎' then category end ) as lt_cnt
                    ,count( distinct case when category = '保养' then category end ) as by_cnt
                from tbl_order
                where orderdate >= date'2017-07-01' and orderdate  < date'2017-08-01'
                group by orderid
                having cat_cnt= 2 and lt_cnt = 1 and by_cnt = 1
                
             ) ff
发表于 2022-04-11 23:28:03 回复(0)
爱吃肉的小饼干希望被offer砸中头像 爱吃肉的小饼干希望被offer砸中 
1）
方法1：
select pid 
from (
    select pid, sum(salesamount) as sales, dense_rank() over(order by sum(salesamount) desc) as ranking
    from tbl_order as t1
    left join tbl_user as t2
    on t1.userid= t2.userid
    where City = "上海市"
    and month(orderdate) between 1 and 6
    and year(orderdate) = 2017
    group by pid
) as t
where ranking <= 10

方法2：
select pid
from tbl_order as t1
left join tbl_user as t2
on t1.userid= t2.userid
where City = "上海市"
and month(orderdate) between 1 and 6
and year(orderdate) = 2017
group by pid
order by sum(salesamount) desc
limit 10

2）


	
	

		select count(distinct orderid)
	


	

		from (
	


	

		    select orderid, count(distinct category) as cat_num
	


	

		    from tbl_order
	


	

		    where year(orderdate) = 2017 
	


	

		    and category in ("轮胎", "保养")
	


	

		    group by orderid
	


	

		) as t
	


	

		where cat_num = 2

编辑于 2023-07-20 17:20:04 回复(0)
牛客349970922号头像 牛客349970922号
1、
select pid from
(
select pid,sum(salesamount) as s
from tbl_order t1
join tbl_user t2
on t1.userid =t2.userid
where to_date(t1.orderdate,'yyyyMMdd')>='2017-01-01' and to_date(t1.orderdate,'yyyyMMdd')<'2017-07-01'
and t2.City='上海市'
group by 1
order by s desc
) t
limit 10;


2、
select count(orderid)
from tbl_order 
where year(orderdate)='2017' and month(orderdate)=7 and category in ('轮胎','保养');


发表于 2022-02-17 14:41:46 回复(0)
Yasmine_La头像 Yasmine_La
select t0.pid
from(select pid
,sum(salesmout)  as m
from tbl_order 
where datetime>=2017/1 and datetime<=2017/6  and userid in ( select userid from tbl_user where city='上海市')
group by pid
order by m desc
limit 10)t0

select count(distinct orderid) from tbl_order  where datetime=2017/7 and category in ('轮胎','保养')
发表于 2022-02-16 20:47:47 回复(0)
啊坡糍嘚头像 啊坡糍嘚 
（1）select t1.pid from tbl_order t1
left join tbl_user t2 on t1.userid=t2.userid
where t2.City='上海市' and year(t1.orderdate)=2017 and month(t1.roderdate) between 1 and 6 #month和year函数可以返回日期的月和年
group by t1.pid 
order by sum(t1.salesamout) desc # order by 后面可以跟聚合函数，但是group by 和where后面不能直接跟聚合函数，group by 的having后面可以加
limit 10
(2)select count(orderid) as cnt from tbl_order 
where year(orderdate)=2017 and month(roderdate)=7 
and category in ('轮胎','保养')


编辑于 2021-09-13 12:02:32 回复(0)
黄瓜不会质壁分离头像 黄瓜不会质壁分离
2. 有且仅有吧 盲猜一个

SELECT COUNT(DISTINCT order_id)

FROM tbl_order

WHERE orderdate BETWEEN '2017/7/1 00:00' AND '2017/7/31 23:59'

AND category IN ('轮胎', '保养' )

AND order_id IN (SELECT order_id

    FROM tbl_order 

    GROUP BY order_id

    WHERE COUNT(order_id) = 2)

发表于 2021-09-12 23:23:14 回复(0)
2htttttttttt头像 2htttttttttt
1.
select a.pid
from
(
select pid,sum(salesamount) as amount
from tbl_order,tbl_user
where orderdate between '2017/1/1 00:00' and '2017/6/30 23:59'
and tbl_order.userid = tbl_user.userid
and city = '上海市'
group by pid
) a
order by a.amount desc 
limit 1,10

2.
select count(*)
from tbl_order 
where orderdate like "%2017/7%"
and category in （'轮胎'，'保养'）
发表于 2021-08-24 16:46:38 回复(0)
从零开始刷代码头像 从零开始刷代码
1、
SELECT pid FROM tbl_order
WHERE userid IN (
        SELECT userid FROM tbl_user
        WHERE City = '上海'
) 
AND datetime BETWEEN '2017/01/01 00:00' AND '2017/06/30 23:59';

发表于 2021-02-17 17:33:45 回复(0)
牛客863884691号头像 牛客863884691号
有大哥给点建议吗
1.
select pid from
(
select pid,rank() over (partition by pid order by x) rn from
(
select pid,sum(salesamout) as x
  from tbl_order a JOIN tbl_user b on a.userid=b.userid
where City = '上海市‘ and Month(orderdate) between 1 and 6
group by pid
) as R
) as T
where rn = 10

2.
select count(orderid) from tbl_order where orderid=(
select orderid,
           sum(case when category NOT IN ("车品’,'美容‘） then 1 Else 0 End）as A
 from tbl_order 
where Month(orderdate)=7
group by orderid
 )
 and A=0
group by order id
发表于 2020-12-11 10:58:31 回复(0)
提交观点

问题信息

途虎 2021 数据分析师
来自：途虎养车2023秋招数...
上传者：小小
难度：
11条回答 2463浏览

热门推荐

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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