oracle查同比增长_oracle 拼同比环比查询sql

///

///生成计算同比环比查询语句///table:表名称；statColumns：要统计的值字段;yearColumn:年份字段名；monthColumn：月份字段名；joinColumns:除年月外的连接条件///--上期无值或0本期有值不为0：1///--上期有值不为0本期无值或0：-1///--上期本期都无值或都为0：null///--上期本期都有值且都不为0：(本期-上期)/上期///

public string GenerateOracleStatSql(string table, string[] statColumns, string[] joinColumns, string yearColumn, stringmonthColumn)

{if (string.IsNullOrEmpty(table) || statColumns == null || statColumns.Length == 0)

{return null;

}string mainTableName = "m"; //主表别名

string tbTableName = "t";//同比表别名

string hbTableName = "h";//环比表别名

StringBuilder sql = newStringBuilder();//查询

sql.Append("select");//查询年月以外的字段//查询年月

foreach (string column injoinColumns)

{

sql.AppendFormat("{0}.{1},", mainTableName, column);

}

sql.AppendFormat("{0}.{1},{0}.{2}", mainTableName, yearColumn, monthColumn);//查询主表统计字段

foreach (string column instatColumns)

{

sql.AppendFormat(",{0}.{1}", mainTableName, column);

}//查询同比，环比

foreach (string column instatColumns)

{//同比

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 injoinColumns)

{

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);returnsql.ToString();

}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！