如何使用ROW_NUMBER()限制SQL Server返回的结果

Question

我有如下的数据库

CREATE DATABASE Test2;
CREATE TABLE table1
(
name nvarchar(50),
year int,
total1 int,
total2 int
);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('a', 2020, 25,3);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('b', 2018, 33,4);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('c', 2020, 10,3);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('b', 2018, 7,2);
INSERT INTO table1 (name, year, total1,total2)
VALUES ('a', 2020, 20,6);

我想用以下代码限制从SQL Server返回的结果(取第二行和第三行(

select 
*
from
(select 
year, name, 
sum(total1) as "sum_Total1", 
sum(total2) as "sum_Total2", 
round((cast(isnull(sum(total2), 0) as float)) / (cast(sum(total1) as float)), 3) as "sum_Total2/sum_Total1", 
row_number() over (order by round((cast(isnull(sum(total2), 0) as float)) / (cast(sum(total1) as float)), 3) asc) as no
from 
Table_1
group by 
name, year
order by 
round((cast(isnull(sum(total2), 0) as float)) * 100 / (cast(sum(total1) as float)), 3) asc) a
where 
a.no > 1 and a.no < 3

SQL Server返回错误：

ORDER BY子句在视图、内联函数、派生表、子查询和公共表表达式中无效，除非同时指定了TOP、OFFSET或FOR XML。

Answer 1

实际上，由于您已经在ROW_NUMBER() over(ORDER BY ROUND行中使用了Order By，因此分组后不需要在内部查询中再次使用它。因此，您所需要做的就是在您的组后面按关键字删除不必要的顺序。

同样仅供参考，我可以看到您已经从Table_1查询了，而您的表名是table1，所以您也需要修复它。

Answer 2

有两个问题：

1. 您使用的表与您创建的表不同。

2. 要获得第二排和第三排，您需要更改条件a.no<3至a.no<=3

没有使用order by子句，因为我们有no。

最后：

SELECT *
FROM
(
SELECT year, 
name, 
SUM(total1) AS "sum_Total1", 
SUM(total2) AS "sum_Total2", 
ROUND((CAST(ISNULL(SUM(total2), 0) AS FLOAT)) / (CAST(SUM(total1) AS FLOAT)), 3) AS "sum_Total2/sum_Total1", 
ROW_NUMBER() OVER(
ORDER BY ROUND((CAST(ISNULL(SUM(total2), 0) AS FLOAT)) / (CAST(SUM(total1) AS FLOAT)), 3) ASC) AS no
FROM Table1
GROUP BY name, 
year
--ORDER BY ROUND((CAST(ISNULL(SUM(total2), 0) AS FLOAT)) * 100 / (CAST(SUM(total1) AS FLOAT)), 3) ASC
) a
WHERE a.no > 1
AND a.no <= 3;

Answer 3

只需将订单移动到子查询之外即可：

select * 
from
(
select year ,name,
sum(total1) as "sum_Total1", 
SUM(total2) as "sum_Total2", 
ROUND((CAST(ISNULL(sum(total2),0) as float))/ 
(CAST(sum(total1) as float)),3) as "sum_Total2/sum_Total1", 
ROW_NUMBER() over (ORDER BY 
ROUND((CAST(ISNULL(sum(total2),0) as float))/ (CAST(sum(total1) as float)),3) ASC )  as no
from Table1
group by name, year
) a
where a.no > 1 and a.no < 4
order by no;