SQL如何查找多列满足指定条件的最早行，纵表无需转宽表的高效方案

你原有的窗口函数思路逻辑正确，但存在语法问题：SQL执行顺序中WHERE子句优先级高于窗口函数计算，无法直接在WHERE中引用窗口函数别名RN，需嵌套子查询/CTE实现，修正代码如下：

WITH ranked_data AS (
    SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY Status1, Status2 ORDER BY Date ASC) AS RN
    FROM your_table
)
SELECT * FROM ranked_data 
WHERE RN = 1
  AND Status1 = 'Happy' 
  AND Status2 = 'Sunny';

如果仅需查询Status1='Happy'且Status2='Sunny'的最早行，无需全量分区计算，可直接过滤后排序取第一行，执行效率更高：

SELECT * FROM your_table
WHERE Status1 = 'Happy' AND Status2 = 'Sunny'
ORDER BY Date ASC
LIMIT 1;

针对纵表（字段为ID、Date、Value、Type）的场景，可通过条件过滤+分组聚合直接查询，省去表结构转换的开销，逻辑为：

1. 先过滤出符合要求的两类记录：Type='Status1' AND Value='Happy'、Type='Status2' AND Value='Sunny'
2. 按ID、Date分组，筛选出同时存在两类记录的分组
3. 按Date升序排序取第一条即可得到最早出现的对应ID
   参考代码如下：

SELECT ID, MIN(Date) AS earliest_date 
FROM your_long_table
WHERE (Type = 'Status1' AND Value = 'Happy') 
   OR (Type = 'Status2' AND Value = 'Sunny')
GROUP BY ID, Date
HAVING COUNT(DISTINCT Type) = 2
ORDER BY earliest_date ASC
LIMIT 1;

如果需要返回该行的全量字段，可搭配窗口函数实现：

WITH matched_groups AS (
    SELECT ID, Date,
        ROW_NUMBER() OVER (ORDER BY Date ASC) AS RN
    FROM your_long_table
    WHERE (Type = 'Status1' AND Value = 'Happy') 
       OR (Type = 'Status2' AND Value = 'Sunny')
    GROUP BY ID, Date
    HAVING COUNT(DISTINCT Type) = 2
)
SELECT t.* FROM your_long_table t
JOIN matched_groups g ON t.ID = g.ID AND t.Date = g.Date
WHERE g.RN = 1;

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