首頁猿問 SQL一對多查詢

SQL一對多查詢

SQL Server

qq_遁去的一_1 2018-12-06 21:28:22

表結構:簡略 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') 我想實現(xiàn)查詢的效果如下: ?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表中所有數(shù)據(jù),和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 ?我需要能分頁的,各位有什么好方法嗎?

查看完整描述

4 回答

慕容森

TA貢獻1853條經(jīng)驗獲得超18個贊

創(chuàng)建一個視圖試試呢

反對回復 2019-01-07

喵喔喔

TA貢獻1735條經(jīng)驗獲得超5個贊

嵌套一下不就行了把你獲取的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

反對回復 2019-01-07

慕容3067478

TA貢獻1773條經(jīng)驗獲得超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