查询问题(多表查询,TOP...)
有个表叫User,有个表UserInfo
User表有2个字段UserName,UserID
UserInfo表有字段UserID,score
现在是要查询User表中score前10位的用户名和后10位的用户名
[解决办法]
有个表叫User,有个表UserInfo
User表有2个字段UserName,UserID
UserInfo表有字段UserID,score
现在是要查询User表中score前10位的用户名和后10位的用户名
select top 10 * from
(select user.username , user.userid , userinfo.score from user,userinfo where user.userid = userinfo.userid order by userinfo.score desc) t
select top 10 * from
(select user.username , user.userid , userinfo.score from user,userinfo where user.userid = userinfo.userid order by userinfo.score) t
[解决办法]
select a.username
from User a
where userid in (select top 10 distinct userid from UserInfo order by score)
or userid in (select top 10 distinct userid from UserInfo order by score desc)
[解决办法]
如果数据量大，试试看下面的，看看哪个效率好些。
前10
select a.username,b.score from user a inner join
(select top 10 userid from userinfo order by score desc) b
on a.userid = b.userid
后10
select a.username,b.score from user a inner join
(select top 10 userid from userinfo order by score asc) b
on a.userid = b.userid
