工具人二号

分类

题解(21)

/ 注册

全部文章（共20篇）

题解 | #牛客每个人最近的登录日期(三)#

select round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3) from login where (user_id,date) in (select user_id,DATE_ADD(...

2021-09-02

0 321

题解 | #考试分数(五)#

来给大家表演一手笛卡尔积思路大概就是rank值在（四）start和end之间就好了 SELECT t1.id,t1.job,t1.score,t1.rk FROM (SELECT *,rank()over(partition by job order by score desc) rk FROM g...

2021-08-31

0 392

题解 | #考试分数(四)#

用到了MOD函数和case when循环，第一遍写又忘了加END SELECT job ,ROUND(CASE MOD(COUNT(score),2) WHEN 1 THEN COUNT(score)/2+0.5 ELSE COUNT(score)/2 end,0) ,ROU...

2021-08-31

2 489

题解 | #考试分数(三)#

窗口函数 select t.id,l.name,t.score FROM (select *,DENSE_RANK()over(partition by language_id order BY score desc ) dr FROM grade) t JOIN language l on ...

2021-08-31

0 340

题解 | #考试分数(二)#

小做一下表链接就好了 select g.id,g.job,g.score FROM grade g JOIN (select job,avg(score) a FROM grade GROUP BY job) t on g.job=t.job WHERE g.score>a order BY ...

2021-08-31

0 317

题解 | #牛客的课程订单分析(七)#

上一题改改就好了 SELECT case o.is_group_buy WHEN 'Yes' then 'GroupBuy' else c.name end d,count(1) FROM order_info o LEFT JOIN client c ON o.client_id=c.id WHE...

2021-08-30

0 335

题解 | #牛客的课程订单分析(六)#

用下左右链接即可 SELECT o.id,o.is_group_buy,c.name FROM order_info o LEFT JOIN client c ON o.client_id=c.id WHERE user_id in (SELECT user_id FROM order_info W...

2021-08-30

0 289

题解 | #牛客的课程订单分析(五)#

这道题被我写的异常麻烦先找到符合条件的用户 SELECT user_id FROM order_info WHERE product_name in('C++','JAVA','Python') and status ='completed' and date>...

2021-08-30

0 449

题解 | #牛客的课程订单分析(四)#

（二）改一改就好了 SELECT user_id,min(date),count(product_name) FROM order_info WHERE product_name in('C++','JAVA','Python') and status ='completed...

2021-08-30

0 317

题解 | #牛客的课程订单分析(三)#

就用上一题找到的ID作为临时表 SELECT * FROM order_info WHERE user_id IN (SELECT user_id FROM order_info WHERE product_name in('C++','JAVA','Python') and...

2021-08-30

0 330