MySQL查询中使用union的技巧和注意事项

只看该作者 · 发表于 2017-11-8 17:20:22

把一张表的一条记录（通过不同的条件约束）变成两条记录时，一般会使用union来实现。但是需要注意的是，要给拆出来的两张表第一个字段附上不重复的ID，不然就会踩到坑。

SELECT sum(m_fetch_num), sum(m_return_num) FROM
(
      (
            SELECT concat('0_',order_id) as order_id,
            a.order_fetch_time,a.order_fetch_distanc,
            a.m_fetch_num,0 as m_return_num
            FROM dwm_flash_order_fetch_return as a
            WHERE m_fetch_num>0
      )
      UNION
      (
            SELECT concat('1_',order_id) as order_id,
            0 as order_fetch_time,0 as order_fetch_distanc,
            0 as m_fetch_num,a.m_return_num
            FROM dwm_flash_order_fetch_return as a
            WHERE m_return_num>0
      )
) x