common select

1. URL ( Method : POST )
http://192.168.101.163:8010/route/common/portal/api/common-select

2. BODY
{
  "table_nm": "",      (a) 조회 테이블명 (Required)
  "key": "",           (b) join에 사용되는 (a)의 컬럼명 (Optional)
  "join_info": {       (c) join (Optional)
    "table_nm": "",         (d) (a)와 join을 위한 테이블명
    "key": ""               (e) join에 사용되는 (d)의 컬럼명
  },
  "where_info": [      (f) where (Optional)
    {
      "table_nm": "",       (g) WHERE절을 위한 테이블명
      "key": "",            (h) (g)의 컬럼명
      "value": "",          (i) 조건값
      "compare_op": "",     (j) Comparison Operators 
                               (LIKE | NOT LIKE | IN | NOT IN | Equal | Not Equal | Greater Than | Greater Than or Equal | Less Than | Less Than or Equal)
      "op": ""              (k) AND | OR (다수의 WHERE절 사용시)
      "sub": [              (l) sub where (Optional)
        {
          "table_nm": "",
          "key": "",
          "value": "",
          "compare_op": "",
          "op": ""
        }
      ]
    }
  ],
  "order_info": {      (m) order (Optional)
    "table_nm": "",         (n) ORDER를 위한 테이블명
    "key": "",              (o) (l)의 컬럼명
    "order": ""             (p) ASC | DESC (오름차순 | 내림차순)
  },
  "page_info": {       (q) paging (Optional)
    "per_page": 0,          (r) 페이지 당 row 
    "cur_page": 0           (s) 페이지 번호
  }
}

@@@ EXAMPLE @@@
1. select
- body :
  {
    "table_nm": "tb_data_visual_anals"
  }

- query :
  SELECT * FROM tb_data_visual_anals;

2. select + join
- body : 
{
  "table_nm": "tb_data_visual_anals",
  "key": "conts_id",
  "join_info": {
    "table_nm": "tb_data_visual_anals_data_set",
    "key": "conts_id"
  }
}

- query : 
  SELECT * FROM tb_data_visual_anals JOIN tb_data_visual_anals_data_set ON tb_data_visual_anals.conts_id = tb_data_visual_anals_data_set.conts_id;

3. select + where
# where (IN)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "ctgry",
        "value": "PART,PRCE",
        "compare_op": "IN"
      }
    ]
  }

- query :
SELECT * FROM tb_data_visual_anals WHERE tb_data_visual_anals.ctgry IN ('PART', 'PRCE');

# where (NOT IN)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "ctgry",
        "value": "PART,PRCE",
        "compare_op": "NOT IN"
      }
    ]
  }

- query :
SELECT * FROM tb_data_visual_anals WHERE tb_data_visual_anals.ctgry NOT IN ('PART', 'PRCE');

# where (LIKE)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "ctgry",
        "value": "%PART%",
        "compare_op": "LIKE"
      }
    ]
  }

- query :
SELECT * FROM tb_data_visual_anals WHERE tb_data_visual_anals.ctgry LIKE '%PART%';

# where (NOT LIKE)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "ctgry",
        "value": "%PART%",
        "compare_op": "NOT LIKE"
      }
    ]
  }

- query :
SELECT * FROM tb_data_visual_anals WHERE tb_data_visual_anals.ctgry NOT LIKE '%PART%';

# where (Greater Than)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "retv_cnt",
        "value": "34",
        "compare_op": "Greater Than"
      }
    ]
  }

- query :
SELECT * FROM tb_data_visual_anals  WHERE   tb_data_visual_anals.retv_cnt > '34'  ;

# where (Greater Than or Equal)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "retv_cnt",
        "value": "34",
        "compare_op": "Greater Than or Equal"
      }
    ]
  }

- query :
SELECT * FROM tb_data_visual_anals  WHERE   tb_data_visual_anals.retv_cnt >= '34'  ;

# where (Less Than)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "retv_cnt",
        "value": "34",
        "compare_op": "Less Than"
      }
    ]
  }

- query :
  SELECT * FROM tb_data_visual_anals  WHERE   tb_data_visual_anals.retv_cnt < '34'  ;

# where (Less Than or Equal)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "retv_cnt",
        "value": "34",
        "compare_op": "Less Than or Equal"
      }
    ]
  }

