sql - 如何获得前三行的总和，然后是新列中的下三行

Question

你好我有一个sql server内部连接

SELECT a.field_name, 
       b.ticker     AS Ticker_Name, 
       Year(c.date) AS YEAR, 
       c.stock_id, 
       c.field_id, 
       Sum(c.value) AS Value 
FROM   field_table a 
       INNER JOIN stock_transaction c 
               ON a.id = c.field_id 
       INNER JOIN stocks b 
               ON b.id = c.stock_id 
WHERE  b.id = 230 
       AND c.field_id = 29 
GROUP  BY Year(c.date), 
          b.ticker, 
          c.stock_id, 
          c.field_id, 
          a.field_name; 

我还附上了我的输出。

我的输出是每年销售价值的总和。现在我的任务是我必须在新列中显示三行的总和。例如： 2008 , 2009 , 2010 ，然后是 2011 , 2012 , 2013 的总和。然后是 2014 年 , 2015 年 , 2016 年

我的愿望输出是

任何人都可以建议我如何实现这一点。提前谢谢

标签： sqlsql-serverssmssql-server-2016

Answer 1

你只是想要lag()吗？

SELECT f.field_name, s.ticker AS Ticker_Name, 
       Year(st.date) AS YEAR, st.stock_id, st.field_id, 
       Sum(st.value) AS Value,
       LAG(Sum(st.value, 1)) OVER (PARTITION BY f.field_name, s.ticker, st.stock_id, st.field_id ORDER BY Year(st.date)) as year_1,
       LAG(Sum(st.value, 2)) OVER (PARTITION BY f.field_name, s.ticker, st.stock_id, st.field_id ORDER BY Year(st.date)) as year_2,
       LAG(Sum(st.value, 3)) OVER (PARTITION BY f.field_name, s.ticker, st.stock_id, st.field_id ORDER BY Year(st.date)) as year_3
FROM field_table f INNER JOIN
     stock_transaction st
     ON f.id = st.field_id INNER JOIN
     stocks s 
     ON s.id = st.stock_id 
WHERE s.id = 230 AND st.field_id = 29 
GROUP BY Year(st.date), s.ticker, st.stock_id, st.field_id, f.field_name; 

请注意，我还将表别名替换为表的缩写。您应该使用有意义的表别名——例如缩写词——而不是任意字母。