一个insert into的问题

Question

SQL语句如下
insert into tmp_pc_sell_match(event_id, wh_code, contract_id, qty)
           SELECT
                  'ONCE_DELI',                    
                  WH_CODE,
                  CONTRACT_ID,
                  SUM(PRE_DELIVERY_QTY) QTY
             FROM
                  A
            WHERE
                  CONTRACT_ID  = 'XXXX'
              AND DELIVERY_WAY = 1
              AND PRE_DELIVERY_QTY > 0
              AND WH_CODE <> '0'
         GROUP BY 'ONCE_DELI',WH_CODE,CONTRACT_ID        UNION ALL            SELECT 
                  'ONCE_DELI',                    
                  WH_CODE,
                  CONTRACT_ID,
                  SUM(PRE_DELIVERY_QTY) QTY
             FROM 
                  B
            WHERE
                  CONTRACT_ID = 'XXXX'
              AND PRE_DELIVERY_QTY > 0
         GROUP BY 'ONCE_DELI',  WH_CODE,CONTRACT_ID              
         ORDER BY QTY DESC, WH_CODE;
如果单独执行
           SELECT
                  'ONCE_DELI',                    
                  WH_CODE,
                  CONTRACT_ID,
                  SUM(PRE_DELIVERY_QTY) QTY
             FROM
                  A
            WHERE
                  CONTRACT_ID  = 'XXXX'
              AND DELIVERY_WAY = 1
              AND PRE_DELIVERY_QTY > 0
              AND WH_CODE <> '0'
         GROUP BY 'ONCE_DELI',WH_CODE,CONTRACT_ID        UNION ALL            SELECT 
                  'ONCE_DELI',                    
                  WH_CODE,
                  CONTRACT_ID,
                  SUM(PRE_DELIVERY_QTY) QTY
             FROM 
                  B
            WHERE
                  CONTRACT_ID = 'XXXX'
              AND PRE_DELIVERY_QTY > 0
         GROUP BY 'ONCE_DELI',  WH_CODE,CONTRACT_ID              
         ORDER BY QTY DESC, WH_CODE;
结果是
ONCE_DELI     000   XXXX   132
ONCE_DELI     094   XXXX   36
ONCE_DELI     116   XXXX   12一旦加上insert into插入的结果就变成了
ONCE_DELI     094   XXXX   36
ONCE_DELI     116   XXXX   12
ONCE_DELI     000   XXXX   132感觉是order by就没起作用，是什么原因呢？请大家帮助

Answer 1

你的order by 是你第二个select语句中的排序，跟你insert的排序无关！

Answer 2

oracle是堆组织表，就算你按顺序插入，
直接查询数据也不保证是插入的顺序。
要保证顺序，必须order by。

Answer 3

insert into tmp_pc_sell_match(event_id, wh_code, contract_id, qty)
select * from 
(
  SELECT
  'ONCE_DELI',   
  WH_CODE,
  CONTRACT_ID,
  SUM(PRE_DELIVERY_QTY) QTY
  FROM
  A
  WHERE
  CONTRACT_ID = 'XXXX'
  AND DELIVERY_WAY = 1
  AND PRE_DELIVERY_QTY > 0
  AND WH_CODE <> '0'
  GROUP BY 'ONCE_DELI',WH_CODE,CONTRACT_ID  UNION ALL  SELECT  
  'ONCE_DELI',   
  WH_CODE,
  CONTRACT_ID,
  SUM(PRE_DELIVERY_QTY) QTY
  FROM  
  B
  WHERE
  CONTRACT_ID = 'XXXX'
  AND PRE_DELIVERY_QTY > 0
  GROUP BY 'ONCE_DELI', WH_CODE,CONTRACT_ID   
)  
 ORDER BY QTY DESC, WH_CODE;