728x90
$query = "
select
SQL_CALC_FOUND_ROWS
*
, ifnull(mic1.ctgr_name, '') as ctgr_name1
, ifnull(mic2.ctgr_name, '') as ctgr_name2
, ifnull(mic3.ctgr_name, '') as ctgr_name3
from
(
select
@rownum:=@rownum+1 as num
, mi.*
, ifnull((select sum(qty) as sum_total from test_11 ahs where ahs.it_11 = mi.it_22 group by pp_id ), 0) as sum_total
from test_dd mi
,(SELECT @rownum:=0) r
where mi.it_no > 0
and mi.it_use_yn = 'Y'
";
if (!empty($search['sch_it_nm'])) {
$query .= " and it_nm like '%".$search['sch_it_nm']."%'";
}
if (!empty($search['sch_it_div'])) {
$query .= " and it_div = '".$search['sch_it_div']."'";
}
$query .= " and it_use_yn ='Y'";
$query .= "
order by mi.it_no asc
)ee
left outer join test_dd_category mic1 on mic1.ctgr_deg='1' and ee.clt_ctgr1 = mic1.ctgr_code
left outer join test_dd_category mic2 on mic2.ctgr_deg='2' and concat(ee.clt_ctgr1,ee.clt_ctgr2) = mic2.ctgr_code
left outer join test_dd_category mic3 on mic3.ctgr_deg='3' and concat(ee.clt_ctgr1,ee.clt_ctgr2) = mic2.ctgr_code and concat(ee.clt_ctgr1,ee.clt_ctgr2,ee.clt_ctgr3) = mic3.ctgr_code
order by num desc
limit ".$rowno." , ".$rowperpage."
";
$result['page_list'] = $this->db->query($query)->result_array();
$result['total_cnt'] = $this->db->query("SELECT FOUND_ROWS() AS total_cnt;")->row()->total_cnt;
return $result;
// SQL_CALC_FOUND_ROWS : LIMIT 0, 10을 지정하여 10개의
// row만 가져온다고 하더라도 LIMIT이 없을 때의 쿼리 결과와 같은 row 수를 계산하여 가져온다■ IFNULL
SELECT IFNULL(필드명, "대체할 값") FROM 테이블명;
은 해당 필드의 값이 NULL을 반환할때 다른 값으로 출력할 수 있도록 하는 함수이다.
■ LADP / RADP
■ insert Code
public function insert(){
$insert_id = 0;
try{
$insertParam = func_get_arg(0);
//Trans
$this->db->trans_start();
$this->db->insert($this->table, $insertParam);
$insert_id = $this->db->insert_id();
//Trans commit
$this->db->trans_complete();
// $this->lastQuery();
// exit();
} catch (MY_Exception $e) {
$this->db->trans_rollback();
throw new MY_Exception(__FUNCTION__.$e->getMessage());
}
return $insert_id;
}■ select
public function osJoinList($page = 1, $pageList = 5, $search){
//Paging
if(is_numeric($page) == false) $page = 1;
if($page < 0) $page = 1;
$limit_ofset = ($page-1) * $pageList;
$query = "
select
SQL_CALC_FOUND_ROWS
*
, mc.emp_dd , mc.clt_dd , mc.sale_dd
from
(
select
@rownum:=@rownum+1 as num
, mqo.*
from test_order mqo
,(SELECT @rownum:=0) r
where mqo.qte_ord_no > 0
";
if (!empty($search['startdate']) && !empty($search['enddate'])) {
$query .= " AND (concat(sale_year, '-', sale_month, '-', LPAD(sale_day, 2, 0)) BETWEEN '{$search['startdate']}' AND '{$search['enddate']}') ";
}
$query .= "
order by mqo.ord_no asc
)ee
left outer join test22 mc on mc.ord_no = ee.ord_no
order by num desc
limit ".$limit_ofset." , ".$pageList."
";
// $this->echo_r($query);
// exit();
$result['page_list'] = $this->db->query($query)->result_array();
$result['total_cnt'] = $this->db->query("SELECT FOUND_ROWS() AS total_cnt;")->row()->total_cnt;
return $result;
}■ sql select 결과를 insert
INSERT INTO test_release ( no_1 ,no_2 ,no_3)
SELECT
j.job_no
,NOW()
,j.select_id
FROM test22 b INNER JOIN job_cc j ON b.it_11 =j.it_22
WHERE j.job_no=158;■ sql update
/**
* @param $qty
* @param $test_no
* @return mixed
* @throws MY_Exception
*/
public function minusQty($qty, $test_no){
try {
$query = "update ".$this->table."
set qty = qty - '".$qty."'
";
$query .= " where test_no = '".$test_no."'";
$resultSet = $this->db->query($query);
} catch (MY_Exception $e) {
throw new MY_Exception(__FUNCTION__.$e->getMessage());
}
return $resultSet;
}■ sql union insert
INSERT INTO test_ddd ( it_id , test_nm )
SELECT it_id, test_nm
FROM test_sss A
, (SELECT '사유1' as test_nm
UNION ALL
SELECT '사유2'
UNION ALL
SELECT '사유3'
UNION ALL
SELECT '사유4'
UNION ALL
SELECT '사유5'
UNION ALL
SELECT '사유6'
UNION ALL
SELECT '사유7'
) B
WHERE it_check IN ('pr11','pr22') ■ sql key 이용하여 select 한뒤 insert 또는 update
$query = "
INSERT INTO test_summary
(
id_11,
id_22,
id_33,
id_44,
id_55,
id_66,
id_77
)";
$query .= " VALUES ";
$query .= " ( ";
$query .= " '{$id_11}', ";
$query .= " '{$id_22}', ";
$query .= " '{$id_33}', ";
$query .= " '{$id_44}', ";
$query .= " '{$id_55}', ";
$query .= " '{$id_66}', ";
$query .= " now() ";
$query .= " ) ";
$query .= " ON DUPLICATE KEY UPDATE "; //key 를 제외한 나머지 컬럼 나열
$query .= " ppk_qty = VALUES (ppk_qty),";
$query .= " ppk_s_qty = VALUES (ppk_s_qty),";
$query .= " ppk_plan = VALUES (ppk_plan),";
$query .= " ppk_time = VALUES (ppk_time),";
$query .= " ppk_time = VALUES (ppk_time)";
$this->db->query($query);■ sql 컬럼 추가, 삭제 , 수정
== 컬럼 추가 ==
ALTER TABLE test_order
add test_type ENUM('ss1','ss2') DEFAULT 'ss1' comment '컬럼을 추가1';
ADD test_column INT(11) DEFAULT 0 comment '컬럼을 추가2',
ADD test_ss DATE NULL DEFAULT NULL COMMENT '컬럼을 추가3';
https://extbrain.tistory.com/39■ sql 예시
public function list($page = 1, $pageList = 5, $search){
//Paging
if(is_numeric($page) == false) $page = 1;
if($page < 0) $page = 1;
$limit_ofset = ($page-1) * $pageList;
//# 1. 쿼리형
$query = "
select
SQL_CALC_FOUND_ROWS
*
, mc.s_name
from
(
select
@rownum:=@rownum+1 as num
, mso.*
, IFNULL((
select sum(qty) as order_qty from test_ss sh
right outer join test_ddc mit on mit.it_id = sh.it_id
where sh.sale_ord_no = mso.sale_ord_no and mit.it_div in ('prdc','smpd')
),0 ) AS order_qty
, IFNULL((select sum(qty) as ship11_qty from testip_dss sh where sh.sale_ord_no = mso.sale_ord_no ),0) AS ship_order_qty
, IFNULL((select count(status) as status from testip_sds sh where sh.sale_ord_no = mso.sale_ord_no and status='start' GROUP BY sale_ord_no),0) AS status
, (SELECT round(SUM(request),4) AS need_ss FROM (
SELECT
IFNULL((
SELECT sum((seq.make_moq / mi.moq_qty) * si.qty) from test_seq seq where seq.it_id = si.it_id
) , 0 ) AS request
, si.sale_ord_no
from test_cc si
LEFT OUTER JOIN test_dd mi ON mi.it_id = si.it_id where mi.it_div in ('ss11','ss22')
) ss where ss.sale_ord_no = mso.sale_ord_no ) as need_ss
from test_ord mso
,(SELECT @rownum:=0) r
where mso.sale_ord_no > 0
and mso.trash_yn = 'N' and mso.sale_order_type='order'
";
if(!empty($search['startdate']) && !empty($search['enddate'])) {
$query .= " AND (concat(s_year, '-', LPAD(s_month, 2, 0), '-', LPAD(s_day, 2, 0)) BETWEEN '{$search['startdate']}' AND '{$search['enddate']}') ";
}
if (!empty($search['s_nmbr'])) {
$query .= " AND mso.s_nmbr like '".$search['s_nmbr']."%' ";
}
$query .= "
order by mso.s_ord_no asc
)ee
left outer join test_dd mc on mc.clt_id = ee.clt_id
order by num desc
limit ".$limit_ofset." , ".$pageList."
";
// $this->echo_r($query);
// exit();
$result['page_list'] = $this->db->query($query)->result_array();
$result['total_cnt'] = $this->db->query("SELECT FOUND_ROWS() AS total_cnt;")->row()->total_cnt;
return $result;
}■ insert into values => select
■ 트리거
BEGIN
SET @whse_qty = IFNULL(NEW.qty,0) -0;
SET @whse_amt = IFNULL(NEW.amt,0) -0;
IF @whse_qty <> 0 THEN
INSERT INTO test_history SET
it_id=NEW.it_id
,warehouse_id=NEW.warehouse_id
,bf_whse_qty=0
,do_whse_qty= @whse_qty
,af_whse_qty= @whse_qty
,bf_whse_amt=0
,do_whse_amt= @whse_amt
,af_whse_amt= @whse_amt
,rgst_dt=NOW()
,emp_id =NEW.last_emp_id
,bigo=NEW.last_bigo
,ref_action =NEW.last_ref_action
,ref_table =NEW.last_ref_table
,ref_no =NEW.last_ref_no
,ref_chit_id =NEW.last_ref_chit_id
;
END IF ;
END■ MySQL에 일시를 표시하는 함수 now(), current_timestamp()
now(3), now(6)은 밀리세컨즈 자릿수를 의미 (최대: 6)
SELECT now(), now(3), now(6), current_timestamp(); now() : 2021-08-11 23:57:04, now(3) : 2021-08-11 23:57:04.103,
now(6) : 2021-08-11 23:57:04.103821, current_timestamp() : 2021-08-11 23:57:04■ 테이블, 데이터 복사
create table b1 select * from a1 where false; // 테이블 생성, 데이터 복사안함
create table b1 select * from a1 // 테이블 생성 , 데이터 복사
728x90