如圖：同一個卡號，操作型別有贈送（OPERATION=18），有消費(OPERATION=15)，有退貨(operation=1)，我想查在一個時間點的余額（也就是virtulmoney的累加值相加減，余額公式是：贈送綜合-消費綜合+退貨總和）
比如我想查：卡號是6015902 在10月31號的余額是多少。該怎么寫呢。？謝謝
uj5u.com熱心網友回復：
SELECT SUM(DECODE(OPERATION, 18, VIRTUALMONEY, 0)) -SUM(DECODE(OPERATION, 15, VIRTUALMONEY, 0)) +
SUM(DECODE(OPERATION, 1, VIRTUALMONEY, 0))
FROM TABLE_NAME
WHERE CARDNO = '6015902'
AND OPDATE BETWEEN TO_DATE('20191031000000', 'YYYYMMDDHH24MISS') AND
TO_DATE('20191031235959', 'YYYYMMDDHH24MISS');
uj5u.com熱心網友回復：
感謝哈。好像很實用哦uj5u.com熱心網友回復：
能不能去掉具體的卡號，篩出所有的卡號，以卡號分組，只要給給一個時間，就能查出這個時間點所有卡的余額uj5u.com熱心網友回復：
SELECT CARDNO ,SUM(DECODE(OPERATION, 18, VIRTUALMONEY, 0)) -SUM(DECODE(OPERATION, 15, VIRTUALMONEY, 0)) +
SUM(DECODE(OPERATION, 1, VIRTUALMONEY, 0))
FROM TABLE_NAME
WHERE 1=1
AND OPDATE BETWEEN TO_DATE('20191031000000', 'YYYYMMDDHH24MISS') AND
TO_DATE('20191031235959', 'YYYYMMDDHH24MISS')
group by CARDNO ;
uj5u.com熱心網友回復：
SELECT CARDNO,SUM(DECODE(OPERATION, 18, VIRTUALMONEY, 0)) -
SUM(DECODE(OPERATION, 15, VIRTUALMONEY, 0)) +
SUM(DECODE(OPERATION, 1, VIRTUALMONEY, 0))
FROM TABLE_NAME
WHERE OPDATE BETWEEN TO_DATE('20191031000000', 'YYYYMMDDHH24MISS') AND
TO_DATE('20191031235959', 'YYYYMMDDHH24MISS')
GROUP BY CARDNO;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/202752.html
標籤：開發
上一篇：誰幫忙一個SQL問題