leetcode的SQL 中等难度题176-1341

177第N高的薪水

ifnull 类似三目运算符即：ifnull?__ : __ -->ifnull(__ ,__ )
limit n,m n是开始的坐标，m是偏移量

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N -1;
if N < 0 then
RETURN null;
else
  RETURN (
      # Write your MySQL query statement below.
      select ifnull((select distinct salary from employee order by salary desc limit N,1),null) as getNthHighestSalary
  );
  end if;
END

178分数排名

SELECT a.Score, COUNT(DISTINCT b.Score) AS `RANK`
FROM Scores a, Scores b
WHERE a.Score <= b.Score
GROUP BY a.Id,a.Score
ORDER BY `RANK`;

为Scores表中的每个分数计算排名，其中排名规则是：分数相同则排名相同，且下一个不同的分数排名按实际位置跳跃（即“稠密排名”的变种，更接近竞赛中的排名规则）。

下面详细解释其逻辑：

表结构假设

假设Scores表的结构如下（常见设计）：

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

核心逻辑拆解

1. 表连接（FROM Scores a, Scores b）
   这是SQL中的隐式内连接，相当于FROM Scores a JOIN Scores b，会将表a（别名）中的每一行与表b（别名）中的每一行进行组合，形成“笛卡尔积”临时表。
   例如，a中Id=1（Score=3.50）的行，会与b中所有行（6行）组合，生成6条记录。

2. 条件过滤（WHERE a.Score <= b.Score）
   只保留满足“a的分数 ≤ b的分数”的组合。
   作用：对于a中的某个分数a.Score，筛选出所有分数≥它的b.Score（包括自身）。
   例如，a.Score=3.65时，b.Score可以是3.65、3.85、4.00（即所有不小于3.65的分数）。

3. 分组与计数（GROUP BY a.Id + COUNT(DISTINCT b.Score)）

   • GROUP BY a.Id：按a表的每一行（即每个原始分数）分组。
   • COUNT(DISTINCT b.Score)：统计每组中b.Score的不同值的数量。
     结合WHERE条件，这个数量本质是：有多少个不同的分数 ≥ 当前a.Score，这个数量就是当前分数的排名。

   例如：

   • 当a.Score=4.00时，b.Score中≥4.00的不同值只有4.00，因此COUNT(DISTINCT b.Score)=1 → 排名第1。
   • 当a.Score=3.65时，b.Score中≥3.65的不同值有3.65、3.85、4.00 → COUNT=3 → 排名第3。

4. 排序（ORDER BY RANK）
   最终结果按排名升序排列（即从高到低展示分数及其排名）。

执行结果示例

以上述表数据为例，执行结果如下：

Score	RANK
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

关键点总结

• 排名逻辑：分数越高，≥它的不同分数越少，排名数字越小（符合“第1名最高”的直觉）。
• 相同分数同排名：例如两个4.00，它们的RANK都是1。
• DISTINCT的作用：避免b中相同分数被重复计数（例如两个4.00在b中，只算1次）。

184. 部门工资最高的员工
利用in可以使用两个字段，且使用子查询

select d1.name as Department ,e1.name as Employee,e1.salary as Salary    from Employee e1,Department d1 where e1.departmentId =d1.id and (e1.departmentId ,e1.salary ) in
(select e.departmentId ,max(e.salary) as Salary   from Employee e group by e.departmentId);

1070. 产品销售分析 III

1. 在 SQL 中，当使用 GROUP BY 分组时，SELECT 子句中能直接出现的字段有严格限制：要么是 GROUP BY 中指定的分组字段，要么是被聚合函数（如 MIN、MAX、SUM 等）处理过的字段。
2. 当 GROUP BY 后面跟多个字段时，SQL 会按照这些字段的组合进行分组，即 “先按第一个字段分组，每个分组内再按第二个字段分组，以此类推”
3. HAVING 是 SQL 中用于过滤筛选分组后结果的子句，它与 WHERE 的作用类似，但适用场景不同：WHERE 用于筛选原始数据行，而 HAVING 用于筛选 **GROUP BY 分组后的结果 **。
4. HAVING 后面使用的函数不一定必须是 SELECT 子句中显式查询的字段或函数，但通常需要与分组逻辑相关（通常是基于分组后的聚合计算）。

SELECT s.product_id, s.year AS first_year, s.quantity, s.price
FROM Sales s
INNER JOIN (
  SELECT product_id, MIN(year) AS first_year 
  FROM Sales 
  GROUP BY product_id
) t ON s.product_id = t.product_id AND s.year = t.first_year;

570. 至少有5名直接下属的经理

select Employee.Name as Name
from (
  select ManagerId as Id
  from Employee
  group by ManagerId
  having count(Id) >= 5
) as Manager join Employee
on Manager.Id = Employee.Id