网友问我的:请教;我有这样一组数据
a 1 2 3
a 4 5 6
b 1 2 3
b 4 5 6
写一条SQL查出
a组第三列最大的
b组第三列最大的
我的实验如下:
SQL> create table tab1222(a varchar2(10),n1 number,n2 number,n3 number);
Table created.
SQL> insert into tab1222 values('a',1,2,3);
1 row created.
SQL> insert into tab1222 values('a',4,5,6);
1 row created.
SQL> insert into tab1222 values('b',1,2,3);
1 row created.
SQL> insert into tab1222 values('b',4,5,6);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tab1222;
A N1 N2 N3
---------- ---------- ---------- ----------
a 1 2 3
a 4 5 6
b 1 2 3
b 4 5 6
SQL> select a,max(n3) from tab1222 group by a;
A MAX(N3)
---------- ----------
a 6
b 6
SQL> select * from tab1222 where n3 in (select a,max(n3) from tab1222 group by a);
select * from tab1222 where n3 in (select a,max(n3) from tab1222 group by a)
*
ERROR at line 1:
ORA-00913: too many values
SQL> select * from tab1222 tab1,(select a,max(n3) n3 from tab1222 group by a) tab2 where tab1.a=tab2.a and tab1.n3=tab2.n3;
A N1 N2 N3 A N3
---------- ---------- ---------- ---------- ---------- ----------
a 4 5 6 a 6
b 4 5 6 b 6
SQL> select tab1.a a,tab1.n1 n1,tab1.n2 n2,tab1.n3 n3 from tab1222 tab1,(select a,max(n3) n3 from tab1222 group by a) tab2 where tab1.a=tab2.a and tab1.n3=tab2.n3;
A N1 N2 N3
---------- ---------- ---------- ----------
a 4 5 6
b 4 5 6
OK,完成
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/119941/viewspace-721752/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/119941/viewspace-721752/