首页 新闻 赞助 找找看

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[]的主页 555[] | 初学一级 | 园豆:4
提问于:2011-07-14 12:00
< >
分享
最佳答案
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


小-_-戴 | 初学一级 |园豆:9 | 2011-07-26 21:13
你好,你这个显示错误的
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
555[] | 园豆:4 (初学一级) | 2011-07-27 11:27
其他回答(3)
-1

创建一个视图试试呢

╰つ左手 | 园豆:145 (初学一级) | 2011-07-14 15:19
-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

罗铭 | 园豆:250 (菜鸟二级) | 2011-07-16 08:55
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 ...

杯具程序员 | 园豆:1718 (小虾三级) | 2011-07-26 17:17
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册