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                times2  　　李四  　　写报告   　　李四的最后一条   　　2011-07-053  　　王五  　　总结   　　　已操作完毕,最后一条 2011-07-116 　　 张三  　　设计图纸3    图纸4正在进行   　　2011-07-135  　　张三  　　设计图纸2   设计图纸完毕   　　　2011-07-091  　　张三  　　设计图纸     已操作完毕,最后一条 2011-06-304  　　赵六  　　测试   　　　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 ...