SQL一对多查询

Question

表结构:简略

create table table1
(
    t1Id int primary key identity(1,1),
    author varchar(20),
    title varchar (20)
)
create table table2
(
    t2Id int primary key identity(1,1),
    tFKey int,
    centent varchar(20),
    times varchar (20)
)

insert into table1 values('张三','设计图纸')
insert into table1 values('李四','写报告')
insert into table1 values('王五','总结')
insert into table1 values('赵六','测试')
insert into table1 values('张三','设计图纸2')
insert into table1 values('张三','设计图纸3')

insert into table2 values(1,'第一条记录','2011-06-17')
insert into table2 values(1,'第二条记录','2011-06-19')
insert into table2 values(1,'已操作完毕,最后一条','2011-06-30')
insert into table2 values(2,'李四的第一条记录','2011-07-03')
insert into table2 values(2,'李四的最后一条','2011-07-05')
insert into table2 values(3,'测试1','2011-07-01')
insert into table2 values(3,'测试2','2011-07-06')
insert into table2 values(3,'测试3','2011-07-08')
insert into table2 values(3,'测试4','2011-07-09')
insert into table2 values(3,'已操作完毕,最后一条','2011-07-11')
insert into table2 values(5,'设计图纸完毕','2011-07-09')
insert into table2 values(6,'图纸4正在进行','2011-07-13')

我想实现查询的效果如下:

 t1Id    author      title         centent                times
2  　　李四  　　写报告   　　李四的最后一条   　　2011-07-05
3  　　王五  　　总结   　　　已操作完毕,最后一条 2011-07-11
6 　　 张三  　　设计图纸3    图纸4正在进行   　　2011-07-13
5  　　张三  　　设计图纸2   设计图纸完毕   　　　2011-07-09
1  　　张三  　　设计图纸     已操作完毕,最后一条 2011-06-30
4  　　赵六  　　测试   　　　NULL     　　　　　　NULL\

------------------------------------------------------

就是查询出table1表中所有数据,和table2表中对应t1中的Id,只显示最后一条记录,我这样可以显示,但不支持分页

SELECT table1.*,b.centent, b.times
  FROM table1 LEFT JOIN (
  SELECT number = ROW_NUMBER() OVER(PARTITION BY tFkey ORDER BY times desc),* FROM table2
  ) b ON t1Id = b.tfkey AND b.number = 1 order by author, times desc

 我需要能分页的,各位有什么好方法吗?

Answer 1

创建一个视图试试呢

Answer 2

嵌套一下不就行了 把你获取的rownum当成字段来处理 with aa as（ SELECT table1.*,b.centent, b.times  FROM table1 LEFT JOIN (  SELECT number = ROW_NUMBER() OVER(PARTITION BY tFkey ORDER BY times desc) rm,* FROM table2  ) b ON t1Id = b.tfkey AND b.number = 1 order by author, times desc ） selec aa.* from aa where rm betwwen 2 and 8

Answer 3

select * from ( SELECT table1.*,b.centent, b.times FROM table1 LEFT JOIN ( SELECT number = ROW_NUMBER() OVER(PARTITION BY tFkey ORDER BY times desc),* FROM table2 ) b ON t1Id = b.tfkey AND b.number = 1 order by author, times desc ) tbl where  加分页条件  rownum between ... and ...