SQL总和和两个查询减去问题

Question

好吧，我需要某人。我有以下两个查询：

SELECT DA.OWNG_OCD AS OFFICE, 'FL' AS STATE, SUM(S.STK_END_SEQ_NUM -
S.STK_STRT_SEQ_NUM) + COUNT(*) AS TOTSTK FROM STKRNG S, DFACCT DA, CMPNT C 
WHERE RANGE_USED_SW = 'N' AND S.DFTACCT_CANUM = DA.DFTACCT_CANUM
AND DA.OWNG_OCD = C.OCD AND C.ST = 'FL' AND S.STK_TYP = 'R' GROUP   
BY DA.OWNG_OCD;

和

SELECT C.OCD, COUNT(*) AS USED FROM DRAFT D
JOIN STKRNG S ON S.DFTACCT_CANUM = D.DFTACCT_CANUM
JOIN DFACCT DA ON S.DFTACCT_CANUM = DA.DFTACCT_CANUM
JOIN CMPNT C ON CMPNT.OCD = DA.OWNG_OCD
WHERE D.DRFT_SEQ_NUM >= (SELECT MIN(S.STK_STRT_SEQ_NUM) FROM STKRNG S 
WHERE D.DFTACCT_CANUM = S.DFTACCT_CANUM AND S.RANGE_USED_SW = 'N') 
AND  D.DRFT_SEQ_NUM <= (SELECT MAX(S.STK_END_SEQ_NUM) FROM STKRNG S WHERE   
D.DFTACCT_CANUM = S.DFTACCT_CANUM  AND S.RANGE_USED_SW = 'N')
AND S.STK_TYP = 'R'
AND S.RANGE_USED_SW = 'N'
AND C.ST = 'FL'
GROUP BY C.OCD;

我试图编写一个查询，其中第二次查询中的计数结果从第一个查询中的计数结果中减去。关于如何执行此操作的任何想法？

Answer 1

将您的查询放入最终查询的子句中：

select q1.totstk - q2.used
from ( <your first query here> ) q1
join ( <your second query here> ) q2 on q2.ocd = q1.office;

Answer 2

尝试这样的东西：

with STKRNGMINMAX as (
SELECT S.DFTACCT_CANUM, 
MIN(S.STK_STRT_SEQ_NUM) MINNUM, MAX(S.STK_END_SEQ_NUM) MAXNUM, 
SUM(S.STK_END_SEQ_NUM - S.STK_STRT_SEQ_NUM) DIFFNUM  
FROM STKRNG S 
WHERE (S.RANGE_USED_SW, S.STK_TYP)=('N', 'R')
group by S.DFTACCT_CANUM
)
SELECT C.OCD, S.DIFFNUM - COUNT(*) AS TOTSTK,  
FROM DRAFT D
INNER JOIN STKRNGMINMAX S ON S.DFTACCT_CANUM = D.DFTACCT_CANUM and D.DRFT_SEQ_NUM between S.MINNUM AND S.MAXNUM 
INNER JOIN DFACCT DA ON S.DFTACCT_CANUM = DA.DFTACCT_CANUM
INNER JOIN CMPNT C ON C.OCD = DA.OWNG_OCD and  C.ST='FL'
GROUP BY C.OCD;