你这个b都是一样的select a,b,min(c) c from table group by a,b
a b c 1 x 2 1 x 3 2 y 2 2 y 3 SQL:select a,b,c from ( select a,b,c,row_number() over(partition by a,b order by a) rn from tt ) where rn=1RESULT: a b c 1 x 2 2 y 2
create table test_table (a char(2),b char(2),c char(2)); insert into test_table values ('1','x','2'); insert into test_table values ('1','x','3'); insert into test_table values ('2','y','2'); insert into test_table values ('2','y','3');查询语句一: select a,b,c from ( select a,b,c,row_number() over(partition by a,b order by a) rn from test_table) where rn=1;查询语句二: select a,b,min(c) c from test_table group by a,b;结果都一样: A B C -- -- -- 1 x 2 2 y 2
select a,b,min(c) c from test_table group by a,b; 或 select a,min(b) b,min(c) c from test_table group by a;
TRY IT .. SQL> SELECT * 2 FROM TABLE T1 3 WHERE EXISTS ( 4 SELECT 1 5 FROM TABLE T2 6 WHERE T1.A = T2.A 7 AND T1.C < T2.C 8 ); A B C ---------- - ---------- 1 X 2 2 Y 2SQL>
select a,b,min(c) from tbl 2 group by a,b;A B MIN(C) -- -- ------ 1 x 2 2 y 2SQL>
SQL> SELECT * 2 FROM TABLE T1 3 WHERE EXISTS ( 4 SELECT 1 5 FROM TABLE 6 WHERE T1.A = A 7 AND T1.C < C 8 ); 这样就行了
from table
group by a,b
a b c 1 x 2
1 x 3 2 y 2 2 y 3 SQL:select a,b,c from
(
select a,b,c,row_number() over(partition by a,b order by a) rn from tt
)
where rn=1RESULT:
a b c 1 x 2 2 y 2
insert into test_table values ('1','x','2');
insert into test_table values ('1','x','3');
insert into test_table values ('2','y','2');
insert into test_table values ('2','y','3');查询语句一:
select a,b,c from ( select a,b,c,row_number() over(partition by a,b order by a) rn from test_table) where rn=1;查询语句二:
select a,b,min(c) c from test_table group by a,b;结果都一样:
A B C
-- -- --
1 x 2
2 y 2
或
select a,min(b) b,min(c) c from test_table group by a;
SQL> SELECT *
2 FROM TABLE T1
3 WHERE EXISTS (
4 SELECT 1
5 FROM TABLE T2
6 WHERE T1.A = T2.A
7 AND T1.C < T2.C
8 ); A B C
---------- - ----------
1 X 2
2 Y 2SQL>
2 group by a,b;A B MIN(C)
-- -- ------
1 x 2
2 y 2SQL>
SQL> SELECT *
2 FROM TABLE T1
3 WHERE EXISTS (
4 SELECT 1
5 FROM TABLE
6 WHERE T1.A = A
7 AND T1.C < C
8 );
这样就行了