SQL连接中筛选唯一配对——简化现有可行查询

你的核心需求是将同一用户下的多条注册表单与多条支付订单做一一唯一关联，原查询通过两次窗口函数过滤笛卡尔积的方式虽然能得到正确结果，但可以通过更直接的方式实现：

优化思路 #

分别给同一用户下的表单、订单生成排序后的行号，再通过行号+用户ID关联，跳过笛卡尔积生成步骤，逻辑更直观且性能更优。

优化后代码 #

-- registration form table
with forms as (
  select
    1 as user_id,
    'jeff' as nametag
  union all
  select
    1 as user_id,
    'john' as nametag
  union all
  select
    1 as user_id,
    'jim' as nametag
),

-- paid orders table
orders as (
  select
    1 as user_id,
    'abc' as order_id
  union all
  select
    1 as user_id,
    'def' as order_id
  union all
  select
    1 as user_id,
    'zyx' as order_id
),

ranked_forms as (
  -- 给用户的表单按名称排序生成行号
  select 
    *,
    row_number() over (partition by user_id order by nametag) as rn
  from forms
),
ranked_orders as (
  -- 给用户的订单按ID排序生成行号
  select 
    *,
    row_number() over (partition by user_id order by order_id) as rn
  from orders
)

-- 通过行号关联表单与订单
select 
  rf.user_id,
  rf.nametag,
  ro.order_id
from ranked_forms rf
inner join ranked_orders ro 
  on rf.user_id = ro.user_id 
  and rf.rn = ro.rn;

输出结果 #

优势对比 #

• 性能更优：原查询会先生成用户下表单×订单的笛卡尔积（3×3=9条数据）再过滤，优化后直接处理各自的数据集，数据量更小
• 逻辑更清晰：明确体现“同一用户下，表单按名称排序、订单按ID排序后一一匹配”的业务逻辑，可读性更强
• 代码更简洁：减少了嵌套层级，结构一目了然

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