如何编写SQL实现同ID分组下按标签配对规则查询ORDER_NUMBER

通用兼容写法（适配所有主流SQL数据库） #

通过自连接加UNION ALL拼接所有配对规则，无需数据库额外特性支持：

-- A与B配对
SELECT
  t1.Tag AS Tag_main,
  t2.Tag AS Tag_minor,
  t1.ORDER_NUMBER AS ORDER_NUMBER_main,
  t2.ORDER_NUMBER AS ORDER_NUMBER_minor
FROM order_tag t1
INNER JOIN order_tag t2 
  ON t1.ID = t2.ID 
  AND t1.Tag = 'A' 
  AND t2.Tag = 'B'

UNION ALL

-- B与C配对
SELECT
  t1.Tag AS Tag_main,
  t2.Tag AS Tag_minor,
  t1.ORDER_NUMBER AS ORDER_NUMBER_main,
  t2.ORDER_NUMBER AS ORDER_NUMBER_minor
FROM order_tag t1
INNER JOIN order_tag t2 
  ON t1.ID = t2.ID 
  AND t1.Tag = 'B' 
  AND t2.Tag = 'C'

UNION ALL

-- X与Y配对
SELECT
  t1.Tag AS Tag_main,
  t2.Tag AS Tag_minor,
  t1.ORDER_NUMBER AS ORDER_NUMBER_main,
  t2.ORDER_NUMBER AS ORDER_NUMBER_minor
FROM order_tag t1
INNER JOIN order_tag t2 
  ON t1.ID = t2.ID 
  AND t1.Tag = 'X' 
  AND t2.Tag = 'Y'

UNION ALL

-- Y与Z配对
SELECT
  t1.Tag AS Tag_main,
  t2.Tag AS Tag_minor,
  t1.ORDER_NUMBER AS ORDER_NUMBER_main,
  t2.ORDER_NUMBER AS ORDER_NUMBER_minor
FROM order_tag t1
INNER JOIN order_tag t2 
  ON t1.ID = t2.ID 
  AND t1.Tag = 'Y' 
  AND t2.Tag = 'Z'

-- 可选：按ID和主标签排序，和示例输出顺序完全一致
ORDER BY t1.ID, Tag_main

注意：请将代码中的order_tag替换为你实际的表名。

简化写法（适配MySQL8.0+、PostgreSQL、Oracle12c+） #

通过CTE定义配对映射表，后续新增配对规则只需修改映射表即可，维护成本更低：

WITH pair_map(main_tag, minor_tag) AS (
    VALUES ('A','B'), ('B','C'), ('X','Y'), ('Y','Z')
)
SELECT 
  t1.Tag AS Tag_main,
  t2.Tag AS Tag_minor,
  t1.ORDER_NUMBER AS ORDER_NUMBER_main,
  t2.ORDER_NUMBER AS ORDER_NUMBER_minor
FROM order_tag t1
INNER JOIN pair_map pm ON t1.Tag = pm.main_tag
INNER JOIN order_tag t2 ON t1.ID = t2.ID AND t2.Tag = pm.minor_tag
ORDER BY t1.ID, Tag_main

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