怎样结合这两句SQL？

Question

请问各位大虾，如果有个表可以计算出n个月的生产总数，如何能只用1个SQL同时显示3个月和12个月的。如下：
3个月的SELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-3) 12个月的
SELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-12)怎样用一句SQL实现？

Answer 1

SELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-3) union allSELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-12)

Answer 2

select a.HKD_TOTAL_AMT,b.HKD_TOTAL_AMT from 
(SELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-3) ) a,
(
SELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-12)) b

Answer 3

select a.HKD_TOTAL_AMT "3月",b.HKD_TOTAL_AMT "12月" from 
(SELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-3) ) a,
(
SELECT 
ROUND(SUM(Suga_F_Ccy_Rate(SGP.PO_CCY, 'HKD','Corporate', TO_DATE('2006-01-01','YYYY-MM-DD'))*SGP.PO_UNIT_PRICE*SGP.ORDER_QUANTITY),4) HKD_TOTAL_AMT,
FROM SUGA_GROUP_PURCHASE_DETAILS SGP
WHERE SGP.ORDER_DATE  >   ADD_MONTHS( TRUNC(SYSDATE),-12)) b

Answer 4

谢了！这样不行。我自己解决了。
/* Formatted on 2006/07/26 14:02 (Formatter Plus v4.8.0) */
SELECT y.subsidary, y.ccy, y.supplier_name, x.hkd_total_amt hkd_total_amt_3,
       y.hkd_total_amt hkd_total_amt_12
  FROM (SELECT   sgp.subsidary, 'HKD' ccy, sgp.supplier_name supplier_name,
                 ROUND
                    (SUM (  apps.suga_f_ccy_rate (sgp.po_ccy,
                                                  'HKD',
                                                  'Corporate',
                                                  TO_DATE ('2006-01-01',
                                                           'YYYY-MM-DD'
                                                          )
                                                 )
                          * sgp.po_unit_price
                          * sgp.order_quantity
                         ),
                     4
                    ) hkd_total_amt
            FROM apps.suga_group_purchase_details sgp
           WHERE sgp.order_date > ADD_MONTHS (TRUNC (SYSDATE), -3)
        GROUP BY sgp.subsidary, sgp.supplier_name
        ORDER BY 1, 4 DESC) x,
       (SELECT   sgp.subsidary, 'HKD' ccy, sgp.supplier_name supplier_name,
                 ROUND
                    (SUM (  apps.suga_f_ccy_rate (sgp.po_ccy,
                                                  'HKD',
                                                  'Corporate',
                                                  TO_DATE ('2006-01-01',
                                                           'YYYY-MM-DD'
                                                          )
                                                 )
                          * sgp.po_unit_price
                          * sgp.order_quantity
                         ),
                     4
                    ) hkd_total_amt
            FROM apps.suga_group_purchase_details sgp
           WHERE sgp.order_date > ADD_MONTHS (TRUNC (SYSDATE), -12)
        GROUP BY sgp.subsidary, sgp.supplier_name
        ORDER BY 1, 4 DESC) y
 WHERE (x.subsidary(+) = y.subsidary AND x.supplier_name(+) = y.supplier_name)结贴！