Oracle记录：Union查询两边的字段个数必须一样

Union查询两边的字段个数必须一样

SELECT *

  FROM (SELECT

        /*+ FIRST_ROWS(40) */

         PagedResult.*, ROWNUM ROWNO

          FROM (SELECT WB_PROC_DATE,

                       SITEM_CODE,

                       ITEM_NAME,

                       RECEIVER_NAME,

                       TEL,

                       MOBILE,

                       WBMSG,

                       ADDR,

                       AREA,

                       AREA_CODE,

                       OUT_SGROUP_CODE,

                       MSG

                  FROM (SELECT PU.INSERT_DATE AS WB_PROC_DATE,

                               TA.SITEM_CODE AS SITEM_CODE,

                               TA.ITEM_NAME AS ITEM_NAME,

                               (SELECT TC.CUST_NAME

                                  FROM TCUSTOMER TC

                                 WHERE TC.CUST_NO = PU.CUST_NO) AS RECEIVER_NAME,

                               (SELECT TEL

                                  FROM TCUSTPLACE

                                 WHERE CUST_NO = PU.CUST_NO

                                   AND TCUSTPLACE.PLACE_GB = '10'

                                   AND ROWNUM = 1) AS TEL,

                               (SELECT TEL

                                  FROM TCUSTPLACE

                                 WHERE CUST_NO = PU.CUST_NO

                                   AND TCUSTPLACE.PLACE_GB = '30'

                                   AND ROWNUM = 1) AS MOBILE,

                               TCODE_NAME('C004',

                                          (SELECT S.CUST_GRADE

                                             FROM tcustsystem S

                                            WHERE S.CUST_NO = PU.CUST_NO)) AS WBMSG,

                               (SELECT FUN_LONG_ADDR(POST_NO, POST_SEQ, ADDR) AS ADDR

                                  FROM TCUSTPLACE

                                 WHERE CUST_NO = PU.CUST_NO

                                   AND TCUSTPLACE.PLACE_GB = '10'

                                   AND ROWNUM = 1) AS ADDR,

                               TCODE_NAME('B895', PU.AREA_CODE) as AREA,

                               PU.AREA_CODE as AREA_CODE,

                               '' as OUT_SGROUP_CODE,

                               '' AS MSG

                          FROM TPREINSU PU, TITEM TA

                         WHERE TA.ITEM_CODE = PU.ITEM_CODE

                           AND TA.VEN_CODE = '100272' /**P*/

                           AND PU.ORDER_DATE >=

                               to_date('2019-01-17', 'YYYY-MM-DD') /**P*/

                           AND PU.ORDER_DATE <

                               to_date('2019-01-17', 'YYYY-MM-DD') /**P*/ +1

                        UNION ALL

                        SELECT M.INSERT_DATE AS WB_PROC_DATE,

                               SUBSTR(M.ITEM_CODE, 0, 6) AS SITEM_CODE,

                               M.ITEM_NAME AS ITEM_NAME,

                               M.CUST_NAME AS RECEIVER_NAME,

                               M.TEL AS TEL,

                               '' AS MOBILE,

                               TCODE_NAME('C004', M.CUST_GRADE) AS WBMSG,

                               (SELECT FUN_LONG_ADDR(POST_NO, POST_SEQ, ADDR) AS ADDR

                                  FROM TCUSTPLACE

                                 WHERE CUST_NO = M.CUST_NO

                                   AND TCUSTPLACE.PLACE_GB = '10'

                                   AND ROWNUM = 1) AS ADDR,

                               TCODE_NAME('B895', M.AREA_CODE) as AREA,

                               M.AREA_CODE as AREA_CODE,

                               M1.OUT_SGROUP_CODE as OUT_SGROUP_CODE,

                               M.VOC_NOTE AS MSG

                          FROM TCUSTVOCM M, runhs.TCUSTVOCM1 M1, TITEM T

                         WHERE 1 = 1

                           AND T.ITEM_CODE = M.ITEM_CODE

                           AND M.INSERT_DATE >=

                               to_date('2019-01-17', 'YYYY-MM-DD') /**P*/

                           AND M.INSERT_DATE <

                               to_date('2019-01-17', 'YYYY-MM-DD') /**P*/+1

                           AND T.VEN_CODE = '100272' /**P*/

                           AND m.voc_no = m1.voc_no

                           AND m.voc_seq = m1.voc_seq

                           AND m.out_lgroup_code = '21'

                           AND m.out_mgroup_code = '16'

                           --AND m1.out_sgroup_code in('01')   

                           --AND m1.out_sgroup_code in ('01', '02'))

                 ORDER BY WB_PROC_DATE DESC) PagedResult

         WHERE ROWNUM <= 100 /**P*/

         +1)

 WHERE ROWNO > 0 /**P*/

 

 

说明：

union查询就是把2条或者多条sql的查询结果合并成1个结果集。

 

例如：

 

    sql1查询N行，

 

    sql2查询M行。

 

    sql1 union sql2，返回M+N行（当且仅当sql1和sql2的查询结果集没有重复的情况下）。

 

union的注意事项：

 

 1、sql1和sql2要想使用union查询必须保证查询的字段数量一致，否则报错，当sql1与sql2的查询字段名称不一致的时候，返回的字段名称为前面的一条sql为标准，但内容却是各自查询的东西。

2、当union遇到完全相同的行时，将会被合并，合并是比较耗时的工作，一般不让union合并，使用“union all”可以避免。

3、union的子句中，不用写order by，合并后的结果集可以进行order by，子句order by没有意义。