牛客SQL-牛客题：256-288（不干了）

256. 写一个sql查询，积分表里面出现三次以及三次以上的积分。若有多个符合条件的number，则按number升序排序输出

SELECT `number`
FROM grade
GROUP BY `number`
HAVING COUNT(`number`) >= 3
ORDER BY `number`

1. WHERE 语句在 GROUP BY 语句之前；SQL会在分组之前计算 WHERE 语句。
2. HAVING 语句在 GROUP BY 语句之后；SQL会在分组之后计算 HAVING 语句。
3. 查询语句的 SELECTt 和 GROUP BY,HAVING 子句是聚组函数唯一出现的地方，在 WHERE 子句中不能使用聚组函数。

257. 输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列

SELECT id, `number`, DENSE_RANK() OVER(ORDER BY `number` DESC) AS t_rank
FROM passing_number
ORDER BY t_rank, id

258. 找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序

SELECT t1.id, t1.name, t2.content
FROM person t1 LEFT JOIN task t2 ON t1.id = t2.person_id -- 1. 没有任务的也要输出，不能用内联接; 2. 注意对应关系
ORDER BY t1.id

259. 写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序

SELECT
    t1.date,
    ROUND(AVG(CASE t1.type WHEN 'no_completed' THEN 1 ELSE 0 END), 3) AS p
#  ROUND(SUM(CASE WHEN type = 'no_completed' THEN 1 ELSE 0 END) / COUNT(*), 3) AS p
FROM email t1 LEFT JOIN user t2 ON (t1.send_id = t2.id)
                  LEFT JOIN user t3 ON (t1.receive_id = t3.id)
WHERE t2.is_blacklist = 0 AND t3.is_blacklist = 0
GROUP BY t1.date
ORDER BY t1.date

260. 写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序

# 思路：分组，max()求最大日期，再排序
SELECT user_id, MAX(`DATE`)
FROM login
GROUP BY user_id
ORDER BY user_id

/*
# MAX()窗口函数
SELECT DISTINCT user_id, MAX(date) OVER(PARTITION BY user_id) AS date
FROM login
ORDER BY user_id

# FIRST_VALUE() 窗口函数返回expression窗口框架第一行的值
SELECT
    DISTINCT user_id,
    FIRST_VALUE(`date`) OVER(PARTITION BY user_id ORDER BY `date` DESC) AS date
FROM login
*/

FIRST_VALUE()窗口函数返回expression窗口框架第一行的值：
FIRST_VALUE(expression) OVER ( [partition_clause] [order_clause] [frame_clause] )

261. 写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序

SELECT t2.name AS u_n, t3.name AS c_n, tmp.`date`
FROM (
    SELECT
        user_id,
        MAX(date) AS date
    FROM login
    GROUP BY user_id
) tmp INNER JOIN login t1 ON tmp.user_id = t1.user_id AND tmp.date = t1.date
      INNER JOIN user t2 ON (t1.user_id = t2.id)
      INNER JOIN client t3 ON (t1.client_id = t3.id)
ORDER BY u_n

/*
# 排序
select t2.name,t3.name,t1.date
from login t1
inner join user t2
on t1.user_id=t2.id
inner join client t3
on t1.client_id=t3.id
where (t1.user_id,t1.date )in(
select t.user_id,max(t.date) from login t group by t.user_id
)
order by t2.name,t3.name

# 先连表，在where子查询中得到每个用户最近的登录日期，连接主查询与子查询得到最终结果
select t2.name,t3.name,t1.date
from login t1
inner join user t2
on t1.user_id=t2.id
inner join client t3
on t1.client_id=t3.id
where (t1.user_id,t1.date )in(
select t.user_id,max(t.date) from login t group by t.user_id
)
order by t2.name,t3.name
*/

262.

288. 写出一个SQL，查找出当天的每个题单的刷题量，先按提交数量降序排序，如果提交数量一样的话，再按subject_id升序排序

注：当天(对，就是你现在写代码的这一天，实现原理就是后台有特殊程序会将'2999-02-22'这个东西变为今天的日期，并且将'2999-02-21'变为昨天的日期)

SELECT name, COUNT(*) AS cnt
FROM subject t2 LEFT JOIN submission t1 ON (t1.subject_id = t2.id)
# 不是很懂这里的双表联接了，两种都可以，不过按道理应该是 t1 left join t2，t1 作为主表
WHERE create_time = CURDATE() # CURDATE()：返回当前的日期，如:2022-10-24
GROUP BY name, subject_id # 一定要加上 subject_id，否则ORDER BY会报错
ORDER BY cnt DESC, subject_id