牛客30426190号

分类

/ 注册

全部文章（共28篇）

子查询中使用窗口函数

SELECT user_id, MIN(date), MAX(date), cnt FROM( SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) rk, COUNT(*) OVER(...

2023-10-10

1 336

利用上一题中的代码

SELECT id, t1.job, score, t_rank FROM( SELECT *, DENSE_RANK() OVER(PARTITION BY job ORDER BY score DESC) AS t_rank FRO...

2023-10-08

1 283

通过子查询中使用窗口函数进行筛选

SELECT id, name, score FROM ( SELECT g.id AS id, l.name AS name, g.score AS score, DENSE_RANK() OVER(PARTITION BY l.name ORDE...

2023-10-07

2 361

SUM+窗口函数

SELECT u.name AS u_n, pn.date, SUM(pn.number) OVER(PARTITION BY pn.user_id ORDER BY pn.date) AS ps_num FROM passing_number pn JOIN user ...

2023-10-06

1 324

在SUM内使用IF

SELECT date, SUM(IF(rk=1, 1, 0)) AS new FROM( SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id) AS rk FROM login ) t1 GROUP...

2023-10-05

1 318

三表连接后分组和排序即可

SELECT de.dept_no, d.dept_name, t.title, COUNT(*) AS count FROM titles t JOIN dept_emp de ON t.emp_no = de.emp_no JOIN department...

2023-09-23

1 254

通过子查询单独查询出manager的薪水。貌似写的有点复杂

SELECT de.emp_no, t1.manager_no, s.salary AS emp_salary, t1.manager_salary FROM dept_emp de LEFT JOIN salaries s ON de.emp_no = s...

2023-09-22

1 245

题解 | 在子查询中使用窗口函数，计算每组工资排序rk

SELECT dept_no, emp_no, salary AS maxSalary FROM( SELECT de.emp_no, de.dept_no, s.salary, RANK() OVER(PARTITION BY de...

2023-09-20

1 290