- query :
SELECT * FROM tb_data_visual_anals  WHERE   tb_data_visual_anals.retv_cnt <= '34'  ;

# where (OR | AND)
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "ctgry",
        "value": "PART"
        "compare_op": "Equal"
      },
      {
        "table_nm": "tb_data_visual_anals",
        "key": "ctgry",
        "value": "PRCE",
        "compare_op": "Equal",
        "op" : "OR"
      }
    ]
  }
- query : 
  SELECT * FROM tb_data_visual_anals WHERE tb_data_visual_anals.ctgry = 'PART' OR tb_data_visual_anals.ctgry = 'PRCE';

# where (sub where)
- body
{
   "table_nm":"tb_data_visual_anals",
   "key":"conts_id",
   "join_info":{
      "table_nm":"tb_data_visual_anals_data_set",
      "key":"conts_id"
   },
   "where_info":[
      {
         "table_nm":"tb_data_visual_anals",
         "key":"ctgry",
         "value":"PART",
         "compare_op":"Equal",
         "op":""
      },
      {
         "table_nm":"tb_data_visual_anals",
         "key":"ctgry",
         "value":"PART",
         "compare_op":"Equal",
         "op":"OR",
         "sub":[
            {
               "table_nm":"tb_data_visual_anals",
               "key":"ctgry",
               "value":"PRCE",
               "compare_op":"Equal",
               "op":"AND"
            },
            {
               "table_nm":"tb_data_visual_anals",
               "key":"ctgry",
               "value":"PRCE,PART",
               "compare_op":"IN",
               "op":"OR"
            }
         ]
      }
   ]
}

- query
 SELECT * FROM tb_data_visual_anals JOIN tb_data_visual_anals_data_set ON tb_data_visual_anals.conts_id = tb_data_visual_anals_data_set.conts_id WHERE   tb_data_visual_anals.ctgry = 'PART' OR (tb_data_visual_anals.ctgry = 'PART' AND tb_data_visual_anals.ctgry = 'PRCE' OR tb_data_visual_anals.ctgry IN ( 'PRCE', 'PART' ));

4. select + order
# order 1
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "order_info": {
      "table_nm": "tb_data_visual_anals",
      "key": "retv_cnt",
      "order": "ASC"
    }
  }
- query : 
  SELECT * FROM tb_data_visual_anals ORDER BY tb_data_visual_anals.retv_cnt ASC;

# order 2
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "order_info": {
      "table_nm": "tb_data_visual_anals",
      "key": "retv_cnt",
      "order": "DESC"
    }
  }
- query :
  SELECT * FROM tb_data_visual_anals ORDER BY tb_data_visual_anals.retv_cnt DESC;

5. select + page
- body : 
  {
    "table_nm": "tb_data_visual_anals",
    "order_info": {
      "table_nm": "tb_data_visual_anals",
      "key": "conts_id",
      "order": "ASC"
    },
    "page_info": {
      "per_page": 2,
      "cur_page": 2
    }
  }

- query : 
  SELECT * FROM tb_data_visual_anals ORDER BY tb_data_visual_anals.conts_id ASC LIMIT 2 OFFSET (2 * 1);

6. select + join + where + order + page
- body :
  {
    "table_nm": "tb_data_visual_anals",
    "key": "conts_id",
    "join_info": {
      "table_nm": "tb_data_visual_anals_data_set",
      "key": "conts_id"
    },
    "where_info": [
      {
        "table_nm": "tb_data_visual_anals",
        "key": "ctgry",
        "value": "PART",
        "compare_op": "Equal",
        "op": ""
      }
    ],
    "order_info": {
      "table_nm": "tb_data_visual_anals",
      "key": "retv_cnt",
      "order": "ASC"
    },
    "page_info": {
      "per_page": 1,
      "cur_page": 2
    }
  }
- query :
  SELECT * FROM tb_data_visual_anals JOIN tb_data_visual_anals_data_set ON tb_data_visual_anals.conts_id = tb_data_visual_anals_data_set.conts_id WHERE tb_data_visual_anals.ctgry = 'PART' ORDER BY tb_data_visual_anals.retv_cnt ASC LIMIT 1 OFFSET (1 * 1);