牛客网SQL答案与解析--基础1

SQL数据库实战题_面试必刷+解析_牛客题霸_牛客网【牛客题霸原创】SQL数据库实战题集，配有官方题解，数据分析岗位面试必考题，包含数据库查找、增加、删除等知识点。同时配有考试模式和练习模式，可切换练习。https://www.nowcoder.com/ta/sql

---1.查找最晚入职员工的所有信息  日期降序排列，日期最大，入职最晚
---LIMIT(m, n) 从第 m + 1 行开始取 n 条记录,
-- 入职最晚可能不止一个人，用最大值
select emp_no,birth_date,first_name,last_name,gender,hire_date 
from (select emp_no,birth_date,first_name,last_name,gender,hire_date
     ,rank()over(order by hire_date desc) posn 
      from employees)
where posn =1

select * from employees
where hire_date =(select max(hire_date) from employees)

SELECT * FROM employees order by hire_date desc limit 0,1

---2.查找入职员工时间排名倒数第三的员工所有信息

select emp_no,birth_date,first_name,last_name,gender,hire_date
from 
(select emp_no,birth_date,first_name,last_name,gender,hire_date
 ,rank()over(order by hire_date desc) posn
 from employees)
where posn =3;

select * from employees
order by hire_date desc limit 2,1;###替换写法 limit 1 offset 2

#子查询方法  或按 hire——date 分组
select * from employees
where hire_date =
(select distinct hire_date from employees
order by hire_date desc
limit 1 offset 2);

select *
from employees
where  hire_date=
(select min(a.hire_date)
from employees a,employees b    #----用条件语句进行表连接
where a.emp_no!=b.emp_no and a.hire_date<b.hire_date
group by a.emp_no
having count(*)<3)

---SQL3 查找当前薪水详情以及部门编号dept_no，以salaries.emp_no升序排序
-- to_date='9999-01-01'时，表示依然在职
select s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no 
from salaries s
join dept_manager d
on s.emp_no =d.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by s.emp_no asc;  
#salaries表中emp_no是按照升序排列的，所以用salaries做主表就可以省略order by

#因为存在换部门，甚至离职的情况，
#所以dept_manager.to_date='9999-01-01’是必须的

#salaries.emp_no, salaries.salary, salaries.from_date, 
#salaries.to_date是salaries表的所有字段，
#所以可以用salaries.*代替
select salaries.*, dept_manager.dept_no
from dept_manager, salaries      #----用条件语句进行表连接
where dept_manager.emp_no=salaries.emp_no
and dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'
order by emp_no;

-- SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no
SELECT e.last_name,e.first_name,d.dept_no
from employees e
JOIN dept_emp d
ON e.emp_no = d.emp_no 

SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e, dept_emp AS d
WHERE e.emp_no = d.emp_no;

-- SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no
SELECT e.last_name,e.first_name,d.dept_no
from employees e
left outer JOIN dept_emp d
ON e.emp_no = d.emp_no 

-- SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t
select emp_no,count(emp_no) t
from salaries
group by emp_no
having t>15

-- SQL8 找出所有员工当前薪水salary情况
select distinct salary 
from salaries
where to_date='9999-01-01'
order by salary desc

-- 对于distinct与group by的使用：
-- 1.当对系统的性能高并且数据量大时使用group by
-- 2.当对系统的性能不高时或者使用数据量少时两者借口
-- 3.尽量使用group by,大数据量的时候都禁止用distinct

select salary
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc;

-- SQL10 获取所有非manager的员工emp_no  #左连接+null
select e.emp_no
from employees e
left join dept_manager d
on e.emp_no = d.emp_no
where d.dept_no is null;

select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);

-- SQL11 获取所有员工当前的manager
select de.emp_no emp_no,dm.emp_no manager
from dept_emp de
left join dept_manager dm
on de.dept_no = dm.dept_no
where de.emp_no != manager
and dm.to_date ='9999-01-01';

select de.emp_no as emp_no, dm.emp_no as manager
from dept_emp de, dept_manager dm
where de.dept_no=dm.dept_no
and de.emp_no!=dm.emp_no
and dm.to_date='9999-01-01';

