如何在 SQL (Oracle) 中连接两个查询

Question

我如何加入这些查询？

SELECT RCTDT, SUM(RCTAMOUNT), COUNT(RCTAMOUNT) FROM RECEIPTS4
WHERE RCTDT BETWEEN '01-nov-2009' AND '30-nov-2009' 
AND RCTTYPE='CA' AND RCTAMOUNT>0
GROUP BY RCTDT

---

SELECT RCTDT, SUM(RCTAMOUNT), COUNT(RCTAMOUNT) FROM RECEIPTS4
WHERE RCTDT BETWEEN '01-nov-2009' AND '30-nov-2009' 
AND RCTTYPE='CQ' AND RCTAMOUNT>0
GROUP BY RCTDT

Answer 1

使用 IN 运算符：

SELECT RCTDT, SUM(RCTAMOUNT), COUNT(RCTAMOUNT) FROM RECEIPTS4
WHERE RCTDT BETWEEN '01-nov-2009' AND '30-nov-2009' 
AND RCTTYPE IN ('CA','CQ') AND RCTAMOUNT>0
GROUP BY RCTDT

Answer 2

也许你的意思是这个

SELECT RCTDT,
 SUM(case when rcttype='CA' then RCTAMOUNT else null end) as CASUM, 
 COUNT(case when rcttype='CA' then RCTAMOUNT else null end) as CACOUNT,
 SUM(case when rcttype='CQ' then RCTAMOUNT else null end) as CQSUM, 
 COUNT(case when rcttype='CQ' then RCTAMOUNT else null end) as CQCOUNT,
FROM RECEIPTS4
WHERE RCTDT BETWEEN '01-nov-2009' AND '30-nov-2009' 
AND RCTTYPE in('CA','CQ') AND RCTAMOUNT>0
GROUP BY RCTDT

Answer 3

在此特定示例中，按照 cletus 的建议使用 IN。在其他情况下，无法从同一选择生成两个结果集，请使用 UNION 组合结果：

SELECT something FROM somewhere
UNION
SELECT something FROM somewhere_else

Answer 4

从收据中选择 RCTDT、SUM(RCTAMOUNT)、COUNT(RCTAMOUNT)4
“2009 年 11 月 1 日”和“2009 年 11 月 30 日”之间的 RCTDT 位置
AND RCTTYPE IN ('CA','CQ') AND RCTAMOUNT>0
GROUP BY RCTDT

也很有用。