MySql Union 立即合并到父表 ID

Question

提前致谢。

我正在尝试将两个表合并为一个。下面的查询返回我想要的结果。我知道 UNION ALL 将组合多个表。

但我想在其父行之后立即合并两个表行。

SELECT 
  temp.transactionId, 
  temp.amount, 
  temp.transactionType 
FROM 
  (
   (SELECT 
      sale.transaction_id AS transactionId, 
      sale.amount AS amount, 
      'SALE' AS transactionType 
    FROM 
     t_sales sale
    WHERE 
      sale.merchant_id = '1' 
   )

   UNION ALL

   (SELECT 
      refund.refund_transaction_id AS transactionId, 
      refund.amount AS amount, 
      'REFUND' AS transactionType 
   FROM 
      t_refunds refund  
   WHERE 
      refund.merchant_id = '1' 
   )
) 
AS temp

上述查询返回 sales 行 结束后的所有 refunds 行。但我需要在每个销售行之后立即显示所有退款行。退款表有销售表 ID(主键)。有什么办法可以实现吗？

Answer 1

这可以通过 id/sale_id 上的 order by 来完成。

试试这个:

SELECT 
  temp.transactionId, 
  temp.amount, 
  temp.transactionType 
FROM 
  (
   (SELECT 
      sale.transaction_id AS transactionId, 
      sale.amount AS amount, 
      'SALE' AS transactionType,
      id,
      1
    FROM 
     t_sales sale
    WHERE 
      sale.merchant_id = '1' 
   )

   UNION ALL

   (SELECT 
      refund.refund_transaction_id AS transactionId, 
      refund.amount AS amount, 
      'REFUND' AS transactionType,
      sale_id as id,
      2
   FROM 
      t_refunds refund  
   WHERE 
      refund.merchant_id = '1' 
   )
   ORDER BY 4,5
) 
AS temp