-- SQL12 获取每个部门中当前员工薪水最高的相关信息

-- 不需要求出emp_no(即只求所有部门中当前员工薪水最高值)，因为可能有重复的人
-- 则用INNER JOIN和GROUP BY和MAX即可解决，以下为错误答案：
select d.dept_no,d.emp_no,max(s.salary)
from salaries s
join dept_emp d
on s.emp_no = d.emp_no
group by d.dept_no

-- 关联子查询，外表固定一个部门，内表进行子查询
SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
INNER JOIN salaries as s1
ON d1.emp_no=s1.emp_no
AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'
WHERE s1.salary in 
     (SELECT MAX(s2.salary)
     FROM dept_emp as d2
     INNER JOIN salaries as s2
     ON d2.emp_no=s2.emp_no
     AND d2.to_date='9999-01-01'
     AND s2.to_date='9999-01-01'
     AND d2.dept_no = d1.dept_no # group by d2.dept_no 此处相当于分组
     )
ORDER BY d1.dept_no;

-- SQL21 查找在职员工自入职以来的薪水涨幅情况

select ts.emp_no,(ts.salary-fs.salary) growth
from (#员工入职薪水--在职
    SELECT e.emp_no,s1.salary 
    FROM salaries s1
    join employees e
    on s1.emp_no = e.emp_no
    where e.hire_date = s1.from_date
    and s1.emp_no IN
        (#在职员工编号
          select s3.emp_no
        from salaries s3
        where to_date = '9999-01-01')) fs
join (# 员工当前工资---在职  默认在职既有工资
    SELECT s2.emp_no,s2.salary 
    from salaries s2
    where to_date ='9999-01-01') ts
on ts.emp_no = fs.emp_no
order by growth;

-- 标准答案  两个左连接后再做内连接
-- 内层求工资表，用的左外连接，考虑有些新员工没有工资的实际情况。
-- 如果认为所有员工都有工资，则可以改成INNER JOIN
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT e.emp_no, s.salary
     FROM employees AS e
     LEFT JOIN salaries AS s
     ON e.emp_no=s.emp_no
     WHERE s.to_date='9999-01-01') AS sCurrent
INNER JOIN (SELECT e.emp_no, s.salary
          FROM employees AS e
          LEFT JOIN salaries AS s
          ON e.emp_no=s.emp_no
          AND s.from_date=e.hire_date) AS sStart
ON sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;

-- 连续内连接----注意连续连接的写法
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (employees AS e
    INNER JOIN salaries AS sCurrent
    ON e.emp_no=sCurrent.emp_no
    AND sCurrent.to_date='9999-01-01'
    INNER JOIN salaries AS sStart
    ON e.emp_no=sStart.emp_no
    AND sStart.from_date=e.hire_date)
ORDER BY growth ASC;

-- 仅用salaries表构建两个工资表  相关子查询的用法理解
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary
     FROM salaries AS s
     WHERE s.to_date=(SELECT to_date
                    FROM salaries
                    WHERE emp_no=s.emp_no
                    ORDER BY to_date DESC LIMIT 1)
     ) AS sCurrent,
     (SELECT s.emp_no, s.salary
     FROM salaries AS s
     WHERE s.from_date=(SELECT from_date
                    FROM salaries
                    WHERE emp_no=s.emp_no
                    ORDER BY from_date ASC LIMIT 1)
     ) AS sStart
WHERE sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;
-- 相关子查询写法2  
SELECT DISTINCT a.emp_no, (a.salary - b.salary) AS growth
FROM salaries AS a, salaries AS b
WHERE a.salary=(SELECT salary
    FROM salaries AS s
    WHERE s.emp_no=a.emp_no
    ORDER BY to_date DESC LIMIT 1)#未考虑必须是在职员工，所以结果有问题
AND b.salary=(SELECT salary
    FROM salaries AS s
    WHERE s.emp_no=a.emp_no
    ORDER BY to_date ASC LIMIT 1)
