SQL一对多查询
0
[已解决问题]
解决于 2011-09-10 10:21
表结构:简略
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
我需要能分页的,各位有什么好方法吗?
![555[]的主页](http://pic.cnblogs.com/default-avatar.png){kind=avatar}
最佳答案
0
SELECT * FROM (SELECT num=ROW_NUMBER() OVER(PARTITION BY tFkey ORDER BY times DESC),t1.*,t2.centent, t2.times FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1. t1Id = t2.tfkey ORDER BY t1.author,t2. times DESC ) AS
t WHERE t.num BETWEEN 1 AND 5
你好,你这个显示错误的
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
其他回答(3)
-1
创建一个视图试试呢
-1
嵌套一下不就行了 把你获取的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
1
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 ...
