表结构如下:
ID A B
1 1 11
2 1 14
3 2 16
4 3 18
5 2 15我执行select Table.A,Min(B)B group by A以后得到结果如下:A B
1 11
2 15
3 18
如果我还要加上他们原来所在行的ID值应该如何改写语句?即查询结果如下:ID A B
1 1 11
5 2 15
4 3 18
ID A B
1 1 11
2 1 14
3 2 16
4 3 18
5 2 15我执行select Table.A,Min(B)B group by A以后得到结果如下:A B
1 11
2 15
3 18
如果我还要加上他们原来所在行的ID值应该如何改写语句?即查询结果如下:ID A B
1 1 11
5 2 15
4 3 18
from table a inner join
(select Table.A,Min(B)B from table group by A) b
on a.a=b.a and a.b=b.b
insert into tb2 values(1, 1 , 11 );
insert into tb2 values(2, 1, 14 );
insert into tb2 values(3, 2, 16 );
insert into tb2 values(4, 3, 18 );
insert into tb2 values(5, 2 , 15 );
--select tb2.a,min(tb2.b) from tb2 group by tb2.a--第一种方式生成数据,采用oracle函数的方式生成
select a1.id, a1.a, a1.b
from(
select t.*,row_number() over(partition by a order by b asc) rn from tb2 t
)a1
where rn=1;
--第二种方式生成数据,采用子查询方式生成
select (select tb2.id from tb2 where tb2.b=a1.min_b and rownum=1) as id, a1.*
from(
select a.A,min(a.B) min_b from tb2 a group by a.a
)a1;在plsql里面运行,结果如下:
SQL> Table created1 row inserted1 row inserted1 row inserted1 row inserted1 row inserted ID A B
---------- ---------- ----------
1 1 11
5 2 15
4 3 18 ID A MIN_B
---------- ---------- ----------
1 1 11
5 2 15
4 3 18SQL>