如何查询过滤冗余记录？优化保留最新n_month/n_year记录的复杂查询

嗨，我来帮你搞定这两个SQL问题，尤其是第二个场景，正好可以用更简洁的方案替代你现在冗长的UNION+JOIN组合～

过滤记录是SQL里最常用的操作，常用的方法有这些：

• 基础过滤用WHERE子句：这是最直接的方式，针对单条记录的条件过滤，比如排除状态为无效的记录、数值超出范围的记录：

  SELECT * FROM your_table WHERE status = 'valid' AND price BETWEEN 10 AND 100;
  

• 聚合后过滤用HAVING子句：如果是分组后的结果需要过滤（比如只保留订单数大于10的客户），就得用HAVING，它和WHERE的区别是HAVING作用于分组后的结果：

  SELECT customer_id, COUNT(order_id) AS order_count
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(order_id) > 10;
  

• 去重过滤用DISTINCT：如果要排除重复的记录组合，直接在SELECT后加DISTINCT：

  SELECT DISTINCT product_id, category FROM products;
  

• 关联排除用子查询/CTE：比如要排除在另一张表中存在的记录，用NOT EXISTS性能通常比NOT IN更好（尤其是大表场景）：

  SELECT u.*
  FROM users u
  WHERE NOT EXISTS (
      SELECT 1 FROM banned_users bu WHERE bu.banned_user_id = u.user_id
  );
  

针对你有n_month和n_year列的场景，要保留最新日期的记录，同时替换复杂的UNION/JOIN，这里有几个简洁高效的方案：

推荐方案：窗口函数（最简洁、易维护） #

窗口函数比如ROW_NUMBER()可以帮你给每条记录按日期排序标序号，然后只取序号为1的（也就是最新的那条/那些）。首先需要把n_year和n_month转换成可排序的日期值，不同数据库的转换函数略有不同：

• SQL Server：DATEFROMPARTS(n_year, n_month, 1)
• MySQL：MAKEDATE(n_year, n_month)
• Oracle：TO_DATE(n_year || '-' || n_month || '-01', 'YYYY-MM-DD')

假设你的表叫your_table，还有其他业务列（比如id, value），示例代码如下：

WITH ranked_records AS (
    SELECT 
        *,
        -- 按日期降序排序，最新的记录序号为1
        ROW_NUMBER() OVER (ORDER BY DATEFROMPARTS(n_year, n_month, 1) DESC) AS rn
    FROM your_table
)
-- 取出序号为1的记录，排除rn列
SELECT id, n_year, n_month, value  -- 部分数据库支持用SELECT * EXCEPT (rn)简化
FROM ranked_records
WHERE rn = 1;

如果是按分组保留每个组的最新记录（比如每个产品的最新月度数据），只需要在OVER()里加PARTITION BY指定分组列：

ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY DATEFROMPARTS(n_year, n_month, 1) DESC) AS rn

这个方案完全不需要复杂的UNION和JOIN，代码可读性拉满，而且性能通常比多表关联更好。

备选方案：子查询获取最新日期后过滤 #

先找到表中最大的年份，再找到该年份下最大的月份，然后过滤出符合这个年月组合的记录：

SELECT *
FROM your_table
WHERE (n_year, n_month) = (
    -- 先找最大的年份，再找该年份下的最大月份
    SELECT MAX(n_year), MAX(n_month)
    FROM your_table
    WHERE n_year = (SELECT MAX(n_year) FROM your_table)
);

这个方案适合不支持窗口函数的老版本数据库，但逻辑上比窗口函数稍复杂一点。

简单场景方案：TOP/LIMIT（仅适用于单条最新记录） #

如果你的最新日期只有一条记录，可以直接按日期降序排序后取第一条：

-- SQL Server
SELECT TOP 1 *
FROM your_table
ORDER BY DATEFROMPARTS(n_year, n_month, 1) DESC;

-- MySQL/PostgreSQL
SELECT *
FROM your_table
ORDER BY MAKEDATE(n_year, n_month) DESC
LIMIT 1;

但如果同一个最新日期有多个记录，这个方法只会取一条，所以更适合单条记录的场景。

内容的提问来源于stack exchange，提问作者okorng