尚硅谷hql练习 | 您所在的位置:网站首页 › SQL刷题网站 › 尚硅谷hql练习 |
尚硅谷hql 50练
尚大练习网站 1. 查询累积销量排名第二的商品在这里插入图片描述](https://img-blog.csdnimg.cn/24d8c48e679e4cca95eab16c70945984.png) with t1 as ( SELECT sku_id, sum(sku_num) as num from order_detail group by sku_id ), t2 as ( SELECT sku_id, num, rank() over ( order BY num desc ) rk FROM t1 ) SELECT sku_id FROM t2 where rk = 2; 2.查询至少连续三天下单的用户 思路这道题刚开始用的rank()函数进行排序,rank()的排序规则是 1 1 3 ,即出现排名相同的会处于同一名次,会出现如下的情况 即同一天登录的无法去重 使用row_number() 可以解决问题,因为row_number()的排序规则是1,2,3,4 SELECT user_id FROM ( SELECT user_id, count(*) as cnt FROM ( SELECT user_id, create_date, rk, date_sub(create_date, rk) as ds FROM ( SELECT user_id, create_date, row_number() over( partition by user_id order by create_date ) as rk FROM order_info ) t1 ) t2 GROUP by user_id, ds HAVING cnt >= 3 ) t3;2022-11-11 3.查询各品类销售商品的种类数及销量最高的商品注意点:在hql中 group by中出现的字段,在select中要出现,要么是要查询的,要么用聚合函数包裹住 包裹比较多,有更好的解法还请多多指教。 SELECT category_id, category_name, sku_id, name, order_num, sku_cnt FROM ( SELECT category_id, category_name, sku_id, name, sku_cnt, order_num, rank() over( PARTITION by category_id order by order_num desc ) as rk FROM ( select t1.category_id, t1.category_name, od.sku_id, si.name, sum(od.sku_num) as order_num, t1.sku_cnt FROM ( SELECT ci.category_id, ci.category_name, count(*) as sku_cnt FROM sku_info si JOIN category_info ci on si.category_id = ci.category_id GROUP by ci.category_id, ci.category_name ) t1 join sku_info si ON t1.category_id = si.category_id JOIN order_detail od ON si.sku_id = od.sku_id GROUP by t1.category_id, t1.category_name, od.sku_id, si.name, t1.sku_cnt ) t2 ) t3 where rk=1; 4.查询用户的累计消费金额及VIP等级从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额,以及每个用户在其每个下单日期的VIP等级。 用户vip等级根据累积消费金额计算,计算规则如下: 设累积消费总额为X, 若0= |
CopyRight 2018-2019 实验室设备网 版权所有 |