oracle 拼同比环比查询sql

　　　　 /// <summary>
        /// 生成计算同比环比查询语句
        /// table:表名称；statColumns：要统计的值字段;yearColumn:年份字段名；monthColumn：月份字段名；joinColumns:除年月外的连接条件
        /// --上期无值或0本期有值不为0：1
        /// --上期有值不为0本期无值或0：-1
        /// --上期本期都无值或都为0：null
        /// --上期本期都有值且都不为0：(本期-上期)/上期
        /// </summary>
        public string GenerateOracleStatSql(string table, string[] statColumns, string[] joinColumns, string yearColumn, string monthColumn)
        {
            if (string.IsNullOrEmpty(table) || statColumns == null || statColumns.Length == 0)
            {
                return null;
            }
            string mainTableName = "m"; //主表别名
            string tbTableName = "t";//同比表别名
            string hbTableName = "h";//环比表别名
            StringBuilder sql = new StringBuilder();
            //查询
            sql.Append("select ");
            //查询年月以外的字段
            //查询年月
            foreach (string column in joinColumns)
            {
                sql.AppendFormat("{0}.{1},", mainTableName, column);
            }
            sql.AppendFormat("{0}.{1},{0}.{2}", mainTableName, yearColumn, monthColumn);
            //查询主表统计字段
            foreach (string column in statColumns)
            {
                sql.AppendFormat(",{0}.{1}", mainTableName, column);
            }
            //查询同比，环比
            foreach (string column in statColumns)
            {
                //同比
                sql.AppendFormat(",decode(nvl({0}.{2}, 0),0,decode(nvl({1}.{2}, 0), 0, null, 1),decode(nvl({1}.{2}, 0),0,-1,({1}.{2} - {0}.{2}) / {0}.{2})) {2}_TB", tbTableName, mainTableName, column);
                //环比
                sql.AppendFormat(",decode(nvl({0}.{2}, 0),0,decode(nvl({1}.{2}, 0), 0, null, 1),decode(nvl({1}.{2}, 0),0,-1,({1}.{2} - {0}.{2}) / {0}.{2})) {2}_HB", hbTableName, mainTableName, column);
            }
            string tbwhere = "";
            string hbwhere = "";
            foreach (string column in joinColumns)
            {
                tbwhere += string.Format(" and {0}.{1} = {2}.{1}", mainTableName, column, tbTableName);
                hbwhere += string.Format(" and {0}.{1} = {2}.{1}", mainTableName, column, hbTableName);
            }
            //要查询的表和连接条件
            sql.AppendFormat(@"  from {0} {1}
                                  left join {0} {2}
                                    on to_number({1}.{4}) = to_number({2}.{4}) + 1
                                   and to_number({1}.{5}) = to_number({2}.{5}) {6}
                                  left join {0} {3}
                                    on to_number({1}.{4}) * 12 + to_number({1}.{5}) =
                                       to_number({3}.{4}) * 12 + to_number({3}.{5}) + 1 {7}
                                 order by to_number({1}.{4}), to_number({1}.{5})", table, mainTableName, tbTableName, hbTableName, yearColumn, monthColumn, tbwhere, hbwhere);
            return sql.ToString();
        }

调用：

string sql = GenerateOracleStatSql("TEST_STAT", new string[] { "TOT_WT" }, new string[] { "GOODS_NAME" }, "STAT_YEAR", "STAT_MONTH");

生成结果：

select m.GOODS_NAME,
       m.STAT_YEAR,
       m.STAT_MONTH,
       m.TOT_WT,
       decode(nvl(t.TOT_WT, 0),
              0,
              decode(nvl(m.TOT_WT, 0), 0, null, 1),
              decode(nvl(m.TOT_WT, 0),
                     0,
                     -1,
                     (m.TOT_WT - t.TOT_WT) / t.TOT_WT)) TOT_WT_TB,
       decode(nvl(h.TOT_WT, 0),
              0,
              decode(nvl(m.TOT_WT, 0), 0, null, 1),
              decode(nvl(m.TOT_WT, 0),
                     0,
                     -1,
                     (m.TOT_WT - h.TOT_WT) / h.TOT_WT)) TOT_WT_HB
  from TEST_STAT m
  left join TEST_STAT t
    on to_number(m.STAT_YEAR) = to_number(t.STAT_YEAR) + 1
   and to_number(m.STAT_MONTH) = to_number(t.STAT_MONTH)
   and m.GOODS_NAME = t.GOODS_NAME
  left join TEST_STAT h
    on to_number(m.STAT_YEAR) * 12 + to_number(m.STAT_MONTH) =
       to_number(h.STAT_YEAR) * 12 + to_number(h.STAT_MONTH) + 1
   and m.GOODS_NAME = h.GOODS_NAME
 order by to_number(m.STAT_YEAR), to_number(m.STAT_MONTH)