sql - 带有子查询的 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);

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