现在的需求是查询热门的关键字。
输入关键字查询出以此关键字开头的的前4条内容,中间包含这个关键字的3条内容,以关键字结尾的3条内容。
我现在写的SQL是这样的,很慢。。
希望可以帮我优化一下。--1.查询以关键字a开头的4条记录
select * from
(
(select tt.*,rownum,'1' rr from
(select t.tone_name, sum(t.order_times) c from t_rbt_search_tone t
where t.tone_type = '2' and (t.tone_name like 'a%' or t.py_tone_name like 'a%' or t.abb_tone_name like 'a%')
group by t.tone_name order by c desc) tt
where rownum < 5)
union
--2.查询中间包含关键字a的3条记录
(select tt.*,rownum,'2' rr from
(select t.tone_name, sum(t.order_times) c from t_rbt_search_tone t
where t.tone_type = '2' and (t.tone_name like '%a%' or t.py_tone_name like '%a%' or t.abb_tone_name like '%a%')
group by t.tone_name order by c desc) tt
where rownum < 4)
--3.查询以关键字a结尾的3条记录
union
(select tt.*,rownum,'3'as rr from
(select t.tone_name, sum(t.order_times) c from t_rbt_search_tone t
where t.tone_type = '2' and (t.tone_name like '%a' or t.py_tone_name like '%a' or t.abb_tone_name like '%a')
group by t.tone_name order by c desc) tt
where rownum < 4)
)
order by rr,c desc
;
输入关键字查询出以此关键字开头的的前4条内容,中间包含这个关键字的3条内容,以关键字结尾的3条内容。
我现在写的SQL是这样的,很慢。。
希望可以帮我优化一下。--1.查询以关键字a开头的4条记录
select * from
(
(select tt.*,rownum,'1' rr from
(select t.tone_name, sum(t.order_times) c from t_rbt_search_tone t
where t.tone_type = '2' and (t.tone_name like 'a%' or t.py_tone_name like 'a%' or t.abb_tone_name like 'a%')
group by t.tone_name order by c desc) tt
where rownum < 5)
union
--2.查询中间包含关键字a的3条记录
(select tt.*,rownum,'2' rr from
(select t.tone_name, sum(t.order_times) c from t_rbt_search_tone t
where t.tone_type = '2' and (t.tone_name like '%a%' or t.py_tone_name like '%a%' or t.abb_tone_name like '%a%')
group by t.tone_name order by c desc) tt
where rownum < 4)
--3.查询以关键字a结尾的3条记录
union
(select tt.*,rownum,'3'as rr from
(select t.tone_name, sum(t.order_times) c from t_rbt_search_tone t
where t.tone_type = '2' and (t.tone_name like '%a' or t.py_tone_name like '%a' or t.abb_tone_name like '%a')
group by t.tone_name order by c desc) tt
where rownum < 4)
)
order by rr,c desc
;
like '%a%'和like '%a'不用用索引,索引慢.
顶一个
where t.tone_type = '2' and (t.tone_name like '%a' or t.py_tone_name like '%a' or t.abb_tone_name like '%a')
group by t.tone_name order by c desc)
select * from ....使用with,利用tone_type = '2'的索引,唉