有一个这样的表字段 id a sj
1 15 2009-11-01
3 10 2009-11-01
22 13 2009-11-03
4 20 2009-11-01
44 20 2009-11-02
5 20 2009-11-03
求sql:得到的是每天a字段最大值和最大值的id、最小值值和最小值的id
1 15 2009-11-01
3 10 2009-11-01
22 13 2009-11-03
4 20 2009-11-01
44 20 2009-11-02
5 20 2009-11-03
求sql:得到的是每天a字段最大值和最大值的id、最小值值和最小值的id
解决方案 »
- Oracle下创建数据库的SQL命令(高分!)
- 我安装的oracle10.2在$ORACLE_HOME/rdbms/demo下怎么没有DEMO文件?如何获得?谢谢!
- 存储过程 备份数据库
- sql*plus中如何查看一个空表有哪些列
- 在安装oracle 10g 时,创建数据库时提示出错,错误是:Enterprise Manager 配置失败
- ◆发生了ORA-02063问题
- internal/oracle@catv无法登录?
- 简单问题:用什么命令查看库中的表清单?
- 怎样提高效率?大讨论!!
- 求问一个select (select a from table) from table的问题?
- Oracle连接问题!急>>急>>急
- oracle 如何查看隐含参数的值?
select *
from
(
select t.*,row_number() over(partition by sj order by a desc ) max_rn,row_number() over(partition by sj order by a ) min_rn from t
)
where max_rn=1 or min_rn=1
where not exists (select 1 from t t1 where t1.sj = t0.sj and (t1.a > t0.a or t1.a < t0.a) );
from
(select t0.* from t t0
where not exists (select 1 from t t1 where t1.sj = t0.sj and t1.a > t0.a)) m
(select t0.* from t t0
where not exists (select 1 from t t1 where t1.sj = t0.sj and t1.a < t0.a)) n
where m.sj = n.sj;这是字段写在一起的情况呵呵,提供几种可选做法, 算是补过。
sj maxid max minid min avg
2009-11-01 4 20 1 15 15
2009-11-01 44 20 44 20 20
sj maxid max minid min avg
2009-11-01 4 20 1 15 15
2009-11-02 44 20 44 20 20
还有你要的结果和你的描述不符
2009-11-01的minid和min应该是3,10
(select * from c t0
where not exists (select t1.sj from c t1 where t1.sj = t0.sj and t1.a > t0.a)) m,
(select * from c t0
where not exists (select t1.sj from c t1 where t1.sj = t0.sj and t1.a < t0.a)) n,
(select sj,avg(a) a from c group by sj) k
where m.sj = n.sj
and m.sj = k.sj;mysql 5测试通过,这是通用sql,也适用于oracle
(select * from c t0
where not exists (select t1.sj from c t1 where t1.sj = t0.sj and t1.a > t0.a)) m,
--这个子查询选出了最大值的记录
(select * from c t0
where not exists (select t1.sj from c t1 where t1.sj = t0.sj and t1.a < t0.a)) n,
--这个子查询选出了最小值的记录
(select sj,avg(a) a from c group by sj) k
--这个子查询求出了平均值
where m.sj = n.sj
and m.sj = k.sj;
--两个WHERE条件把三个子查询关联起来