子查询之间的行号 [英] Row-Number in Between Sub Query
本文介绍了子查询之间的行号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
select
row_number() over (order by BookTitle) AS Row,
BookTitleID,
BookTitle,
CallNumber,
FullName,
count(case Status when 'OnShelf' then 1 else null end) AS CopiesOnShelves
from
(
select
Book.BookTitleID,
BookTitles.BookTitle,
BookTitles.CallNumber,
Book.Status,
FullName = LastName + ', ' + FirstName + ' ' + MiddleName
From
Book
left outer join
BookTitles
on BookTitles.BookTitleID = Book.BookTitleID
left outer join
Authors
on Authors.AuthorID = BookTitles.AuthorID ) sub
Where Row between 1 and 10 -- not working
Group By Callnumber, BookTitle, BookTitleID, FullName
在这个例子中我将如何使用 In between ROW 来显示第 1 行到第 10 行.
How I will use In between ROW in this on example to display Row 1 to Row 10.
推荐答案
select * from(
select
Row,
BookTitleID,
BookTitle,
CallNumber,
FullName,
CopiesOnShelves
from
(
select
Book.BookTitleID,
BookTitles.BookTitle,
BookTitles.CallNumber,
FullName = LastName + ', ' + FirstName + ' ' + MiddleName,
CopiesOnShelves = count(case Status when 'OnShelf' then 1 else null end),
Row = row_number() over (order by BookTitle)
From
Book
left outer join
BookTitles
on BookTitles.BookTitleID = Book.BookTitleID
left outer join
Authors
on Authors.AuthorID = BookTitles.AuthorID
Group By Book.BookTitleID, BookTitles.BookTitle, BookTitles.CallNumber,
LastName, FirstName, MiddleName
) sub
) sub2
WHERE Row between @start and @end
这篇关于子查询之间的行号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文