最大:select date,count from tablename where count=(select max(count) from tablename )
最小:select date,count from tablename where count=(select min(count) from tablename
最小:select date,count from tablename where count=(select min(count) from tablename) 小了个括号:)
select * from (select date,min(count) from table group by date) a, (select date,max(count) from table group by date) b
select a.date,a.count from tablename a where a.count in(select max(b.count),min(b.count) from tablename b)
select date,count from table where count=(select min(count) from table) or count=(select max(count) from table)
如果不要重复,可以加上distinct 只有一条数据表示表中的数据都一样,或者只有1条
select (select date from tabName where count = (select min(count) from tblName) and rownum<2) as minDate, (select min(count) from tblName) as minCount, (select date from tabName where count = (select max(count) from tblName) and rownum<2) as maxDate, (select max(count) from tblName) as maxCount from dual; 其中tblName为你的实际表名 哈哈,搞定。
hevin() 的做法是对的,我试验过了 但感觉是太繁琐,希望能研究出较简单的方法啦:)
只是感觉上太繁琐,实际运行起来,这个SQL语句执行的速度应该不会太慢
创建试验表: create table mytest(col1 date,col2 number);插入如数据: begin for inti in 1..100 loop insert into mytest values(sysdate,inti); end loop; end;执行的语句: select a.col1,a.col2,b.col1,b.col2 from mytest a,mytest b where a.col2=(select max(col2) from mytest) and b.col2=(select min(col2) from mytest);执行结果: COL1 COL2 COL1 COL2 --------- ---------- --------- ---------- 11-AUG-04 100 11-AUG-04 1符合要求吧主要是用到了表的别名
where count=(select max(count) from tablename )
where count=(select min(count) from tablename
where count=(select min(count) from tablename)
小了个括号:)
(select date,min(count) from table group by date) a,
(select date,max(count) from table group by date) b
只有一条数据表示表中的数据都一样,或者只有1条
(select min(count) from tblName) and rownum<2) as minDate,
(select min(count) from tblName) as minCount, (select date from tabName where count =
(select max(count) from tblName) and rownum<2) as maxDate,
(select max(count) from tblName) as maxCount
from dual;
其中tblName为你的实际表名
哈哈,搞定。
但感觉是太繁琐,希望能研究出较简单的方法啦:)
create table mytest(col1 date,col2 number);插入如数据:
begin
for inti in 1..100 loop
insert into mytest values(sysdate,inti);
end loop;
end;执行的语句:
select a.col1,a.col2,b.col1,b.col2 from mytest a,mytest b where a.col2=(select max(col2) from mytest) and b.col2=(select min(col2) from mytest);执行结果:
COL1 COL2 COL1 COL2
--------- ---------- --------- ----------
11-AUG-04 100 11-AUG-04 1符合要求吧主要是用到了表的别名