我目前正在使用以下SQL查询,它返回25行。如何修改它以忽略第一行:
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
我正在使用 SQL Server 2008。
谢谢。
我目前正在使用以下SQL查询,它返回25行。如何修改它以忽略第一行:
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
我正在使用 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
对于SQL Server 2012及以上版本,您可以使用FETCH OFFSET
。
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
然后您可以删除“order by”。
适用于所有版本。ORDER BY
- 如果 OP 想要按特定顺序排列它们(无论是否删除“第一个”),则需要该子句。 - Clockwork-Muse您可以使用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
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
使用以下查询来构建完美运行的查询
WITH cte_customers AS (
SELECT
ROW_NUMBER() OVER(
ORDER BY
first_name,
last_name
) row_num,
customer_id,
first_name,
last_name
FROM
sales.customers
) SELECT
customer_id,
first_name,
last_name
FROM
cte_customers
WHERE
row_num <> 1;
WHERE
子句来排除那个财政年度呢?请注意,在AVG
运行后排除它(例如使用OFFSET
或窗口函数)会使平均值“不正确”——你会添加用户看不到的数字。 - Clockwork-Muse