网易笔试题

[xiaochengfu1]

刚在网上看到的
题目是这样的：

表 User userid bigint
username varchar

表Message messageId bigint
fromUserid bigint
toUserid bigint
sendtime date

这是个发送聊天信息相关的两个表，现写出SQL语句，求出username = ‘TestUser’这个人最近联系的10个人（包括发送的和接受的人）

各位看看，该怎么写这个SQL

[wfqqwer5213]

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 <= 10

oracle测试成功

[wfqqwer5213]

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条

修改一下

[dr_lou]

select * from Message where fromuserid = xx or touserid = xx order by sendTime desc limit 10; 这样不可以么？

xx是子查询出来的userid

[veardn]

select * from message where fromUserid in (select userid from user where username = 'usertest')

or toUserid in (select userid from user where username = 'usertest')

order by sendtime desc limit 10

这样子吗？

[xiaochengfu1]

有没有用标准sql写的，不分啥数据库

[HOME_ning]

弱弱的飘过~~~~~~

[crazylaa]

都没有考虑到重复的问题。

[wfqqwer5213]

[Quote=引用 10 楼 xiaochengfu1 的回复:]
有没有用标准sql写的，不分啥数据库
[/Quote]

要取前10条，在mysql里是limit 10,sqlservce和sybase里是top 10 oracle是rownum<11.没有一个标准的统一

[zcy9979420]

[Quote=引用 6 楼 wfqqwer5213 的回复:]

SQL code

select *
from (select *
from (select t.touserid, max(t.entrydate)
from message t, t_user u
where t.formuserid = u.userid
a……
[/Quote]

+1

[a619225471]

根据时间时间从近开始排序 取前面10条记录

[chenqqabcdchenqqabcd]

我好像见过这样的题目。也不知道对不对 。思路是这样的，聊天的话点对点通信，每发送一条就往数据库保存一个message记录,同时间将fromuserid和toUserid保存起来，这样再查出最近10记录。不过好像没这么简单。

[caofaping]

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 ;

[wfqqwer5213]

[Quote=引用 12 楼 crazylaa 的回复:]
都没有考虑到重复的问题。
[/Quote]

group by max不就去掉重复了吗？

[thegodofwar]

可能有一点问题，修改如下：

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;

[thegodofwar]

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;

[xiaochengfu1]

[Quote=引用 12 楼 crazylaa 的回复:]
都没有考虑到重复的问题。
[/Quote]

+1

[dingling136]

1： 先查找出username = ‘TestUser’的userid

A: select userid from User where username= 'TestUser'

2. 查询出Message表中与TestUser关联的信息,将其存入一个临时表B

select distinct toUserid , sendtime from Message where fromUserid= A order by desc sendtime limited 10

union

select distinct fromUserid , sendtime from Message where toUserid= A order by
desc sendtime limited 10

3.取出临时表中前10个，条件是按照发送时间排序



不知道对否，尽快公布正确答案

[thegodofwar]

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很长，但思路很简单...

[SDY-小五]

~!!~~ WHERE 

[黄小楼]

6楼的兄弟回答正确！