刚在网上看到的
题目是这样的: 表 User userid bigint
username varchar 表Message messageId bigint
fromUserid bigint
toUserid bigint
sendtime date 这是个发送聊天信息相关的两个表,现写出SQL语句,求出username = ‘TestUser’这个人最近联系的10个人(包括发送的和接受的人) 各位看看,该怎么写这个SQL
题目是这样的: 表 User userid bigint
username varchar 表Message messageId bigint
fromUserid bigint
toUserid bigint
sendtime date 这是个发送聊天信息相关的两个表,现写出SQL语句,求出username = ‘TestUser’这个人最近联系的10个人(包括发送的和接受的人) 各位看看,该怎么写这个SQL
解决方案 »
- 请教:java用openoffice将doc,ppt等文档转换为pdf文件,再将pdf文件转成swf文件,最后用flexplayer播放
- struts2
- 怎么将.js文件中获得的颜色变量值传给.jsp中的变量?????
- jsp打包安装
- 我在Struts中的ActionForm中的问题,谢谢各位大哥了.(急)
- 关于哈希表的问题!
- 在jsp爷里要做一个四级连动的菜单,怎么做效率高?
- jsp如何从多行xml记录中去数据放到数据库中?
- sql中怎么获得当前记录?
- javaweb的项目,公司买的框架,现在项目下所有的jsp都报错Unable to compile class for JSP,求大神解惑
- 前辈们不要路过啊,帮帮忙……
- java.lang.IllegalStateException
select u.userid, u.username , m.fromUserid, m.toUserid , m.sendtime from t_user u , Message m
where u.username = 'test1' and m.fromUserid = u.userid
and rownum < 11
order by m.sendtime desc ;
select *
from (select *
from (select t.touserid, max(t.entrydate)
from message t, t_user u
where t.formuserid = u.userid
and u.username = 'TestUser'
group by t.touserid
union
select t.formuserid, max(t.entrydate)
from message t, t_user u
where t.touserid = u.userid
and u.username = 'TestUser'
group by t.formuserid) tt
order by 2 desc) t1
where rownum <= 10oracle测试成功
select *
from (select tt.touserId, max(sendtime) as sendtime --按对方ID分组,时间最大的就是最近联系的时间
from (select t.touserid, t.entrydate as sendtime
from message t, t_user u
where t.formuserid = u.userid
and u.username = 'TestUser' --testuser作为发送者
union
select t.formuserid, t.entrydate
from message t, t_user u
where t.touserid = u.userid --testuser作为接收者
and u.username = 'TestUser') tt
group by tt.touserId
order by sendtime desc) --所以人按最近时间排序
where rownum < 11 --取前10条修改一下
要取前10条,在mysql里是limit 10,sqlservce和sybase里是top 10 oracle是rownum<11.没有一个标准的统一
union
select distinct fromUserid , sendtime from Message where toUserid= A order by
desc sendtime limited 103.取出临时表中前10个,条件是按照发送时间排序不知道对否,尽快公布正确答案
select distinct username from (
select t5.*,rownum r from (
select t1.toUserid,t2.username,t1.sendtime from (select toUserid,sendtime from message
where fromUserid=(select userid from user where username='TestUser')) t1 join user t2 on(t1.toUserid=t2.userid)
union
select t3.fromUserid,t4.username,t3.sendtime from (select fromUserid,sendtime from message
where toUserid=(select userid from user where username='TestUser')) t3 join user t4 on(t3.fromUserid=t4.userid)
)t5 order by t5.sendtime desc
)t6 where t6.r<=10;sql很长,但思路很简单...
group by max不就去掉重复了吗?
select distinct t5.username from (
select t1.toUserid,t2.username,t1.sendtime from (select toUserid,sendtime from message
where fromUserid=(select userid from user where username='TestUser')) t1 join user t2 on(t1.toUserid=t2.userid)
union
select t3.fromUserid,t4.username,t3.sendtime from (select fromUserid,sendtime from message
where toUserid=(select userid from user where username='TestUser')) t3 join user t4 on(t3.fromUserid=t4.userid)
)t5 order by t5.sendtime desc
) where rownum<=10;
select username from (
select distinct t5.username from (
select t1.toUserid,t2.username,t1.sendtime from (select toUserid,sendtime from message
where fromUserid=(select userid from user where username='TestUser')) t1 join user t2 on(t1.toUserid=t2.userid)
union
select t3.fromUserid,t4.username,t3.sendtime from (select fromUserid,sendtime from message
where toUserid=(select userid from user where username='TestUser')) t3 join user t4 on(t3.fromUserid=t4.userid)
)t5 order by t5.sendtime desc
) where rownum<=10;