一个SQL 是否有优化的可能

[Thinking_In_IT]

表：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

[火龙果被占用了]

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) x
 WHERE x.rn <= 6;

[lw474580248]

[Quote=引用 1 楼 bao110908 的回复:]
SQL code
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_m……
[/Quote]

这个有用

[Thinking_In_IT]

[Quote=引用 1 楼 bao110908 的回复:]
SQL code
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_m……
[/Quote]

嗯 这个确实可以 ，就是少了个where 条件 呵呵

10000条记录的时候 测试了两个SQL，貌似效率没有提升

使用union all 0.047秒

使用火龙果的SQL 0.092秒

[Thinking_In_IT]

我再搞6万条 测试数据 ，再瞅瞅 过一会出最终结果

[xuyang840117]

还没弄到6W啊
期待着呢

[fanyuna]

学习了！帮顶。

[wzjmjx]

SQL code

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) x WHERE x.rn <= 6;



顶下

[wzjmjx]

我的都运行了100W条了阿

[Thinking_In_IT]

[Quote=引用 8 楼 wzjmjx 的回复:]
我的都运行了100W条了阿
[/Quote]

比对了两个SQL 的速度吗？~

[Thinking_In_IT]

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秒

[xuyang840117]

呵呵 你那个就挺好了 ~~
还期待有更好的吗

[Thinking_In_IT]

- - 我的那个SQL 看起来丑！

[xuyang840117]

图个颜色~

[crazylaa]

order by create_date desc。。。
在create_date列上建个索引吧。

[Thinking_In_IT]

下班之前 结贴

[火龙果被占用了]

因为在 message_type_id 条件用了 IN，那就不走索引了。

用 UNION ALL 的话会走索引的。

如果这样的话，就用你那个吧，基本上没有优化的余地了。

[silencexk212112]

关注中。。。。。