select a.id,a.cntid,a.subject,a.previewfile,a.producetime,b.name
from a,b
where a.status=1
and a.cntid=b.id
and a.producetime in (select distinct producetime from a
where rownum<6
order by producetime desc)
from a,b
where a.status=1
and a.cntid=b.id
and a.producetime in (select distinct producetime from a
where rownum<6
order by producetime desc)
解决方案 »
- 关于Oracle数据导入至Access中的问题
- 监听不能启动
- 特难的SQL,请高书帮助,急,跪求各位大峡!!!
- 跪求:如何不安装10g客户端,用IE访问FTP上的oracle10g数据库?
- 急!!!!!!!!!
- 简单的查询问题!!!!!!!
- datepart(year,GETDATE())转换到oracle怎么说
- 超级菜鸟问题:请问各位师兄,什么叫表空间?
- 如何最优取最近一条记录
- 关于 BITMAP index ?????
- 如果时间为:****-**-** 00:00:00的时候,用getString取出的时间为:****-**-** 00:00:00.0后面多了".0",其他的情况没问题
- 子表问题
where rownum<6
order by producetime desc)这个有问题.
from a,b
where a.status=1
and a.cntid=b.id
and a.producetime =(select max(producetime) from a )
from a,b
where a.cntid = b.id
and b.status = 1
and a.producetime in
(select a.producetime from a,b where a.cntid=b.id and b.status=1 having count(a.producetime)<=5 group by b.name order by a.producetime desc)
where rownum<6
order by producetime desc不好意思.刚才吃饭去了.我马上看看各位的回贴,成功了马上结贴.
A.cntid(+)=B.id 比較的全面
---------- ----------
SUBJECT
--------------------------------------------------------------------------------
PREVIEWFILE PRODUCETIM NAME
-------------------- ---------- --------------------
519 22
封侯拜相
040110xn000.jpg 17-1月 -04 新年祝福 520 22
恭贺新禧
040110xn001.jpg 17-1月 -04 新年祝福 521 22
恭喜发财
040110xn002.jpg 17-1月 -04 新年祝福 523 22
猴年行大运
040110xn004.jpg 17-1月 -04 新年祝福 522 22
猴年进步
040110xn003.jpg 17-1月 -04 新年祝福
没了,就上面五条??我是想每个类型都按要求取出五条来.
create table table_temp(
id number(9 ,
cntid number(4),
subject varchar2(100),
previewfile varchar2(20),
status number(1),
producetime date,
name varchar2(20));create or replace pro_testpro
as
is_cntid number(4);
cursor cur_cntid is
select distinct cntid from a;
begin
delete from table_temp;
open cur_cntid;
loop
fetch cur_cntid into is_cntid;
exit when cur_cntid%notfound;
insert into table_temp(
id ,
cntid ,
subject,
previewfile,
status ,
producetime ,
name )
select a.id,a.cntid,a.subject,a.previewfile,a.producetime,b.name
from a,b
where a.status=1
and a.cntid=is_cntid
and a.cntid=b.id
and a.producetime in (
select distinct producetime from a
where status=1
and cntid=is_cntid
and rownum<6
order by producetime desc)
end loop;
close cur_cntid;
commit;
end;
/
select a.id,a.cntid,a.subject,a.previewfile,a.producetime,b.name,
row_number() over (patition by cntid order by A.producetime desc) rank1
from A,B where a.status=1 and b.status=1 and a.id=b.id
)
where rank1<=5
union all
select * from (select a.id,a.cntid,a.subject,a.previewfile,a.producetime,b.name from a,b where A.cntid=B.id and a.status='1' and b.status='1' order by A.producetime desc) where rownum<6