with t(id,col1,col2) as( select 1,1,1 from dual union all select 2,1,2 from dual union all select 3,2,3 from dual union all select 4,3,2 from dual union all select 5,1,2 from dual union all select 6,1,null from dual ) select id,col1,col2 from (select t.*, row_number() over (partition by col1 order by col2 desc nulls last) rn from t) where rn = 1;
楼上正解,通过row_number取得想要的记录。
需要这么麻烦吗? drop table if exists DB_TEST; create TABLE DB_TEST ( ID VARCHAR(64), field1 VARCHAR(64), field2 VARCHAR(64) );insert into db_test values('1','1','1'); insert into db_test values('2','1','2'); insert into db_test values('3','2','3'); insert into db_test values('4','3','2'); select max(id) from db_test group by field1
SELECT t1.ID FROM (SELECT A as A,MAX(B) as B FROM 表B GROUP BY A) t,表B t1 WHERE t.A=t1.A and t.B=t1.B; 结果:
select * from tab_name where (col1,col2) in (select t.col1,max(col2) from tab_name t group by t.col1);
select 1,1,1 from dual
union all select 2,1,2 from dual
union all select 3,2,3 from dual
union all select 4,3,2 from dual
union all select 5,1,2 from dual
union all select 6,1,null from dual
)
select id,col1,col2 from
(select t.*, row_number() over (partition by col1 order by col2 desc nulls last) rn from t)
where rn = 1;
drop table if exists DB_TEST;
create TABLE DB_TEST
(
ID VARCHAR(64),
field1 VARCHAR(64),
field2 VARCHAR(64)
);insert into db_test values('1','1','1');
insert into db_test values('2','1','2');
insert into db_test values('3','2','3');
insert into db_test values('4','3','2');
select max(id) from db_test group by field1
(SELECT A as A,MAX(B) as B FROM 表B GROUP BY A) t,表B t1
WHERE t.A=t1.A and t.B=t1.B;
结果:
in
(select t.col1,max(col2) from tab_name t group by t.col1);