我只能写到这样
SELECT PART_NO, CONTRACT,upper( LOCATION_NO ),TRANSACTION_CODE,DIRECTION,QUANTITY
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2
WHERE PART_NO = 'TAS-990F' and CONTRACT = '03' and upper( LOCATION_NO ) like upper( 'SM%' ) AND TRANSACTION_CODE IN('INVM-COIN','OESHIP')
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')
order by "DATED" desc;我想要的结果是'INVM-COIN','OESHIP'查询出的这两个事务的QUANTITY(数量)的和想减,该怎么写呢
SELECT PART_NO, CONTRACT,upper( LOCATION_NO ),TRANSACTION_CODE,DIRECTION,QUANTITY
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2
WHERE PART_NO = 'TAS-990F' and CONTRACT = '03' and upper( LOCATION_NO ) like upper( 'SM%' ) AND TRANSACTION_CODE IN('INVM-COIN','OESHIP')
and DATE_APPLIED>=to_date('&起始时间','yyyy-mm-dd') and DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd')
order by "DATED" desc;我想要的结果是'INVM-COIN','OESHIP'查询出的这两个事务的QUANTITY(数量)的和想减,该怎么写呢
-
sum(case when TRANSACTION_CODE='OESHIP'then quantity else 0 end)
-
sum(decode(TRANSACTION_CODE,'OESHIP',quantity,0))
(
ID VARCHAR2(5),
val NUMBER(4)
);INSERT INTO T14 VALUES('a', 1);
INSERT INTO T14 VALUES('a', 3);
INSERT INTO T14 VALUES('a', 5);
INSERT INTO T14 VALUES('a', 7);INSERT INTO T14 VALUES('b', 2);
INSERT INTO T14 VALUES('b', 4);
INSERT INTO T14 VALUES('b', 6);SELECT SUM(DECODE(ID, 'a', val, 'b', -val, 0))
FROM T14;