yyyy11一

分类

/ 注册

全部文章（共87篇）

题解 | 考试分数(三)

select id,name,score from(select g.id,l.name,g.score, dense_rank()over(partition by l.name order by g.score desc) a from grade g join language l on l...

2025-11-28

0 22

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

SELECT A.date, SUM(CASE WHEN P=1 THEN 1 ELSE 0 END) AS new FROM (SELECT date,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) P FROM login) A GR...

2025-11-27

0 38

题解 | 顾客登录名

select cust_id,cust_name, UPPER(CONCAT(LEFT(cust_contact,2),LEFT( cust_city,3))) user_login FROM Customers

2025-11-26

0 25

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

select round(sum(case when l2.date = date_add(l1.date,interval 1 day) then 1 else 0 end)/count(distinct l1.user_id),3) as p from (select user_...

2025-11-24

0 39

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

select u.name u_n,c.name c_n,date from login l join user u on u.id=l.user_id join client c on c.id=l.client_id join (select user_id,max(date) max_dat...

2025-11-22

0 39

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

SELECT user_id, MAX(date) from login group by user_id ORDER BY user_id

2025-11-22

0 33

题解 | 异常的邮件概率

SELECT e.date AS email_date, -- 规范字段名，避免关键字冲突 ROUND( SUM(CASE WHEN e.type = 'completed' THEN 0 ELSE 1 END) -- 未完成邮件数 / NULLI...

2025-11-21

0 31

题解 | 出现三次以上相同积分的情况

select number FROM grade GROUP BY number --分组 HAVING COUNT(number)>=3 --筛选符合条件的组聚合函数不能用在where中 ORDER BY number-- 运行顺序FROM → GROUP BY → HAVING → SE...

2025-11-20

0 39

题解 | 给出employees表中排名为奇数行的first_name

SELECT first_name FROM ( SELECT emp_no, -- 保留原表的顺序标识（主键），关键！ first_name, ROW_NUMBER() OVER(ORDER BY first_name ASC) AS r_num -- 按 fir...

2025-11-19

0 20

题解 | 统计salary的累计和running_total

select emp_no,salary,SUM(salary) OVER (ORDER BY emp_no ) AS running_total FROM salaries WHERE to_date = "9999-01-01" 查询所有当前在职员工的最新薪资，并按员工编号（...

2025-11-18

0 25