如何编写无需输入的SQL查询，返回同订单号下含update与create的记录

嘿，我明白你的需求了——你不需要手动指定订单号，想要自动找出所有同时存在update和create两种order_type的订单，然后返回这些订单的所有行对吧？我给你分享几种常用的方法，每种都有各自的适用场景，你可以根据自己的数据库和需求选择：

方法1：子查询+分组筛选（兼容性最强） #

这是最直观也最通用的方法，先找出符合条件的order_num，再关联回原表获取所有行：

SELECT order_num, order_type
FROM order_table
WHERE order_num IN (
    -- 子查询：找出同时有update和create的订单号
    SELECT order_num
    FROM order_table
    WHERE order_type IN ('update', 'create')
    GROUP BY order_num
    -- 用COUNT(DISTINCT)确保两种类型都存在，避免同类型重复导致误判
    HAVING COUNT(DISTINCT order_type) = 2
);

优点： #

• 几乎所有数据库都支持，兼容性拉满
• 逻辑清晰，新手也容易理解

注意点： #

• 如果你的业务保证同一个订单里不会重复出现同一个order_type，可以把COUNT(DISTINCT order_type)换成COUNT(*)，性能会更好，但如果有重复记录，必须用DISTINCT才准确。

方法2：窗口函数（可读性&性能兼顾） #

如果你的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server），这种方法更简洁，而且只需要扫描一次表：

SELECT order_num, order_type
FROM (
    SELECT 
        order_num, 
        order_type,
        -- 按订单分组，统计该订单下符合条件的order_type数量
        COUNT(DISTINCT order_type) OVER (PARTITION BY order_num) AS type_count
    FROM order_table
    WHERE order_type IN ('update', 'create')
) AS sub_query
-- 筛选出同时有两种类型的订单行
WHERE type_count = 2;

优点： #

• 代码可读性高，逻辑连贯
• 相比子查询，减少了一次表扫描，大表场景下性能更优

方法3：自连接（特殊场景下的性能选择） #

通过自连接表，确保同一个订单同时存在两种类型，最后去重得到结果：

SELECT DISTINCT ot1.order_num, ot1.order_type
FROM order_table ot1
JOIN order_table ot2
    ON ot1.order_num = ot2.order_num
WHERE 
    ot1.order_type IN ('update', 'create')
    -- 确保两个连接的行是不同的类型
    AND ot2.order_type = CASE 
        WHEN ot1.order_type = 'update' THEN 'create'
        ELSE 'update'
    END;

优点： #

• 如果order_num和order_type有合适的索引，某些数据库优化器能生成高效的执行计划

缺点： #

• 代码相对复杂，需要用DISTINCT避免重复行，额外增加一点开销

关于最佳实践 #

• 优先推荐窗口函数：如果你的数据库支持，它的可读性和性能都很出色，是现代SQL的首选方案
• 如果需要兼容旧版本数据库，子查询+分组筛选是最稳妥的选择
• 自连接一般只在特定性能调优场景下考虑，日常开发用前两种就足够啦

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