动态循环模板方式构建sql语句

•  解决统计循环拼接SQL语句

  {
  "diffTableNo": 0,
  "isRESTful": true,
  "base": {
    "connName": "ConnBt",
    "isRearEnd": true,
    "isFrontEnd": true
  },
  "responseField": {
    //按月统计分析
    "my-month": {
      //不使用分页拼接 SQL语句
      "njcResult": {
        "args": [],
        "moduleName": "~/Config/AppNodeJs/bt-plugins/policy-statistics/my-month",
        "functionName": null
      },
      "isList": true,
      "show": "data_alias,data_value,data_type",
      "selectSql": {
        //配合sqlList 里面 value=tempalte 使用  {tempvalue}原始 {addvalue} 加1
        "templateStart": 1,
        "templateEnd": 12,
        //模板连接符号
        "templateUnion": " union ",
        "template": [
          {
            "value": "select null policy_item_id,'all' data_type,'{tempvalue}' data_alias,sum(tb_i.mq_price) data_value from bt_policy_status tb_s inner join bt_policy_item tb_i on (tb_i.policy_id=tb_s.policy_id) where tb_i.create_user_id=@_userId and (tb_s.status=1 or tb_s.status=0) and month(payment_date)={tempvalue} and year(payment_date)=@_currentYear",
            "sortId": 10
          },
          {
            "value": " union ",
            "sortId": 12
          },
          {
            "value": "select null policy_item_id,'dj' data_type,'{tempvalue}' data_alias,sum(tb_i.mq_price) data_value from bt_policy_status tb_s inner join bt_policy_item tb_i on (tb_i.policy_id=tb_s.policy_id) where  tb_i.create_user_id=@_userId and (tb_s.status=0) and month(payment_date)={tempvalue} and year(payment_date)=@_currentYear",
            "sortId": 14
          },
          {
            "value": " union ",
            "sortId": 15
          },
          {
            "value": "select null policy_item_id,'yj' data_type,'{tempvalue}' data_alias,sum(tb_i.mq_price) data_value from bt_policy_status tb_s inner join bt_policy_item tb_i on (tb_i.policy_id=tb_s.policy_id) where tb_i.create_user_id=@_userId and (tb_s.status=1 ) and month(payment_date)={tempvalue} and year(payment_date)=@_currentYear",
            "sortId": 18
          }
        ],
        //"page": null,
        //"detail": null,
        //"list": null,
        "sqlList": [
          {
            "value": "select * from (",
            "sortId": 1
          },
          //表示从模板template里面组合后的sql获取
          {
            "value": "template",
            "sortId": 10
          },
          {
            "value": ") t",
            "sortId": 400
          }
        ]
        //,
        //"count": null,
        //"listField": "*",
        //"pageField": "*",
        //"detailField": "*"
      },
      "guidMethod": 0,
      "dataFromType": 0,
      "original": ""
      //"where": "and sign_id in @_filterPrimaryIds"
    }
  },
  "tables": [
    {
      "id": 1,
      "name": "bt_policy_item",
      "isPrimary": true,
      "primaryColumn": [
        {
          "colName": "policy_item_id",
          "alias": "policy_item_id",
          "field": "policyItemId"
        }
      ],
      "type": "Table"
    }
  ]
  }

•  SqlSelect.cs实现代码

  /// <summary>
        /// 拼接组合模板里面sql语句
        /// </summary>
        private string templateStr
        {
            get
            {
                StringBuilder sbTemplate = new StringBuilder();
                if (Template != null && Template.Any() && Template.Count() > 0)
                {
                    foreach (var item in Template.OrderBy(temp => temp.SortId))
                    {
                        sbTemplate.Append($" {item.Value} ");
                    }
                    StringBuilder sb = new StringBuilder();
                    int start = Convert.ToInt32(TemplateStart);
                    int end = Convert.ToInt32(TemplateEnd);
                    for (int i = start; i <= end; i++)
                    {
                        StringBuilder sbItem = new StringBuilder(sbTemplate.ToString());
                        sbItem.Replace("{tempvalue}", i.ToString());
                        sbItem.Replace("{addvalue}", (i + 1).ToString());
                        if (i != end)
                        {
                            sbItem.Append(TemplateUnion);
                        }
                        sb.Append(sbItem.ToString());
                    }
                    return sb.ToString();
                }
                return null;

            }
        }