-- Create table
create table TEST_ZL
(
CONTRACT_ID VARCHAR2(32),
PROJECT_ID VARCHAR2(32),
AMOUNT NUMBER(8,2)
);
-- Add comments to the columns
comment on column TEST_ZL.CONTRACT_ID
is '合同ID';
comment on column TEST_ZL.PROJECT_ID
is '项目ID';
comment on column TEST_ZL.AMOUNT
is '金额';
-- 插入数据
insert into test_zl (CONTRACT_ID, PROJECT_ID, AMOUNT)
values ('001', 'aaa', 100.00);
insert into test_zl (CONTRACT_ID, PROJECT_ID, AMOUNT)
values ('001', 'bbb', 50.00);
insert into test_zl (CONTRACT_ID, PROJECT_ID, AMOUNT)
values ('001', 'ccc', 100.00);
insert into test_zl (CONTRACT_ID, PROJECT_ID, AMOUNT)
values ('002', 'dd', 200.00);
insert into test_zl (CONTRACT_ID, PROJECT_ID, AMOUNT)
values ('002', 'ee', 100.00);
insert into test_zl (CONTRACT_ID, PROJECT_ID, AMOUNT)
values ('003', 'ff', 50.00);
commit;
-- 功能:查询一个合同关联的所有项目中金额最大的合同ID、项目ID、金额,
-- 若一个合同有多个项目的金额相等,且为最大金额,则取其中任一条记录。
-- 问题:下面这个脚本执行报错,不知什么原因,麻烦修改下,或者再写个新的脚本,实现上面的功能。
-- 正确的执行结果应为:
-- 001 aaa 100.00
-- 002 dd 200.00
-- 003 ff 50.00
-- 或
-- 001 ccc 100.00
-- 002 dd 200.00
-- 003 ff 50.00
select t1.*
from test_zl t1
where t1.project_id = (select t2.project_id
from test_zl t2
where t2.contract_id = t1.contract_id
and rownum = 1
order by t2.amount desc)
order by t1.contract_id asc;
建表及相关功能描述见上面脚本,解决方案如下:
select *
from (select t.*,
row_number() over(partition by contract_id order by amount desc) rn
from test_zl t)
where rn = 1;