所以，我有以下查询，
WITH yearlist AS
(
SELECT (year(getdate())+3) AS years
UNION ALL
SELECT y.years - 1 AS years
FROM yearlist y
WHERE y.years - 1 >= (YEAR(GETDATE())-10)
)
SELECT
a.years as [year],
a.CountryName as country,
ISNULL(sum(b.sales), 0) as total
FROM(
SELECT
distinct years
,g.CountryName
FROM
yearlist AS A CROSS JOIN (SELECT
CountryName, salesYear, ISNULL(sum(sales), 0) as total
FROM tblSales where
salesYear BETWEEN (year(getdate())-12) AND (year(getdate()) + 3)
,sales
,salesYear) g
) a left outer join
(SELECT
CountryName, salesYear, ISNULL(sum(sales), 0) as total
FROM tblSales where
salesYear BETWEEN (year(getdate())-12) AND (year(getdate()) + 3)
group by CountryName
,salesYear, sales
) b ON a.CountryName=b.CountryName and a.years=b.salesYear
group by a.CountryName,years
order by years
我得到以下返回：
year country Total
---------- ---------------------------------------- -------
2009 France 0.00
2009 Japan 0.00
2009 Norway 2.30
2009 Portugal 0.00
2009 South Korea 0.00
2009 Spain 0.00
2009 Sweden 0.00
2009 United Kingdom 0.00
2009 United States 0.00
2010 France 0.00
2010 Japan 0.00
2010 Norway 0.00
2010 Portugal 0.00
2010 South Korea 0.00
2010 Spain 0.00
2010 Sweden 0.00
2010 United Kingdom 0.00
2010 United States 0.00
2011 France 0.00
2011 Japan 0.00
2011 Norway 0.00
2011 Portugal 2.00
2011 South Korea 0.00
2011 Spain 0.00
2011 Sweden 0.00
2011 United Kingdom 0.00
2011 United States 0.00
2012 France 0.00
2012 Japan 0.01
2012 Norway 0.00
2012 Portugal 0.00
2012 South Korea 0.00
2012 Spain 0.00
2012 Sweden 0.00
2012 United Kingdom 0.00
2012 United States 0.00
2013 France 0.00
2013 Japan 2.00
2013 Norway 0.00
2013 Portugal 0.00
2013 South Korea 0.00
2013 Spain 0.00
2013 Sweden 0.00
2013 United Kingdom 0.00
2013 United States 0.00
随着年份的增加，我正在努力实现每个国家的累计总数。但我似乎无法理解。我已经试过了：
sum(sales) over (order by salesYear rows unbounded preceding) as total
但这只是用累积总数填充每一行。
我想要的输出如下：
year country Total
---------- ---------------------------------------- -------
2009 France 0.00
2010 France 0.00
2011 France 0.00
2009 Japan 0.00
2010 Japan 0.00
2011 Japan 0.00
2009 Norway 2.30
2010 Norway 2.30
2011 Norway 2.30
2009 Portugal 0.00
2010 Portugal 0.00
2011 Portugal 2.00
2009 South Korea 0.00
2010 South Korea 0.00
2011 South Korea 0.00
2009 Spain 0.00
2010 Spain 0.00
2011 Spain 0.00
2009 Sweden 0.00
2010 Sweden 0.00
2011 Sweden 0.00
2009 United Kingdom 0.00
2010 United Kingdom 0.00
2011 United Kingdom 0.00
2009 United States 0.00
2010 United States 0.00
2011 United States 0.00
我似乎无法让他们单独积累。
您很可能也需要使用partition by子句：
sum(sum(sales)) over (partition by country order by salesYear rows unbounded preceding)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句