统计sql组合模板

•  Tables 必须，里面表可以随便赋予
•  Union 前后空格，避免sql连接错误
•  Sql里面必须包含 tables里面主键列名,如 null policy_item_id,

{
  "diffTableNo": 0,
  "isRESTful": true,
  "base": {
    "connName": "ConnBt",
    "isRearEnd": true,
    "isFrontEnd": true
  },
  "responseField": {
    //rcfx  统计日常分析数据
    "my-rcfx": {
      //不使用分页拼接 SQL语句
      "isList": true,
      "selectSql": {
        "page": null,
        "detail": null,
        "list": null,
        "sqlList": [
          {
            "value": "select * from (",
            "sortId": 1
          },
          //2、生  日：统计家庭底下当月被保人生日的数据。
          {
            "value": "select null policy_item_id, 'birthday' name,count(bd_bbr_day) value from bt_policy where create_user_id=@_userId and month(bd_bbr_day)=@_currentMonth",
            "sortId": 2
          },
          {
            //两边必须有空格，避免sql拼接错误
            "value": " union ",
            "sortId": 3
          },
          //3、待核对：统计保单已提交，保障缴费状态未修改数据。
          {
            "value": "select null policy_item_id,'dhd' name,count(tb_i.policy_item_id) value  from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id ) where tb_i.create_user_id=@_userId and tb_s.payment_date is null",
            "sortId": 4
          },
          {
            "value": " union ",
            "sortId": 5
          },
          //1、待缴纳：统计未来1个月需要缴费的保单项数据。
          {
            "value": "select null policy_item_id,'djl' name, count(tb_i.policy_item_id) value from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id ) where tb_i.create_user_id=@_userId  and tb_s.status='0' and tb_s.payment_date>=@_futureMonth01S and tb_s.payment_date<=@_futureMonth01E",
            "sortId": 6
          },
          {
            "value": ") t",
            "sortId": 7
          }
        ],
        "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"
    }
  ]
}

//select 'birthday' name,count(policy_id) value from bt_policy where policy_id='3913181546147876864' and month(bd_bbr_day)=2

//union

//select 'dhd' name,count(tb_i.policy_item_id) value  from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id ) where tb_i.policy_id='3913998358531936256' and tb_s.payment_date is null

//union

//select'djl' name, count(tb_i.policy_item_id) value from bt_policy_item tb_i left join bt_policy_status tb_s on(tb_s.policy_id=tb_i.policy_id and tb_s.payment_date>='2004-05-12 16:00:00' and tb_s.payment_date<'2004-06-12 16:00:00') where tb_i.policy_id='3913181546147876864' and tb_s.status='0'