Sql优化实战

SELECT 
l.APPLIED_TRANSACTION_ID  as 子记录ID,
t.transaction_type as 子记录类型,
t.TRANID as 子记录订单号,
t.CREATE_DATE as 子记录创建时间,
l.ORIGINAL_TRANSACTION_ID  as 父记录ID,
t1.transaction_type as 父记录类型,
t1.TRANID as 父记录订单号,
t1.CREATE_DATE as 父记录创建时间
from transactions t 
left join TRANSACTION_LINKS l on t.TRANSACTION_ID = l.APPLIED_TRANSACTION_ID 
left join TRANSACTIONS t1 on t1.transaction_id = l.ORIGINAL_TRANSACTION_ID 
-- WHERE l.APPLIED_TRANSACTION_ID in (${id}) or l.ORIGINAL_TRANSACTION_ID in (${id})
where t.tranid in (${id}) or t1.tranid in (${id})

-- 把输入id作为子记录id的查询条件
SELECT 
l.APPLIED_TRANSACTION_ID  as 子记录ID,
t.transaction_type as 子记录类型,
t.TRANID as 子记录订单号,
t.CREATE_DATE as 子记录创建时间,
l.ORIGINAL_TRANSACTION_ID  as 父记录ID,
t1.transaction_type as 父记录类型,
t1.TRANID as 父记录订单号,
t1.CREATE_DATE as 父记录创建时间
from(
  SELECT *
  from transactions
  where tranid in (${id})
) t
left join TRANSACTION_LINKS l on t.TRANSACTION_ID = l.APPLIED_TRANSACTION_ID
left join transactions t1 on  t1.transaction_id = l.ORIGINAL_TRANSACTION_ID 
union
-- 把输入id作为父记录的查询条件
SELECT 
l.APPLIED_TRANSACTION_ID  as 子记录ID,
t.transaction_type as 子记录类型,
t.TRANID as 子记录订单号,
t.CREATE_DATE as 子记录创建时间,
l.ORIGINAL_TRANSACTION_ID  as 父记录ID,
t1.transaction_type as 父记录类型,
t1.TRANID as 父记录订单号,
t1.CREATE_DATE as 父记录创建时间
from(
  SELECT *
  from transactions
  where tranid in (${id})
) t1
left join TRANSACTION_LINKS l on t1.TRANSACTION_ID = l.ORIGINAL_TRANSACTION_ID
left join transactions t on t.TRANSACTION_ID = l.APPLIED_TRANSACTION_ID