J#-分页

String sql = "select top 1 * from jobs" +
" where job_id not in (select " +
"top "+(index-1)+" job_id from jobs)";

1,J#-分页.sql

use pubs
go

select * from authors
select * from titleauthor
--查询书籍编号,作者姓名,电话,地址
select title_id,au_lname,au_fname,phone,address from authors join titleauthor on authors.au_id=titleauthor.au_id
select title_id,au_lname,au_fname,phone,address from authors,titleauthor where authors.au_id=titleauthor.au_id




--查询书籍编号,作者姓名,电话,地址,住在oakland
select title_id,au_lname,au_fname,phone,address from authors join titleauthor on authors.au_id=titleauthor.au_id where city='oakland'

select * from authors
select * from publishers

select * from authors a join publishers p on a.city=p.city

--显示住在同城的作者和出版商,还要显示剩下的所有作者
select * from authors a left join publishers p on a.city=p.city
select * from publishers p right join authors a on a.city=p.city

select * from authors a full join publishers p on a.city=p.city

select * from titles
select * from titleauthor
select * from authors
--查询一本书的信息(书籍编号,书名,作者名,单价)
select titles.title_id,title,price,au_lname,au_fname from titles,titleauthor,authors where titles.title_id=titleauthor.title_id and titleauthor.au_id=authors.au_id


--查询书籍编号为ps3333的作者姓名和电话
select au_lname,au_fname,phone from authors where au_id in
(select au_id from titleauthor where title_id='ps3333')

--查询书籍编号,作者姓名,电话,地址

select * from titles
--查询比business的书的平均单价还要高的书籍信息
select * from titles where price>
(select avg(price) from titles where type='business')



--分页查询
select * from titles

--分3页
--第一页
select top 6 * from titles

--第二页
select top 6 * from titles where title_id not in(select top 6 title_id from titles)

--第三页
select top 6 * from titles where title_id not in (select top 12 title_id from titles)


--通用
--每页n,当前页为p
select top n * from 表 where 字段 not in(select top (p-1)*n 字段 from 表)


select * from authors