要求:新闻类型表(类型个数不确定)TbWordbook,和新闻表TbMessage,每种类型均返回六条最新的新闻,这样的SQL该怎么写?我使用的是mySQL数据库
解决方案 »
- 新手求前辈推荐一本学习mysql的书,要求从零开始讲解的,内容深刻的
- 千万级别mysql汉字查询如何更快?
- 一个GROUP BY的问题,在线等,急
- JSP+MYSQL4.1 无论写如或者读取都是乱码?试过许多方法都没用!!
- mysql 查询问题,想请教大家,帮下小弟
- mysql 中mid和substring 的区别是什么?
- 第一次用mysql就要用存储过程,大家帮帮忙!!!很急啊
- Mysql用于商业用途,要收费么?要多少?
- Postgresql:怎么才能知道已知表的主键???(三天之内给分!)
- 为什么我在代码里开了事务后,再调用mysql的存储过程,存储过程并不在我开启的事务里执行?
- MySQL中Max查最大值的用法
- 关于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不支持啊。