AND a.emp_no=b.emp_no
ORDER BY growth ASC;

-- 相关子查询写法3
SELECT DISTINCT emp_no,
(SELECT salary
FROM salaries
WHERE emp_no=a.emp_no
ORDER BY to_date DESC LIMIT 1)#未考虑必须是在职员工，所以结果有问题
-
(SELECT salary
FROM salaries
WHERE emp_no=a.emp_no
ORDER BY to_date ASC LIMIT 1) AS growth
FROM salaries as a
ORDER BY growth;

单表自连接
select s1.emp_no, s2.salary-s1.salary growth
from salaries s1
inner join salaries s2 on s1.emp_no = s2.emp_no
where s2.to_date='9999-01-01'
group by s1.emp_no  
order by growth asc
-- order by growth DESC
-- limit 1
替换后两行代码，原本答案有问题

-- SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息

select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (--#查询员工当前工资表 emp_sal
    select de.emp_no,de.dept_no,s1.salary as emp_salary
    from dept_emp de,salaries s1
    where de.emp_no=s1.emp_no
    and s1.to_date='9999-01-01'
    and de.to_date='9999-01-01'
    )as emp_sal
inner join(--#查询经理当前工资表mag_sal
    select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
    from dept_manager dm,salaries s2
    where dm.emp_no=s2.emp_no
    and s2.to_date='9999-01-01'
    and dm.to_date='9999-01-01'
    )as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;
-- 假设员工工资和经理工资表已经存在  一表多用
select de.emp_no,dm.emp_no as manager_no,
s1.salary as emp_salary,s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.salary>s2.salary
and s2.to_date='9999-01-01'
and s1.to_date='9999-01-01';

-- SQL26 汇总各个部门当前员工的title类型的分配数目

-- 连续内连接
SELECT de.dept_no,dep.dept_name,t.title,count(*)
from dept_emp de
join titles t
on de.emp_no = t.emp_no
join departments dep
on dep.dept_no =de.dept_no
group by dept_no,title
order by dept_no,title
---三表连接相当于连续内连接
select d.dept_no,
       d.dept_name,
       t.title,
       count(t.title)as count
from departments d,dept_emp de,titles t
where de.emp_no=t.emp_no
and de.dept_no=d.dept_no
and de.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by d.dept_no,t.title

-- 用窗口函数count()over()解决  此处分组问题在窗口函数中解决
select distinct d.dept_no, d.dept_name, t.title, 
count(t.emp_no)over(partition by t.title , de.dept_no)
from departments as d 
join dept_emp as de on d.dept_no = de.dept_no
join titles as t on t.emp_no = de.emp_no
where de.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
order by d.dept_no;

-- SQL70 牛客每个人最近的登录日期(五）次日留存率
-- 因为分母有可能为0，用ifnull(不为null时返回的值，为null时返回的值)
-- 用户登录日期是该用户登录日期最小值时该用户为初次登录用户，记为一个新用户
select date
        ,ifnull(round(
                       (sum(case when (user_id,date)in
                                 (select user_id,date_add(date,interval -1 day) 
                                 from login) #12号作为前一天有这个人，说明13号有这个人
                                           -- 把13号登录人员对应的日期，提前1天变成12号，
                                           -- 与12号登录的人员和日期进行匹配，
                                           -- 如果可以匹配上，说明12号登录的13号也登录了
                             and (user_id,date)in 
                                (select user_id,min(date)
                                   from login group by user_id)
                             then 1 
                             else 0 
                             end))/
                       (sum(case when (user_id,date)in
                               (select user_id,min(date)
                                   from login group by user_id)#当前日期新用户的特征是 当前日期=该用户所有登录日期的最小值
                           then 1 
                           else 0 
                           end))
                      ,3)
               ,0)as p
from login
group by date
order by date;

-- 新用户登录的日期的新用户留存率，用UNION补上没有新用户登录的日期
-- distinct 不能去掉，若同一用户在同一天用不同客户端登录，次日留存登录次数会多统计
-- 以a表为左连接保证每个新用户user_id都存在，只要是新用户，就对应留存率计算 
SELECT a.date,ROUND(COUNT(DISTINCT login.user_id)/ COUNT(a.user_id),3) AS p
FROM (SELECT user_id,MIN(date) AS date FROM login GROUP BY user_id) AS a
LEFT JOIN login
ON login.user_id=a.user_id
AND login.date=DATE_ADD(a.date,INTERVAL 1 DAY)
GROUP BY a.date

UNION
SELECT date,0.000 AS p
FROM login
WHERE date NOT IN(
     SELECT MIN(date) FROM login GROUP BY user_id)
ORDER BY date;

-- 所有表连接完后，select 查询字段，查询后每个表的相应字段都进行了相应的筛选

select t0.date,
ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0)
from
(select date from login group by date) t0
left join
(select user_id,min(date) as date from login group by user_id)t1
on t0.date=t1.date
left join login as t2
on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1
group by t0.date

