周粥粥ph

分类

题解(21)

/ 注册

全部文章（共19篇）

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

with s AS(SELECT gi.user_id, u.name, case when gi.type ='add' then gi.grade_num when gi.type ='reduce' then -gi.grade_num end AS gn FROM grade_info g...

Mysql

2021-09-29

0 351

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

WITH s AS(SELECT ri.job, LEFT(ri.date,7) as mon, sum(ri.num) as cnt FROM resume_info ri GROUP BY ri.job,LEFT(ri.date,7) ORDER BY LEFT(ri.date,7) de...

Mysql

2021-09-28

0 322

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

SELECT x.source, count(x.source) from (SELECT oi.id, oi.product_name, oi.is_group_buy, case when c.name is null then 'GroupBuy' else c.name END AS&quo...

Mysql

2021-09-28

0 398

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

SELECT x.user_id, min(date) AS "first_buy_date", max(date) AS "second_buy_date", x.cnt FROM (SELECT oi.id, oi.user_id,...

Mysql

2021-09-28

0 421

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

SELECT oi.user_id, min(oi.date), x.cnt FROM order_info oi LEFT JOIN (select user_id, COUNT(id) as cnt from order_info where datediff(date,"2025-1...

Mysql

2021-09-28

0 464

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

SELECT a.user_id, a.date as "first_buy_date", a.cnt from (SELECT oi.user_id, oi.date, ROW_NUMBER()over(partition by oi.user_id order by o...

Mysql

2021-09-28

2 525

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

SELECT * FROM order_info oi WHERE oi.user_id IN (select user_id from order_info where datediff(date,"2025-10-15")>0 and status ="...

Mysql

2021-09-28

0 418

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

select xx.id,xx.job,xx.score,xx.t_rank from (select g.id, g.job, g.score, ROW_NUMBER()over(partition by job order by score desc) as t_rank, x.start, ...

Mysql

2021-09-27

0 333

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

select x.id, x.name, x.score from (SELECT g.id, l.name, g.score, DENSE_RANK()over(partition by l.name order by score desc)mm FROM grade g LEFT JOIN l...

Mysql

2021-09-27

1 371

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

SELECT u.name AS u_n, pn.date AS date, sum(pn.number)over(partition by u.name order by pn.date) FROM passing_number pn LEFT JOIN user u on u.id = pn....

Mysql

2021-09-26

0 331