king

oracle下拼同比环比查询sql方法

king Mssql 1970-01-01 881浏览 0
拼接方法:   
     /// <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)
%0

发表评论