使用子查询的Having子句

Question

我有以下场景

CREATE TABLE plch_sales
(
   region      VARCHAR2 (100),
   product     VARCHAR2 (100),
   amount   NUMBER
)
/

INSERT INTO plch_sales VALUES ('North', 'Magic Wand',  1000);
INSERT INTO plch_sales VALUES ('North', 'Skele-Gro',   1000);
INSERT INTO plch_sales VALUES ('North', 'Timeturner ', 1000);
INSERT INTO plch_sales VALUES ('South', 'Portkey',     1000);
INSERT INTO plch_sales VALUES ('South', 'Quaffle',     1000);
INSERT INTO plch_sales VALUES ('West',  'Imperius',    1000);
INSERT INTO plch_sales VALUES ('West',  'Gringotts',   1000);
COMMIT;

为什么以下查询没有返回任何行？数据库是Oracle。

select region, sum(amount) sm_amount from PLCH_SALES group by region having sum(amount) > (select sum(amount)/3 from PLCH_SALES);

- Lokesh sahu

---

你在这里想要什么？ - Sujitmohanty30

我会说那是个错误... - sticky bit

这将返回行 .. > (select sum(amount)/3 + 0 from PLCH_SALES)。 - Serg

Answer 1

你需要先执行sum()，然后像下面这样除以3 -

SELECT region, SUM(amount) sm_amount
FROM PLCH_SALES
GROUP BY region
HAVING SUM(amount) > (SELECT SUM(amount)FROM PLCH_SALES)/3;

或者您可以尝试使用window()聚合函数

select distinct region,sm_amount from
(
select region,sum(amount) over(partition by region) as sm_amount, sum(amount) over()/3 as total
from plch_sales
)a where sm_amount>total

- Fahmi

---

我可以问一下为什么需要先执行sum()然后再除以3吗？原始查询为什么没有给出结果？ - Sujitmohanty30

@Sujitmohanty30，通常它应该返回OP的查询结果，但在检查了所有数据库后，我觉得它是特定于Oracle的某些内容 - 你可以在这里检查https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5629958971bb28a89155fcda77c7f716。 - Fahmi

是的，实际上我注意到另一件事情了。如果我们只是将相同的语句(select sum(amount)/3 + 0 from PLCH_SALES)放入选择值中，即使在OP的查询中也可以正常工作。这是奇怪的行为... - Sujitmohanty30

Answer 2

你所编写的方式无法与having子句一起使用。

有多种方法可以实现，例如使用公共表表达式（CTE）、父表或像下面这样：

select region, sum(amount) sm_amount 
from PLCH_SALES 
group by region 
having sum(amount) >(sum(amount)/3 )

;

- Ankit Agrawal

Answer 3

你的查询应该可以工作。子查询返回：

2333.333333333333333333333333333333333333

没有 having 的查询返回：

REGION  SM_AMOUNT
West    2000
North   3000
South   2000

很明显，'North' 应该在结果集中。

然而，在 Oracle 中却不是这样。这里 是一个 db<>fiddle。它可以通过子查询来实现：does

select x.*
from (select region, sum(amount) as sm_amount, (select sum(amount)/3 from PLCH_SALES) as thirds
      from PLCH_SALES
      group by region
     ) x
where sm_amount > thirds ;

它可以与CTE一起使用：

with thirds as (
      select sum(s2.amount)/3 as val from PLCH_SALES s2
     )
select region, sum(amount) sm_amount
from PLCH_SALES
group by region
having sum(amount) > (select val from thirds);

这听起来像一个 bug。

这个查询在其他数据库中可以正常工作：

• Postgres
• SQL Server
• MySQL

毫无疑问，任何尝试它的其他数据库也都没有问题。

- Gordon Linoff

---

你有看到我在Fahmi的答案上留言吗？如果我们把子查询放在select语句中，它在Oracle中也可以工作吗？那这是否意味着什么？我想不通。 - Sujitmohanty30

@Sujitmohanty30……我不认为你的问题是“如何编写逻辑”（尽管这回答了那个问题）。我理解它为“为什么你的代码不起作用”。答案似乎是Oracle中的一个错误。代码没问题；据我所知，在任何其他数据库中都可以工作。 - Gordon Linoff

不好意思，你误解了我的意思。我并不是在试图回答问题，而是在问你……当我把它放到select子句中时，这对你有什么意义吗？它可以正常工作。我自己无法弄清楚，这就是我需要你的意见的原因。但无论如何，感谢你的回复，最重要的是这似乎是一个bug。 - Sujitmohanty30