select a.*,b.* from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type where 6>(select count(*) from TbMessage where msg_type=a.msg_type and edit_time>a.edit_time) order by a.msg_type,a.edit_time desc
select a.* from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type where EXISTS (select * from TbMessage where msg_type=a.msg_type order by a.edit_time desc limit 6) order by a.msg_type,a.edit_time desc
写错了,重发: select a.* from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type where EXISTS (select * from TbMessage where msg_type=b.msg_type order by edit_time desc limit 6) order by a.msg_type,a.edit_time desc
select a.* from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type where EXISTS (select * from TbMessage where msg_type=b.wb_type order by edit_time desc limit 6) order by a.msg_type,a.edit_time desc
谢谢楼上建议。主要是新闻表时间字段是timestamp类型的,这目前SQL测试通过了 但是我还有一张表也要实现类似效果,但它的时间类型是datetime类型。所以这个SQL用不起来。。我的数据库使用的是mySQL,以前在用Oracle时使用的是的方法如下 final String sql = "select * from (select distinct t.message_id,t.title,t.edit_date,t.message_Type,t.community_code, row_number() over (partition by t.message_type order by t.edit_date desc ) rn from tb_message t ,tb_wordbook tw where t.message_type = tw.wb_id and tw.wb_code in (" + messageType + ") and t.check_flg = 1 and Length(t.community_code) = "+ commLength +" ) where rn <= " + size;但是mysql不支持啊。
TbWordbook:
wb_id(主键)
wb_type
wb_code
wb_nameTbMessage:
msg_id(主键)
msg_type(外键TbWordbook.wb_type)
msg_title(标题)
edit_time(编辑日期)
...其他内容省略...例如TbMessage表中有如下记录
msg_id msg_type msg_title edit_time
1 1 标题一 18.03.333
2 1 标题二 18.03.444
3 2 标题三 18.03.555
4 2 标题四 18.03.666
5 3 标题五 18.03.777
6 4 标题六 18.03.888以下返回每种类型的唯一一条记录,其中每种类型均返回按编辑日期的最新的数据,类似如下:
2 1 标题二 18.03.444
4 2 标题四 18.03.666
5 3 标题五 18.03.777
6 4 标题六 18.03.888求SQL。
from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type
where 6>(select count(*) from TbMessage where msg_type=a.msg_type and edit_time>a.edit_time)
order by a.msg_type,a.edit_time desc
from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type
where EXISTS (select * from TbMessage where msg_type=a.msg_type order by a.edit_time desc limit 6)
order by a.msg_type,a.edit_time desc
select a.*
from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type
where EXISTS (select * from TbMessage where msg_type=b.msg_type order by edit_time desc limit 6)
order by a.msg_type,a.edit_time desc
from TbMessage a inner join TbWordbook b on a.msg_type=b.wb_type
where EXISTS (select * from TbMessage where msg_type=b.wb_type order by edit_time desc limit 6)
order by a.msg_type,a.edit_time desc
+--------+----------+-----------+-----------+
| msg_id | msg_type | msg_title | edit_time |
+--------+----------+-----------+-----------+
| 1 | 1 | 标题一 | 18.03.333 |
| 2 | 1 | 标题二 | 18.03.444 |
| 3 | 2 | 标题三 | 18.03.555 |
| 4 | 2 | 标题四 | 18.03.666 |
| 5 | 3 | 标题五 | 18.03.777 |
| 6 | 4 | 标题六 | 18.03.888 |
+--------+----------+-----------+-----------+
6 rows in set (0.00 sec)mysql>
mysql> select * from TbMessage a
-> where not exists (select 1 from TbMessage where msg_type=a.msg_type and edit_time>a.edit_time);
+--------+----------+-----------+-----------+
| msg_id | msg_type | msg_title | edit_time |
+--------+----------+-----------+-----------+
| 2 | 1 | 标题二 | 18.03.444 |
| 4 | 2 | 标题四 | 18.03.666 |
| 5 | 3 | 标题五 | 18.03.777 |
| 6 | 4 | 标题六 | 18.03.888 |
+--------+----------+-----------+-----------+
4 rows in set (0.08 sec)mysql>
where 6> (select count(*) from TbMessage where msg_type=a.msg_type and edit_time>a.edit_time);
msg_id msg_type msg_title edit_time
1 1 标题一 18.03.333
2 1 标题二 18.03.444
3 1 标题三 18.03.444
4 2 标题四 18.03.555
5 2 标题五 18.03.666
6 2 标题六 18.03.666
7 3 标题七 18.03.777
8 4 标题八 18.03.888 返回
msg_id msg_type msg_title edit_time
3 1 标题三 18.03.444
6 2 标题六 18.03.666
7 3 标题七 18.03.777
8 4 标题八 18.03.888 感激不尽。
+--------+----------+-----------+-----------+
| msg_id | msg_type | msg_title | edit_time |
+--------+----------+-----------+-----------+
| 1 | 1 | 标题一 | 18.03.333 |
| 2 | 1 | 标题二 | 18.03.444 |
| 3 | 1 | 标题三 | 18.03.444 |
| 4 | 2 | 标题四 | 18.03.555 |
| 5 | 2 | 标题五 | 18.03.666 |
| 6 | 2 | 标题六 | 18.03.666 |
| 7 | 3 | 标题七 | 18.03.777 |
| 8 | 4 | 标题八 | 18.03.888 |
+--------+----------+-----------+-----------+
8 rows in set (0.00 sec)mysql> select * from TbMessage a
-> where not exists (select 1 from TbMessage
-> where msg_type=a.msg_type and
-> (edit_time>a.edit_time
-> or
-> (edit_time=a.edit_time and msg_id>a.msg_id)
-> ) );
+--------+----------+-----------+-----------+
| msg_id | msg_type | msg_title | edit_time |
+--------+----------+-----------+-----------+
| 3 | 1 | 标题三 | 18.03.444 |
| 6 | 2 | 标题六 | 18.03.666 |
| 7 | 3 | 标题七 | 18.03.777 |
| 8 | 4 | 标题八 | 18.03.888 |
+--------+----------+-----------+-----------+
4 rows in set (0.06 sec)mysql>
但是我还有一张表也要实现类似效果,但它的时间类型是datetime类型。所以这个SQL用不起来。。我的数据库使用的是mySQL,以前在用Oracle时使用的是的方法如下
final String sql = "select * from (select distinct t.message_id,t.title,t.edit_date,t.message_Type,t.community_code, row_number() over (partition by t.message_type order by t.edit_date desc ) rn from tb_message t ,tb_wordbook tw where t.message_type = tw.wb_id and tw.wb_code in (" + messageType + ") and t.check_flg = 1 and Length(t.community_code) = "+ commLength +" ) where rn <= " + size;但是mysql不支持啊。