我有一个包含3列(项目,old_value,new_value)的表(STUFF)。我想检索所有值变化百分比最高的项目。以下查询产生“此处不允许使用组功能”错误。我该如何解决这个问题?
SELECT item,
old_value,
new_value,
highest_percentage
FROM (SELECT item, old_value, new_value,
(new_value - old_value) / old_value * 100 AS highest_percentage
FROM STUFF)
WHERE highest_percentage = MAX(highest_percentage);
这是桌子
CREATE TABLE STUFF
( item VARCHAR2(30) PRIMARY KEY,
old_value NUMBER(6, 2),
new_value NUMBER(6, 2));
INSERT INTO STUFF VALUES('E1', 0.62, 1.78);
INSERT INTO STUFF VALUES('B1', 0.80, 3.28);
INSERT INTO STUFF VALUES('B2', 2.72, 7.36);
INSERT INTO STUFF VALUES('M4', 2.70, 5.65);
INSERT INTO STUFF VALUES('T6', 5.70, 6.65);
INSERT INTO STUFF VALUES('R3', 4.00, 16.40);
INSERT INTO STUFF VALUES('G10', 8.00, 32.80);
理想情况下,我应该具有此输出。
ITEM OLD_VALUE NEW_VALUE HIGHEST_PERCENTAGE
------------------- --------- --------- ------------------
B1 0.80 3.28 310
G10 8.00 32.80 310
R3 4.00 16.40 310
在Oracle中它被称为Top-N查询,您可以这样操作:
select * from
(select * from XXX order by YYY desc)
where rownum < N;
根据您的最新更新-在这种情况下,标量子查询会有所帮助:
SELECT * FROM STUFF
WHERE (new_value - old_value) / old_value * 100 =
(SELECT max((new_value - old_value) / old_value * 100) AS highest_percentage
FROM STUFF)
为了避免额外的子查询:
SELECT * FROM
STUFF,
(SELECT max((new_value - old_value) / old_value * 100) AS percentage FROM STUFF) highest
WHERE (new_value - old_value) / old_value * 100 = highest.percentage ;
但是,分析功能似乎是最快的解决方案。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句