解决方案 »
- 求两个VARCHAR类型字段的时间差,菜鸟,急等。
- 如果将一张表中的10万条数据导入另一张表,需要增量导入,每300条提交一次
- Oracle菜鸟问题
- 很简单的存储过程,怎么会编译错误?!
- ▲▲▲HP服务器win2000server环境下(2G内存,双CPU)安装oracle815建库时出错??▲▲▲
- 如何在Oracle中创建数据库?
- 触发器调用存储过程问题, 请高手帮忙!!!
- 懂oracle report6i 和 ERP modual 的高手 进来看一下
- 谁建过多个数据库实例,能说一说怎么做吗?
- 怎样取得某字段只有3个汉字的记录
- oracle 库备份成通用数据库类型如何去做。 500分
- 菜鸟问超简单的问题,关于同步数据的
select jh,rq,rcy,row_number()over(partition by jh order by rcy desc,rq)n from (
select 'BJ12' jh, '2010/07/20' rq, 200 rcy from dual
union all
select 'BJ12' jh, '2010/07/21' rq, 300 rcy from dual
union all
select 'BJ12' jh, '2010/07/22' rq, 100 rcy from dual
union all
select 'BJ12' jh, '2010/07/23' rq, 300 rcy from dual
union all
select 'SH10' jh, '2010/07/20' rq, 150 rcy from dual
union all
select 'SH10' jh, '2010/07/22' rq, 150 rcy from dual
union all
select 'TJ20' jh, '2010/07/23' rq, 120 rcy from dual
)) where n=1
with DBA01
as (select 'BJ12' jh, '2010/07/20' rq, 200 rcy from dual
union all
select 'BJ12' jh, '2010/07/21' rq, 300 rcy from dual
union all
select 'BJ12' jh, '2010/07/22' rq, 100 rcy from dual
union all
select 'BJ12' jh, '2010/07/23' rq, 300 rcy from dual
union all
select 'SH10' jh, '2010/07/20' rq, 150 rcy from dual
union all
select 'SH10' jh, '2010/07/22' rq, 150 rcy from dual
union all
select 'TJ20' jh, '2010/07/23' rq, 120 rcy from dual
),
DAA01
as ( select 'BJ12' jh , '2010/07/20' tcrq from dual
union all
select 'SH10' jh , '2010/07/20' tcrq from dual
union all
select 'TJ20' jh , '2010/07/23' tcrq from dual
)
SELECT DBA01.JH,DBA01.RQ,DBA01.RCY FROM DBA01,
(select DBA01.JH,DBA01.RQ,ROW_NUMBER() OVER(PARTITION BY DBA01.JH ORDER BY DBA01.RCY DESC,DBA01.RQ ASC) RN
from DBA01,DAA01 WHERE DAA01.JH=DBA01.JH ) TT WHERE DBA01.JH=TT.JH AND DBA01.RQ=TT.RQ AND TT.RN=1 ;
select t1.jh,t2.rq,t2.rcy,row_number()over(partition by t1.jh order by t2.rcy desc,t2.rq)n from
(
select 'BJ12' jh, '2010/07/20' TCRQ from dual
union all
select 'SH10' jh, '2010/07/20' TCRQ from dual
union all
select 'TJ20' jh, '2010/07/23' TCRQ from dual
)t1,(
select 'BJ12' jh, '2010/07/20' rq, 200 rcy from dual
union all
select 'BJ12' jh, '2010/07/21' rq, 300 rcy from dual
union all
select 'BJ12' jh, '2010/07/22' rq, 100 rcy from dual
union all
select 'BJ12' jh, '2010/07/23' rq, 300 rcy from dual
union all
select 'SH10' jh, '2010/07/20' rq, 150 rcy from dual
union all
select 'SH10' jh, '2010/07/22' rq, 150 rcy from dual
union all
select 'TJ20' jh, '2010/07/23' rq, 120 rcy from dual
)t2 where t1.jh = t2.jh) where n=1
还能优化吗~?我的DBA01中有1千万条数据,而分类表中只有DAA01有1百来条,我只想统计DBA01中今年的数据,用RQ怎么来加条件
WITH DBA01
as (select 'BJ12' jh, '2010/07/20' rq, 200 rcy from dual
union all
select 'BJ12' jh, '2010/07/21' rq, 300 rcy from dual
union all
select 'BJ12' jh, '2010/07/22' rq, 100 rcy from dual
union all
select 'BJ12' jh, '2010/07/23' rq, 300 rcy from dual
union all
select 'SH10' jh, '2010/07/20' rq, 150 rcy from dual
union all
select 'SH10' jh, '2010/07/22' rq, 150 rcy from dual
union all
select 'TJ20' jh, '2010/07/23' rq, 120 rcy from dual
)
SELECT *
FROM (SELECT JH,
RQ,
RCY,
ROW_NUMBER() OVER(PARTITION BY JH ORDER BY RCY DESC) RN
FROM DBA01) WHERE rn=1
as (select 'BJ12' jh, '2010/07/20' rq, 200 rcy from dual
union all
select 'BJ12' jh, '2010/07/21' rq, 300 rcy from dual
union all
select 'BJ12' jh, '2010/07/22' rq, 100 rcy from dual
union all
select 'BJ12' jh, '2010/07/23' rq, 300 rcy from dual
union all
select 'SH10' jh, '2010/07/20' rq, 150 rcy from dual
union all
select 'SH10' jh, '2010/07/22' rq, 150 rcy from dual
union all
select 'TJ20' jh, '2010/07/23' rq, 120 rcy from dual
),
DAA01
as ( select 'BJ12' jh , '2010/07/20' tcrq from dual
union all
select 'SH10' jh , '2010/07/20' tcrq from dual
union all
select 'TJ20' jh , '2010/07/23' tcrq from dual
)
SELECT DBA01.JH,DBA01.RQ,DBA01.RCY FROM DBA01,
(select DBA01.JH,DBA01.RQ,ROW_NUMBER() OVER(PARTITION BY DBA01.JH ORDER BY DBA01.RCY DESC,DBA01.RQ ASC) RN
from DBA01 WHERE exists (select null from DAA01 where DAA01.JH=DBA01.JH and to_number(substr(DAA01.tcrq,1,4))=2010)) TT
WHERE DBA01.JH=TT.JH AND DBA01.RQ=TT.RQ AND TT.RN=1 ;
WITH DBA01
as (select 'BJ12' jh, '2010/07/20' rq, 200 rcy from dual
union all
select 'BJ12' jh, '2010/07/21' rq, 300 rcy from dual
union all
select 'BJ12' jh, '2010/07/22' rq, 100 rcy from dual
union all
select 'BJ12' jh, '2010/07/23' rq, 300 rcy from dual
union all
select 'SH10' jh, '2010/07/20' rq, 150 rcy from dual
union all
select 'SH10' jh, '2010/07/22' rq, 150 rcy from dual
union all
select 'TJ20' jh, '2010/07/23' rq, 120 rcy from dual
)
select * from DBA01 a
where not exists (select 1 from DBA01 where jh=a.jh and rcy<a.rcy)