The most simple hypothesis:
Table "book", two fields (the book number, the name of the book),
Table "page", n field (of the book number, page Numbers, content),

This table is more than 1 to 2, the relationship between 1 book has many pages, and I want to be a 1 to 1 query, returned to field are: the serial number of the book, the book name, page number, content,
The last page for each book (the largest page page number), how to give expression to the page's largest, please help me to write a query examples, thank you.

CodePudding user response:

Select a. book number, n. The name of the book, a. page, [p] content
The From (
Select [books]. The serial number of the book, MAX ([books]. The name of the book) as the name of the book, MAX ([p]. Page Numbers) as the largest page form [book] inner join [page] on [books]. Book number=[p]. The number of
Group by [books]. The serial number of the book
) A
Inner join [page] on [p]. Book number=a. the serial number of the book AND [page] page=a. biggest page number

CodePudding user response:

 select * from (
Select a. *, page Numbers, b. b. content, row_number () over (partition by a. books the number of the order by b. page desc) as rids the from a book 
Left the join page b on a. books=b. the serial number of the number of 
) a 
Where rids=1 

Direct group according to the page sorting, reverse page number of the largest first, can take all the order number was 1