SELECT sum(decode(TRANSACTION_CODE,'INVM-COIN',quantity,0))-sum(decode(TRANSACTION_CODE,'OESHIP',quantity,0)) 数量1
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where PART_NO = 'TAS-990F' and CONTRACT in('03','01','06') and upper( LOCATION_NO ) like upper( 'SM%' )
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')SELECT sum(QUANTITY) 数量2
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where TRANSACTION_CODE IN('OESHIP','INVM-ISS') and PART_NO = 'TAS-990F' and CONTRACT in('03','02')
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')这两个都是在一个表上查询的 我怎么把他们放在一个查询结果上呢
查询结果 像这样
数量1 数量2
5 35
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where PART_NO = 'TAS-990F' and CONTRACT in('03','01','06') and upper( LOCATION_NO ) like upper( 'SM%' )
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')SELECT sum(QUANTITY) 数量2
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where TRANSACTION_CODE IN('OESHIP','INVM-ISS') and PART_NO = 'TAS-990F' and CONTRACT in('03','02')
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')这两个都是在一个表上查询的 我怎么把他们放在一个查询结果上呢
查询结果 像这样
数量1 数量2
5 35
from
(SELECT sum(decode(TRANSACTION_CODE,'INVM-COIN',quantity,0))-sum(decode(TRANSACTION_CODE,'OESHIP',quantity,0)) 数量1
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where PART_NO = 'TAS-990F' and CONTRACT in('03','01','06') and upper( LOCATION_NO ) like upper( 'SM%' )
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )sub_query1,
(SELECT sum(QUANTITY) 数量2
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where TRANSACTION_CODE IN('OESHIP','INVM-ISS') and PART_NO = 'TAS-990F' and CONTRACT in('03','02')
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')
) sub_query2
from
(
SELECT sum(decode(TRANSACTION_CODE,'INVM-COIN',quantity,0))-sum(decode(TRANSACTION_CODE,'OESHIP',quantity,0)) q1,0
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where PART_NO = 'TAS-990F' and CONTRACT in('03','01','06') and upper( LOCATION_NO ) like upper( 'SM%' )
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')
union all
SELECT 0,sum(QUANTITY) q2
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where TRANSACTION_CODE IN('OESHIP','INVM-ISS') and PART_NO = 'TAS-990F' and CONTRACT in('03','02')
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')
)
数量1 5
数量2 35然后用行列转换..