ROW_NUMBER（）的SQL查询问题 [英] sql query problem with ROW_NUMBER()

问题描述

这里where不起作用：

使用OrderedOrders AS 
（
 select *，ROW_NUMBER（）over（按AlbumName排序）as来自dbo.CountryAlbums的'RowNumber'
，其中SubCategoryName ='Rockabilly'
）
从OrderedOrders中选择*，其中RowNumber在5到10之间

并且在我拿走所有桌子后where工作：

使用OrderedOrders AS 
（
选择*，ROW_NUMBER（）结束（按AlbumName排序）为'RowNumber'
来自dbo.CountryAlbums 
）
 select * from OrderedOrders其中RowNumber介于5和10之间和SubCategoryName ='Rockabilly'

我不想拉整个表如何将它注册为第一个例子？

解决方案

此查询：

;  with  OrderedOrders  AS  
（ 
 选择 *，ROW_NUMBER（） over （订单 按 AlbumName） as  '  RowNumber' 
 来自 dbo.CountryAlbums 
  where  SubCategoryName = '  Rockabilly' 
）
 选择 * 
 来自 OrderedOrders 
 其中 RowNumber    5  和  10  

写得很好，含义与：

  SELECT  * 
  FROM （
  SELECT  *，ROW_NUMBER（） OVER （ ORDER   BY  AlbumName） AS  '  RowNumber' 
  FROM  dbo.CountryAlbums 
  WHERE  SubCategoryName = '  Rockabilly' 
） AS  T 
  WHERE  RowNumber    5  和  10  

当你说：这个或那个不起作用时，你应该更具体一点！

hi，



试试这个

 选择 *，ROW_NUMBER（） over （ order   by  AlbumName） as  '  RowNumber'  into  #OrderedOrders 来自 dbo.CountryAlbums 其中 SubCategoryName = '  Rockabilly' 
 
 选择 * 来自 #OrderedOrders 其中 RowNumber    5  和  10  
  drop  表 #OrderedOrders 

here the "where" not work:

with OrderedOrders AS
(
select * , ROW_NUMBER()   over (order by AlbumName) as 'RowNumber' 
from dbo.CountryAlbums  where SubCategoryName = 'Rockabilly'              
)   
select * from OrderedOrders where RowNumber between 5 and 10  

and after i take all the table the "where" is work:

with OrderedOrders AS
(
select * , ROW_NUMBER()   over (order by AlbumName) as 'RowNumber' 
from dbo.CountryAlbums              
)   
select * from OrderedOrders where RowNumber between 5 and 10  and SubCategoryName = 'Rockabilly'  

I do not want to pull the entire table How can I register it as the first example?

解决方案

This query:

;with OrderedOrders AS
(
    select * , ROW_NUMBER() over(order by AlbumName) as 'RowNumber' 
    from dbo.CountryAlbums
    where SubCategoryName = 'Rockabilly'              
)   
select *
from OrderedOrders
where RowNumber between 5 and 10

is well written and has the same meaning as:

SELECT *
FROM (
    SELECT * , ROW_NUMBER() OVER(ORDER BY AlbumName) AS 'RowNumber' 
    FROM dbo.CountryAlbums
    WHERE SubCategoryName = 'Rockabilly'              
) AS T
WHERE RowNumber between 5 and 10

You should be more specific when you saying: "this or that is not working"!

hi,

try this

select * , ROW_NUMBER()   over (order by AlbumName) as 'RowNumber' into #OrderedOrders  from dbo.CountryAlbums  where SubCategoryName = 'Rockabilly'

select * from #OrderedOrders where RowNumber between 5 and 10
drop table #OrderedOrders

这篇关于ROW_NUMBER（）的SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！