跳到主要内容

SQL 必会：从 SELECT 到窗口函数

财务/审计岗的 SQL 不会考很深，但面试官会现场让你写。下面这些是高频中的高频。

一、执行顺序（先记住这个）​

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE 不能用聚合函数，HAVING 可以，这是最常被问的差别。

二、基础查询​

SELECT dept_id, COUNT(*) AS emp_cnt, AVG(salary) AS avg_salary
FROM employee
WHERE hire_date >= '2024-01-01'
GROUP BY dept_id
HAVING COUNT(*) >= 5
ORDER BY avg_salary DESC
LIMIT 10;

三、多表连接​

类型结果
INNER JOIN两表都匹配
LEFT JOIN左表全保留，右表无匹配为 NULL
RIGHT JOIN右表全保留
FULL JOIN两边都保留（MySQL 不支持，用 UNION 模拟）
SELECT v.voucher_no, v.amount, a.account_name
FROM voucher v
LEFT JOIN account a ON a.code = v.account_code
WHERE v.period = '2026-09';

财务数据最常见的坑：一对多连接导致金额翻倍。连接前先确认关联键是否唯一，必要时先聚合再 join。

四、聚合与条件聚合​

SELECT
  subject,
  SUM(CASE WHEN direction = '借' THEN amount ELSE 0 END) AS debit,
  SUM(CASE WHEN direction = '贷' THEN amount ELSE 0 END) AS credit
FROM journal_entry
GROUP BY subject;

条件聚合是「行列转换」的基本功，做试算平衡表、期间费用表都用它。

五、窗口函数（面试重点）​

SELECT
  emp_name,
  dept_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk,
  SUM(salary)  OVER (PARTITION BY dept_id) AS dept_total,
  salary / SUM(salary) OVER (PARTITION BY dept_id) AS pct
FROM employee;

三个排序函数的区别：

函数并列处理
ROW_NUMBER不并列，1,2,3,4
RANK并列后跳号，1,1,3,4
DENSE_RANK并列不跳号，1,1,2,3

其他常用：LAG/LEAD（同比环比）、SUM() OVER (ORDER BY ...)（累计）、FIRST_VALUE。

求每个部门的 Top 3​

SELECT * FROM (
  SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
  FROM employee
) t
WHERE rk <= 3;

六、日期处理（各家写法不同）​

-- MySQL
DATE_FORMAT(created_at, '%Y-%m') AS ym
DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

-- PostgreSQL
TO_CHAR(created_at, 'YYYY-MM')
DATE_TRUNC('month', created_at)

按月的同比、环比：

SELECT ym, amount,
       LAG(amount, 12) OVER (ORDER BY ym) AS last_year_same_month,
       amount - LAG(amount, 12) OVER (ORDER BY ym) AS yoy_diff
FROM monthly_sales;

七、去重与空值​

  • COUNT(*) 统计行数，COUNT(col) 忽略 NULL；
  • NULL 比较要用 IS NULL，= NULL 永远不成立；
  • COALESCE(col, 0) 把空值变 0，聚合时很重要；
  • DISTINCT 作用在整行，COUNT(DISTINCT col) 才是对单列去重。

八、面试常考的三个题​

  1. 找出重复的记录：
SELECT id_card, COUNT(*) AS c
FROM customer
GROUP BY id_card
HAVING COUNT(*) > 1;
  1. 删除重复保留最小 id：
DELETE FROM customer
WHERE id NOT IN (SELECT MIN(id) FROM customer GROUP BY id_card);
  1. 连续登录天数（经典题，用日期减序号）：
SELECT user_id, MIN(dt) AS start_dt, MAX(dt) AS end_dt, COUNT(*) AS days
FROM (
  SELECT user_id, dt,
         DATE_SUB(dt, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY dt) DAY) AS grp
  FROM login_log
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

九、练习建议​

  • 把「凭证-科目-余额」三张表自己建一遍，写 SQL 出试算平衡表；
  • 面试前手写 5 个：分组取 TopN、同比环比、去重、连续天数、行列转换。