-- SQL74 考试分数(三)
-- 每个岗位分数排名前2名的用户，
-- 得到的结果先按照language的name升序排序，
-- 再按照积分降序排序，最后按照grade的id升序排序，

select t.id,t.name,t.score
from
    (SELECT g.id,l.name,score
    ,dense_rank()over(partition by name order by score desc) posn
    from grade g
    left join language l
    on g.language_id = l.id) t
where posn<=2
order by t.name,t.score desc,t.id;

select g1.id, l.name, g1.score
from grade g1 
join language l on g1.language_id=l.id 
where 
(                 #https://blog.nowcoder.net/n/07c24f0c8b7f4f058013f3707ef58f3b
    select count(distinct g2.score) #满足条件的对应编号，用自连接的方式求出编号满足<=2
    from grade g2 
    where g2.score>=g1.score 
    and g1.language_id=g2.language_id
) <=2 
order by l.name,g1.score desc ,g1.id;

-- SQL76 考试分数(五)
-- 查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序
-- 用一条规则统一奇数个数时和偶数个数时的中位数位置。
-- 无论奇偶，中位数的位置距离（个数+1）/2 小于1
 -- abs()绝对值函数

select id, job, score,t_rank FROM
    (SELECT id, job, score
     , ROW_NUMBER()over(partition by job order by score desc) t_rank
     ,count(score)over(partition by job) num
    FROM grade
    order by id) g
where abs(t_rank-(num+1)/2)<1
#当总数为偶数时，中位数的开始序号，以及中位数的结束序号。
     -- case when count(score)%2=0 
     -- then ceiling(count(score)/2) 
     -- else ceiling(count(score)/2)
     -- end  as start1
     -- ,case when count(score)%2=0 
     -- then ceiling(count(score)/2+1) 
     -- else ceiling(count(score)/2)
     -- end as end1

-- floor()函数向下取整，ceiling()向上取整

-- 无论奇偶 都可以用两个函数找出中位数。
-- floor((count(*)+1)/2) floor((count(*)+2)/2)
-- 例如：floor((5+1)/2)=3、floor((5+2)/2)=3；
--      floor((6+1)/2)=3、floor((6+2)/2)=4
-- 中位数位置：
-- eg1.每个岗位(job)对应的成绩总个数为4，那么中位数位置为2，3；
-- eg2.每个岗位(job)对应的成绩总个数为5，那么中位数位置为3；
select
    x.id
    , x.job
    , x.score
    , x.t_rank
from
(   select id
    , job
    , score
    , row_number() over(partition by job order by score desc) AS t_rank
    , count(score) over(partition by job) AS cnt
    from grade
    ) AS x
where
    x.t_rank = floor((x.cnt+1)/2)
or
    x.t_rank = floor((x.cnt+2)/2)  # ceiling((x.cnt + 1)/ 2) 
order by
    x.id
;

select B.* from
(select job, 
 cast((count(id)+1)/2 AS INTEGER) as 'start' , 
 (cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end)) as 'end'
from grade 
group by job) A   #中位数的排名序号
 
