表:bank_msg_info字段:message_title(标题),create_date(日期),message_type_id(类型)
现在想得到:message_type_id(类型)分别为 1,2,3,4,5,6 同时每种类型按create_date(日期)倒序排序后的前6条记录这是自己写的一个SQL:
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='1' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='2' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='3' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='4' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='5' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='6' order by create_date desc) where rownum<=6
貌似效率有点低,忘各位坛友赐教 请上SQL
现在想得到:message_type_id(类型)分别为 1,2,3,4,5,6 同时每种类型按create_date(日期)倒序排序后的前6条记录这是自己写的一个SQL:
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='1' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='2' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='3' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='4' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='5' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='6' order by create_date desc) where rownum<=6
貌似效率有点低,忘各位坛友赐教 请上SQL
from
(select a.*,
row_number() over(partition by message_type_id order by create_date desc)rn
from bank_msg_info a
where message_type_id in ('1','2','3','4','5','6')
)b
where rn<=6
嗯 3Q10000条记录的时候 测试了两个SQL,貌似效率没有提升使用union all 0.047秒使用火龙果的SQL 0.092秒
create index idx_id on bank_msg_info(message_type_id)select message_title,create_date,message_type_id
from
(select message_title,create_date,message_type_id,
row_number() over(partition by message_type_id order by create_date desc,rowid) rn
from bank_msg_info
where message_type_id in('1','2','3','4','5','6')
) a
where a.rn<7
alter table bank_msg_info storage(buffer_pool default)
测试结果:bank_msg_info 表记录:6万条,message_type_id 有索引:select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='1' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='2' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='3' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='4' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='5' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='6' order by create_date desc) where rownum<=6耗时:
0.172秒-----------------------------------------------------------------------------SELECT x.*
FROM (SELECT message_title, create_date, message_type_id,
row_number() OVER(PARTITION BY message_type_id ORDER BY create_date desc) AS rn
FROM bank_msg_info where message_type_id in('1','2','3','4','5','6') ) x
WHERE x.rn <= 6;耗时:
0.45秒
运行你的SQL后 测试结果:bank_msg_info 表记录:6万条,message_type_id 有索引:select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='1' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='2' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='3' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='4' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='5' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='6' order by create_date desc) where rownum<=6耗时:
0.141秒-----------------------------------------------------------------------------SELECT x.*
FROM (SELECT message_title, create_date, message_type_id,
row_number() OVER(PARTITION BY message_type_id ORDER BY create_date desc) AS rn
FROM bank_msg_info where message_type_id in('1','2','3','4','5','6') ) x
WHERE x.rn <= 6;耗时:
0.40秒