选择所有行并忽略第一行

I'm currently using the following SQL query which is returning 25 rows. How can I modify it to ignore the first row:

我目前正在使用以下返回25行的SQL查询。如何修改它以忽略第一行:

SELECT fiscal_year, SUM(total_sales) as sum_of_year, AVG(SUM(total_sales)) 
OVER () as avg_sum 
FROM sales_report 
GROUP BY fiscal_year 
ORDER BY fiscal_year ASC

I'm using SQL Server 2008.

我正在使用SQL Server 2008。

Thanks.

4 个解决方案

#1

You can use EXCEPT in SQL Server 2008.

您可以在SQL Server 2008中使用EXCEPT。

SELECT fiscal_year, SUM(total_sales) as sum_of_year, AVG(SUM(total_sales)) 
OVER () as avg_sum 
FROM sales_report 
GROUP BY fiscal_year

EXCEPT

SELECT TOP 1 fiscal_year, SUM(total_sales) as sum_of_year, AVG(SUM(total_sales)) 
OVER () as avg_sum 
FROM sales_report 
GROUP BY fiscal_year 
ORDER BY fiscal_year ASC

For SQL Server 2012 and above, you can use FETCH OFFSET

对于SQL Server 2012及更高版本,您可以使用FETCH OFFSET

#2

assuming this is exactly how you'd query it, then: SELECT fiscal_year, SUM(total_sales) as sum_of_year, AVG(SUM(total_sales)) OVER () as avg_sum FROM sales_report WHERE fiscal year <> (SELECT MIN(Fiscal_year) FROM sales_report)) GROUP BY fiscal_year ORDER BY fiscal_year ASC

假设这正是您查询它的方式,那么:SELECT fiscal_year,SUM(total_sales)为sum_of_year,AVG(SUM(total_sales))OVER()as avg_sum FROM sales_report WHERE财政年度<>(SELECT MIN(Fiscal_year)FROM sales_report ))GROUP_ fiscal_year ORDER BY fiscal_year ASC

And then you can remove the "order by".
Works on all versions

然后你可以删除“order by”。适用于所有版本

#3

You can use ROW_NUMBER window function

您可以使用ROW_NUMBER窗口功能

;with cte as
(
SELECT Row_number()over(order by fiscal_year) as RN,fiscal_year, SUM(total_sales) as sum_of_year, AVG(SUM(total_sales)) 
OVER () as avg_sum 
FROM sales_report 
GROUP BY fiscal_year 
)
select * 
From cte
Where RN <> 1
ORDER BY fiscal_year ASC

#4

Hope this is correct. This is what I have tried to make it:

希望这是正确的。这就是我试图做到的:

SELECT fiscal_year
    ,a.sum_of_year
    ,AVG(SUM(a.sum_of_year)) OVER () AS avg_sum
FROM (
    SELECT Row_number() OVER (
            ORDER BY fiscal_year
            ) AS RN
        ,fiscal_year
        ,SUM(total_sales) AS sum_of_year
    FROM test_fiscal
    GROUP BY fiscal_year
    ) a
WHERE a.rN > 1
GROUP BY fiscal_year
    ,rn
    ,a.sum_of_year
ORDER BY fiscal_year ASC

#1