将 ORDER BY 与 UNION 和 ALIASES 与复杂查询一起使用 [英] Using ORDER BY with UNION and ALIASES with complex query

问题描述

我想使用 UNION 对以下查询的 Order No 进行排序，但尽管在谷歌上搜索了很多，但还是无法完成.

I want to sort Order No of the following query with UNION but it could not be done although googling a lot.

IF LEN(@_Order_No)=0
BEGIN 
    SELECT a._PROCESS_INST_NO,c._ISSUE_DATE,c._DELIVERY_DATE, a._Order_No,a._GOODS_CD,a._GOODS_NAME ,
            --a._QTY,
            CAST(a._QTY  as DECIMAL (38,2))  as _QTY,
                b._GOODS_CD as RM_CD,b._GOODS_NAME as RM_NAME,b._UNIT,
            (CASE  
                WHEN  LEFT(a._GOODS_NAME,2)='SS' OR LEFT(a._GOODS_NAME,2)='SN'  THEN
                CAST(a._QTY as DECIMAL (38,2)) * CAST(b._QTY  as DECIMAL (38,2))/ NULLIF(SUM(CAST(a._QTY AS decimal(38,2) )) OVER (PARTITION BY  a._Order_No ,b._GOODS_CD),0) 
            ELSE
                CAST(b._QTY  as DECIMAL (38,2))
            END) as  RM_QTY
        FROM [ENVNDIVDB].[dbo].[TBL_PROC_PM] a
        INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PMS] b
        ON a._PROCESS_INST_NO=b._PROCESS_INST_NO 
        INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PH] c
        ON a._PROCESS_INST_NO=c._PROCESS_INST_NO
        WHERE c._DELIVERY_DATE BETWEEN @startDate AND @endDate

    UNION ALL

    SELECT a._PROCESS_INST_NO,c._ISSUE_DATE,c._DELIVERY_DATE, a._Order_No,a._GOODS_CD,a._GOODS_NAME ,
            --a._QTY,
            CAST(a._QTY  as DECIMAL (38,2))  as _QTY,
            b._GOODS_CD as RM_CD,b._GOODS_NAME as RM_NAME,b._UNIT,
            (CASE  
                WHEN  LEFT(a._GOODS_NAME,2)='SS' OR LEFT(a._GOODS_NAME,2)='SN'  THEN
                CAST(a._QTY as DECIMAL (38,2)) * CAST(b._QTY  as DECIMAL (38,2))/ NULLIF(SUM(CAST(a._QTY AS decimal(38,2) )) OVER (PARTITION BY  a._Order_No ,b._GOODS_CD),0) 
            ELSE CAST(b._QTY  as DECIMAL (38,2))
            END) as  RM_QTY
        FROM [ENVNDIVDB].[dbo].[TBL_PROC_PM] a
        INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PMS] b
        ON a._PROCESS_INST_NO=b._PROCESS_INST_NO 
        INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PH] c
        ON a._PROCESS_INST_NO=c._PROCESS_INST_NO
        WHERE c._DELIVERY_DATE BETWEEN @startDate AND @endDate

    SELECT '' as _PROCESS_INST_NO,'' as _ISSUE_DATE,'' as _DELIVERY_DATE, a.X_PO_NO as _Order_No,a.[X_GOODS_CD] as _GOODS_CD ,a.[X_GOODS_NAME1] as _GOODS_NAME ,
        CAST(X_QTY  as DECIMAL (38,2))  as _QTY,'' as RM_CD,'' as RM_NAME , '' as _UNIT,
            0.0 as RM_QTY
        FROM  [ENVIETNAMPO].[dbo].[_TBL_PO_M] a
END
ELSE
BEGIN 
    SELECT a._PROCESS_INST_NO,c._ISSUE_DATE,c._DELIVERY_DATE, a._Order_No,a._GOODS_CD,a._GOODS_NAME,CAST(a._QTY  as DECIMAL (38,2))  as _QTY,
            b._GOODS_CD as RM_CD,b._GOODS_NAME as RM_NAME,b._UNIT,
            (CASE  
                WHEN  LEFT(a._GOODS_NAME,2)='SS' OR LEFT(a._GOODS_NAME,2)='SN'  THEN
                CAST(a._QTY as DECIMAL (38,2)) * CAST(b._QTY  as DECIMAL (38,2))/ NULLIF(SUM(CAST(a._QTY AS decimal(38,2) )) OVER (PARTITION BY  a._Order_No ,b._GOODS_CD),0) 
            ELSE
                CAST(b._QTY  as DECIMAL (38,2))
            END) as  RM_QTY
        FROM [ENVNDIVDB].[dbo].[TBL_PROC_PM] a
        INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PMS] b
        ON a._PROCESS_INST_NO=b._PROCESS_INST_NO 
        INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PH] c
        ON a._PROCESS_INST_NO=c._PROCESS_INST_NO
        WHERE c._DELIVERY_DATE BETWEEN @startDate AND @endDate AND a._Order_No=@_Order_No

    UNION

    SELECT '' as _PROCESS_INST_NO,'' as _ISSUE_DATE,'' as _DELIVERY_DATE, a.X_PO_NO as _Order_No,a.[X_GOODS_CD] as _GOODS_CD ,a.[X_GOODS_NAME1] as _GOODS_NAME ,
            CAST(X_QTY  as DECIMAL (38,2))  as _QTY,'' as RM_CD,'' as RM_NAME , '' as _UNIT, 0.0 as RM_QTY
        FROM  [ENVIETNAMPO].[dbo].[_TBL_PO_M] a        
        WHERE a.X_PO_NO=@_Order_No
END

推荐答案

就这样:

SELECT _Order_No FROM (values(1), (2), (4), (6)) [first](_Order_No)
UNION 
SELECT _Order_No FROM (values(3), (7), (8), (5)) [second](_Order_No)
ORDER BY _Order_No;

输出:

_Order_No
-----------
1
2
3
4
5
6
7
8

这篇关于将 ORDER BY 与 UNION 和 ALIASES 与复杂查询一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！