怎样结合这两句SQL？

[Eric_1999]

请问各位大虾，如果有个表可以计算出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实现？

[guanshiyu123]

union all

[guanshiyu123]

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 all

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)

[Eric_1999]

可以给个temple吗？

[Eric_1999]

只能这样吗？没有其他方法吗？

[Eric_1999]

这个不行啊，连接在表下面，不是在表右边加一列的阿

[struggle813]

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

[struggle813]

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

[Eric_1999]

谢了！这样不行。我自己解决了。
/* 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)

结贴！

[Eric_1999]

收工