sql - 我想根据 12 个月的间隔向后查找平均值

Question

我想根据 12 个月后的期间列找到值列的平均值。你如何将它应用到 SQL 中？

（列数据由月份和年份组成，值得 nchar (6)）

请高手帮忙，谢谢。

标签： sqlsql-server

Answer 1

  SELECT AVG(VALUE) AS AV 
                    FROM YourTab 
                    WHERE 
                            PERIODE BETWEEN 
                                cast(YEAR(dateadd(month,-11,CAST(PERIODE + '01' as datetime))) * 100 
                                        + MONTH(CAST(PERIODE + '01' as datetime)) as nchar(6))   --go back 11 months for example
                                AND
                                cast(YEAR(dateadd(month,0,CAST(PERIODE + '01' as datetime))) * 100 
                                + MONTH(CAST(PERIODE + '01' as datetime))  as nchar(6))    --go back 0 months, for example
            GROUP BY PERIODE