JOIN
(select g1.*,
    (select count(distinct g2.score) #此种写法不对，SQL74 用 dense_rank()排序,此处中位数用row_number()
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as rank
from grade g1 ) B  #  含排序rank的表格
 
on (A.job=B.job  and B.rank between A.start and A.end)
order by B.id

-- SQL81 牛客的课程订单分析(五)
-- 写出一个sql语句查询在2025-10-15以后，
-- 如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，
-- 那么输出这个用户的user_id，
-- 以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date
-- 以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，
-- 以及购买成功的C++课程或Java课程或Python课程的次数cnt，
-- 并且输出结果按照user_id升序排序，

SELECT p1.user_id,p1.date,p2.date,p1.cnt
FROM (SELECT user_id,date,cnt
      from 
        (select user_id,date,
               ROW_NUMBER()over(partition by user_id order by date) posn,
               count(user_id)over(partition by user_id) cnt
        from order_info
        where product_name in ('C++','Java','Python')
        and status = 'completed'
        and date > '2025-10-15') t
        ####日期加单引号
        where posn =1
        and cnt>=2) p1 #首次购买
JOIN (SELECT user_id,date,cnt
      from 
        (select user_id,date,
               ROW_NUMBER()over(partition by user_id order by date) posn,
               count(user_id)over(partition by user_id) cnt
        from order_info
        where product_name in ('C++','Java','Python')
        and status = 'completed'
        and date > '2025-10-15') t
        where posn =2
        and cnt>=2) p2#第二次购买
on p1.user_id=p2.user_id
and p1.cnt=p2.cnt;

SELECT user_id,MIN(date),MAX(date),cnt
from 
(select user_id,date,
       ROW_NUMBER()over(partition by user_id order by date) posn,
       count(user_id)over(partition by user_id) cnt
from order_info
where product_name in ('C++','Java','Python')
and status = 'completed'
and date >= '2025-10-16') t
where posn =1 or posn=2
and cnt>=2
and date >= '2025-10-16'
group by user_id;

select user_id,
    min(date) as first_buy_date,
    min(下一个日期) as second_buy_date,#下一个日期的最小值为第二次购买的日期
    max(posn) as cnt
from (
    select *,
        row_number() over(partition by user_id order by date) as posn,
        lead(date,1) over(partition by user_id order by date) as 下一个日期
        #按user_id分组，按日期排序后的下一个日期
    from order_info
    where date>'2025-10-15'
    and status='completed'
    and product_name in ('Python','Java','C++')
    order by user_id) as t
group by user_id
having count(*)>=2
order by user_id;

###with的写法，re是新和成的表，写到select语句的前面
with re as
(
    select *,
       count(id) over(partition by user_id) as cnt,
       row_number() over(partition by user_id order by date) as t_rank
    from order_info
    where datediff(date,"2025-10-15")>0
     and status = "completed"
     and product_name in ("C++","Java","Python")
)
 
select user_id,
    min((case when t_rank=1 then date end))as first_buy_date,
    min((case when t_rank=2 then date end)) as second_buy_date,
    cnt
from re
where cnt>1
group by user_id

#SQL86 实习广场投递简历分析
-- 查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，
-- 和对应的2026年的同一个月同岗位，收到简历的数目，
-- 最后的结果先按first_year_mon月份降序，再按job降序排序显示

-- date_format(date, '%Y-%m')生成的字段（如：2025-03）不能使用month()函数来取出其月份，
-- 亦就是说，month()函数的参数只能是完整的【年-月-日】
select a.job, a.first_year_mon, a.first_year_cnt , b.second_year_mon, b.second_year_cnt
from
(select job ,
    concat(job,month(date)) as id ,###工作和月份均匹配
    DATE_FORMAT(date , '%Y-%m') first_year_mon ,
    sum(num) first_year_cnt
from resume_info
where date < '2026-01-01'and date > '2024-12-31'
group by job, first_year_mon)a
join
(select job ,
    concat(job,month(date)) as id ,
    DATE_FORMAT(date, '%Y-%m') second_year_mon ,
    sum(num) second_year_cnt
from resume_info
where date < '2027-01-01'and date > '2025-12-31'
group by job, second_year_mon)b
on a.id = b.id
order by first_year_mon desc , job desc;

-- with as 创建临时表
-- 本地临时表,以一个井号 (#) 开头的那些表名
-- 全局临时表,以两个井号 (##) 开头的那些表名
WITH r1 AS
(
SELECT id, job, date, DATE_FORMAT(date, '%Y-%m') AS first_year_mon, SUM(num) AS first_year_cnt
FROM resume_info
WHERE date LIKE '2025%'
GROUP BY job, first_year_mon
),
r2 AS
(
SELECT id, job, date, DATE_FORMAT(date, '%Y-%m') AS second_year_mon, SUM(num) AS second_year_cnt
FROM resume_info
WHERE date LIKE '2026%'
GROUP BY job, second_year_mon
)

SELECT r1.job,
first_year_mon,
first_year_cnt,
second_year_mon,
second_year_cnt
FROM r1 
inner join r2
on r1.job = r2.job 
AND DATE_FORMAT(r1.date, '%m') = DATE_FORMAT(r2.date, '%m')
ORDER BY first_year_mon DESC, r1.job DESC;

-- substr   mid   lift  right  几个函数的使用
-- year(date) = '2025'  是否加引号会影响结果

select t1.job, mon1 as first_year_mon, sum1 as first_year_cnt,
mon2 as second_year_mon, sum2 as secound_year_cnt
from 
    (select job, substr(date,1,7) as mon1, sum(num) as sum1
    from resume_info
    where year(date) = '2025'
    group by job, mon1) as t1
join
    (select job, substr(date,1,7) as mon2, sum(num) as sum2
    from resume_info
    where year(date) = '2026'
    group by job, mon2) as t2
on t1.job = t2.job and substr(mon1,5,7) = substr(mon2,5,7)
order by first_year_mon DESC, job DESC;

-- strftime('%m',date)  sql不支持此函数

select t1.job,t1.first_year_mon,t1.first_year_cnt,t2.second_year_mon,t2.second_year_cnt
from (
    select job,strftime('%Y-%m',date) as first_year_mon,strftime('%m',date)as mon1,sum(num) as first_year_cnt
    from resume_info
    where date>='2025-01-01' and date<='2025-12-31'
    group by job,first_year_mon) t1 
left join (
    select job,strftime('%Y-%m',date) as second_year_mon,strftime('%m',date) as mon2,sum(num) as second_year_cnt
    from resume_info
    where date>='2026-01-01' and date<='2026-12-31'
    group by job,second_year_mon) t2 
on t1.job=t2.job 
and t1.mon1=t2.mon2
order by t1.first_year_mon desc,t1.job desc;

--sql91查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，
-- 查询结果按照id升序排序

with t as 
(SELECT id,name,r.grade_sum,
       dense_rank()over(ORDER BY grade_sum desc) posn
       #按最大值排序，末句用最大值筛选，此排序可删除
from ( select user_id,
       SUM(case type when 'reduce' then -1*grade_num
                else grade_num # 用case when来修改正负号
                end) grade_sum
       from grade_info
       GROUP BY user_id) r
 inner join user
on user.id=r.user_id)
 
SELECT id,name,grade_sum
FROM t
where grade_sum=(select max(grade_sum)from t) ;##where posn=1

-- 用where 筛选替代上面的内连接   t 表格
select id,name,
       sum(case when type='add'then grade_num 
                else -grade_num 
                end)as grade_sum
from grade_info as g,user as u
where u.id=g.user_id
group by id

-- 临时表t的另一种写法，替换case when 的写法，
-- 注意ifnull的用法，如果为空，则取0值
-- IFNULL(expression, alt_value) 
-- 第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值
with t as (
    select A.user_id, (A.add_num - ifnull(A2.reduce_num, 0) ) grade_sum
    from (
        select user_id, sum(grade_num) add_num
        from grade_info
        where type = 'add'
        group by user_id
    ) A
    left join (
        select user_id, sum(grade_num) reduce_num
        from grade_info
        where type = 'reduce'
        group by user_id
    ) A2 on A2.user_id = A.user_id
)