Question

SQLSERVER取前10条记录怎样取

单表我会
select top 10 from table order by id
联表呢?
例如
select top 10 from (select A.name,B.sex from userinfo A,sexinfo B where A.id = B.id and A.sex = B.sexid) order by id
就会报错,请指教

Answer 1

你这用得着嵌套吗。不是多此一举吗？

select top 10 A.name,B.sex from userinfo A,sexinfo B where A.id = B.id and A.sex = B.sexid order by A.id

要是真象你那么写的话，也可以

select top 10 C.name,C.sex from (select A.name name,B.sex sex ,A.id id from userinfo A,sexinfo B where A.id = B.id and A.sex = B.sexid) C order by A.id

Answer 2

order by中指定了用id排序，至少子查询中应该包含id列吧？另外子查询用在from子句中必须为子查询设置一个别名。
select top 10 * from (select A.id,A.name,B.sex from userinfo A,sexinfo B where A.id = B.id and A.sex = B.sexid) as newtable order by id

Answer 3

思路是正确的，但是sql语句写的有些问题，这么写就对了
select top 10 * from (select A.name,B.sex，A.id from userinfo A,sexinfo B where A.id = B.id and A.sex = B.sexid) C order by C.id

Answer 4

oracle:
select * from tab where rownum <= 10;

sql server:
select top 10 * from tab

mysql:
select * from tab limit 10

db2:
select * from tab fetch first 10 rows only

Answer 5

select top 10 A.name,B.sex from userinfo A,sexinfo B where A.id = B.id and A.sex = B.sexid order by A.id
也许这样会简单点！