如何从股票历史价格表中筛选出首次达到10美元及以上的股票

你实际需要筛选的是首次价格≥10美元之后，所有后续价格都保持≥10美元的股票，注意你之前的思路“统计价格≥10的次数为1”存在偏差：示例中符合条件的BBB，价格≥10的记录共有2条，并非1次。

以下是不同场景下的简洁实现方案：

方案1：通用简洁写法（支持MySQL8.0+/PostgreSQL/Oracle等所有支持CTE的主流数据库） #

逻辑：先找到每只股票首次达到10美元的日期，再检查该日期之后是否存在价格跌破10美元的记录，不存在即为符合要求的股票。

WITH first_reach AS (
    -- 计算每只股票首次价格≥10的日期
    SELECT Stock, MIN(Date) AS first_ge10_date
    FROM t
    WHERE Price >= 10
    GROUP BY Stock
)
SELECT DISTINCT f.Stock
FROM first_reach f
-- 关联检查首次达标后是否有跌破10的记录
LEFT JOIN t ON f.Stock = t.Stock 
    AND t.Date > f.first_ge10_date 
    AND t.Price < 10
WHERE t.Stock IS NULL;

方案2：低版本数据库兼容写法（无需窗口函数/CTE） #

如果使用的是不支持CTE的低版本数据库（比如MySQL5.x），可以用纯聚合查询实现：

SELECT t1.Stock
FROM t t1
WHERE t1.Price >= 10
GROUP BY t1.Stock
HAVING MIN(t1.Date) > COALESCE((
    SELECT MAX(t2.Date) 
    FROM t t2 
    WHERE t2.Stock = t1.Stock AND t2.Price < 10
), '1970-01-01');

逻辑说明：如果某只股票首次≥10美元的日期，晚于它所有价格<10的记录的最大日期，就说明首次达标后再也没有跌破过10美元。COALESCE是为了兼容上市后价格从未低于10美元的股票场景。

方案3：窗口函数极简写法 #

SELECT DISTINCT Stock
FROM (
    SELECT 
        Stock,
        SUM(CASE WHEN Price < 10 THEN 1 ELSE 0 END) OVER (PARTITION BY Stock ORDER BY Date) AS lt10_cnt
    FROM t
) tmp
GROUP BY Stock
HAVING MAX(lt10_cnt) = MIN(lt10_cnt);

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