表结构如下(TABLE1):
col1 col2 col3
AS01 3 OO
AS01 0 X1
AS01 3 X2
AS01 2 O1
......................
想在我要查询col1='AS01' 中最大值col2所在行的所有信息
即结果为:
col1 col2 col3
AS01 3 OO
AS01 3 X2除了用这样的SQL 外可有更简单的办法?
SELECT A.* FROM TABLE1
LEFT JOIN (SELECT COL1,MAX(COL2) COL2 FROM TABLE1 WHERE COL1='AS01' GROUP BY COL1 ) TABLE2
ON A.COL1=B.COL1 AND A.CLO2=B.COL2
WHERE A.CLO1='AS01'
col1 col2 col3
AS01 3 OO
AS01 0 X1
AS01 3 X2
AS01 2 O1
......................
想在我要查询col1='AS01' 中最大值col2所在行的所有信息
即结果为:
col1 col2 col3
AS01 3 OO
AS01 3 X2除了用这样的SQL 外可有更简单的办法?
SELECT A.* FROM TABLE1
LEFT JOIN (SELECT COL1,MAX(COL2) COL2 FROM TABLE1 WHERE COL1='AS01' GROUP BY COL1 ) TABLE2
ON A.COL1=B.COL1 AND A.CLO2=B.COL2
WHERE A.CLO1='AS01'
SELECT A.* FROM TABLE1
RIGHT JOIN (SELECT COL1,MAX(COL2) COL2 FROM TABLE1 WHERE COL1='AS01' GROUP BY COL1 ) TABLE2
ON A.COL1=B.COL1 AND A.CLO2=B.COL2
WHERE A.CLO1='AS01'
from
(SELECT table1.*
from table1
where col1='AS01'
order by col2 desc)
where rownum=1
from
(select col1,col2,col3, row_number() over(partition by col1 order by col2 desc) rn from table1 wheree col1='AS01' ) t
where rn=1
where not exists (select 1 from TABLE1 where col1=a.col1 and col2>a.col2)
SELECT col1,col2,col3
from
(SELECT col1,col2,col3,
ROW_NUMBER() OVER (ORDER BY col2 desc) AS rn
from table1
where col1='AS01')
where rn=1
SELECT table1.*
from table1
where col1='AS01'
and col2=(select max(col2) from table1 where col1='AS01'))
因为楼主在最大值有两行时
两行都要的select col1, col2, col3
from (select col1,
col2,
col3,
rank() over(partition by col1 order by col2 desc) rk
from table1) t
where rk = 1或者就用4楼的方法
4/9楼的都漏写where col1='AS01'条件
其他楼的则只显示一行记录
再次感谢大家的参与!
再次谢谢!
数据量很大的话用rank() 没有什么影响???
(COL1 VARCHAR2(10),
COL2 INT,
COL3 VARCHAR2(10)
);
INSERT INTO TABLE1 VALUES ('AS01', 3, 'OO');
INSERT INTO TABLE1 VALUES ('AS01', 0, 'X1');
INSERT INTO TABLE1 VALUES ('AS01', 3, 'X2');
INSERT INTO TABLE1 VALUES ('AS01', 2, 'O1');
COMMIT; SELECT COL1,
COL2,
COL3
FROM (SELECT COL1,
COL2,
COL3,
MAX(COL2) OVER(PARTITION BY COL1) MAX_COL2
FROM TABLE1) T
WHERE COL2 = MAX_COL2
we try it like this:SQL> select rowid,a.col1,cast(a.col2 as varchar2(20)) col2,a.col3 from zzw_test4
a;ROWID COL1 COL2 COL3
------------------ -------------------- ---------- --------------------
AAAM9zAAEAAAAEMAAA AS01 3 OO
AAAM9zAAEAAAAEMAAB AS01 0 X1
AAAM9zAAEAAAAEMAAC AS01 3 X2
AAAM9zAAEAAAAEMAAD AS01 2 O1SQL> select col1,cast(col2 as varchar2(20)) col2,col3 from zzw_test4 where rowid
in (select rowid from (select rowid,rank() over(order by col2 desc) rn from zzw
_test4) where rn=1);COL1 COL2 COL3
-------------------- ---------- --------------------
AS01 3 OO
AS01 3 X2SQL>
select t1.* from table1 t1 where exists (select * from (select max(col2), c2 col1 c1 from table1 group by col1) temp where temp.c2=t1.col2 and temp.c1=t1.col1)
逗号的位置错了
select t1.* from table1 t1 where exists (select * from (select max(col2) c2, col1 c1 from table1 group by col1) temp where temp.c2=t1.col2 and temp.c1=t1.col1) 测试
LXID NAME CNT UUID
---- ---------- ---- ------------------------------
1 红文具盒 2 AAAM6OAAHAAAABUAAA
2 铅笔 2 AAAM6OAAHAAAABUAAB
3 圆珠笔 2 AAAM6OAAHAAAABUAAC
4 蓝文具盒 2 AAAM6OAAHAAAABUAAD
1 红文具盒 1 AAAM6OAAHAAAABYAAA
2 铅笔 2 AAAM6OAAHAAAABYAAB
3 圆珠笔 3 AAAM6OAAHAAAABYAAC
4 蓝文具盒 4 AAAM6OAAHAAAABYAAD
3 圆珠笔 3 AAAM6OAAHAAAABYAAE
2 铅笔 2 AAAM6OAAHAAAABYAAF
4 蓝文具盒 4 AAAM6OAAHAAAABYAAGLXID NAME CNT UUID
---- ---------- ---- ------------------------------
1 红文具盒 1 AAAM6OAAHAAAABYAAH
已选择12行。
SQL> select t1.* from t05 t1 where exists (select * from (select max(cnt) c2, lx
id c1 from t05 group by lxid) temp where temp.c2=t1.cnt and temp.c1=t1.lxid) ;LXID NAME CNT UUID
---- ---------- ---- ------------------------------
1 红文具盒 2 AAAM6OAAHAAAABUAAA
2 铅笔 2 AAAM6OAAHAAAABUAAB
2 铅笔 2 AAAM6OAAHAAAABYAAB
3 圆珠笔 3 AAAM6OAAHAAAABYAAC
4 蓝文具盒 4 AAAM6OAAHAAAABYAAD
3 圆珠笔 3 AAAM6OAAHAAAABYAAE
2 铅笔 2 AAAM6OAAHAAAABYAAF
4 蓝文具盒 4 AAAM6OAAHAAAABYAAG已选择8行。
insert into table1 values('as01',3,'00');
insert into table1 values('as01',0,'X1');
insert into table1 values('as01',3,'X2');
insert into table1 values('as01',2,'01');
COMMIT;
select * from table1 t;
select *
from (select t.*,
rank() over(partition by t.col1 order by t.col2 desc) rn
from table1 t)
where rn = 1;
select max(col2) from table1 where col1= 'col1' )
select * from table1 t1 where not exists ( select 1 from table1 t2 where t2.col2 > t1.col2 ) and col1 = 'col1'