Question

写出一条Sql语句：取出表A中第10到第20记录（注意：ID可能不是连续的）

表1: id, name, typeid, titre;表2: typeid ,typename
查询 表1的全部字段 和表2 的typename 要求实现分页，每页要有10条数据，查询第二页 写出查询的select语句

Answer 1

SELECT TOP 10 *
FROM (select a.*,b.typename from 表1 a left join 表2 b on a.typeid=b.typeid) a
WHERE ID >(SELECT MAX(id) FROM (SELECT TOP 20 id FROM 表1 ORDER BY id) b)
ORDER BY ID

Answer 2

sql server:

前10条:
select top 10 * from 表 order by 需要排序的字段

11-20:
select * from
(select row_number() over (order by 需要排序的字段) as id,* from 表
)a
where id between 11 and 20
或者
select top 10 * from 表
where id not in (select top 10 * from 表 order by 需要排序的字段)
order by 需要排序的字段

oracle:
前10条:
select * from
(
select * from 表 order by 需要排序的字段
)
where rownum<=10

11-20:

select * from
(
select * from 表 order by 需要排序的字段
)
where rownum between 11 and 20

mysql:
前10条
select * from
(
select * from 表 order by 需要排序的字段
)a
limit 10
11-20条:
select * from
(
select * from 表 order by 需要排序的字段
)a
limit 11,10

---------
根据你的要求
将上面
from 表
替换为
from (select a.*,b.typename from 表1 a left join 表2 b on a.typeid=b.typeid) a
就可以了

Answer 3

select * from (select *,row_number() over(order by c.id) tid from (select a.*,typename from tbl1 a,tbl2 b where a.typeid = b.typeid) c) where c.tid between 11 and 20

Answer 4

select
top 10 * from (select b.*,c.typename from b left join c on b.typeid=c.typeid) b where id >(select id from (select top 20 id FROM b order by id) c)order by id