372 lines
19 KiB
SQL
372 lines
19 KiB
SQL
-- 대구광역시(특히 북구) 기준 기본코드 초기 데이터 스크립트
|
||
-- 실행 순서:
|
||
-- 1) code_kind / code_detail 테이블 생성
|
||
-- 2) code_kind 데이터 입력
|
||
-- 3) code_detail 데이터 입력
|
||
|
||
/* =========================================================
|
||
* 1. 테이블 생성 (이미 존재하면 생략 가능)
|
||
* =======================================================*/
|
||
|
||
CREATE TABLE IF NOT EXISTS `code_kind` (
|
||
`ck_idx` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '코드종류 PK',
|
||
`ck_code` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '종류 코드(A~Y 등)',
|
||
`ck_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '종류 명칭',
|
||
`ck_state` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=사용, 0=미사용',
|
||
`ck_regdate` DATETIME NOT NULL COMMENT '등록일시',
|
||
PRIMARY KEY (`ck_idx`),
|
||
UNIQUE KEY `uk_ck_code` (`ck_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='기본코드 종류';
|
||
|
||
CREATE TABLE IF NOT EXISTS `code_detail` (
|
||
`cd_idx` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '세부코드 PK',
|
||
`cd_ck_idx` INT UNSIGNED NOT NULL COMMENT 'code_kind FK',
|
||
`cd_code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '세부 코드',
|
||
`cd_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '세부 명칭',
|
||
`cd_sort` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '정렬 순서',
|
||
`cd_state` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=사용, 0=미사용',
|
||
`cd_regdate` DATETIME NOT NULL COMMENT '등록일시',
|
||
PRIMARY KEY (`cd_idx`),
|
||
KEY `idx_cd_ck_idx` (`cd_ck_idx`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='세부 기본코드';
|
||
|
||
|
||
/* =========================================================
|
||
* 2. code_kind 초기 데이터
|
||
* (중복 실행 시 uk_ck_code 제약 때문에 에러 가능)
|
||
* =======================================================*/
|
||
|
||
INSERT INTO `code_kind` (`ck_code`, `ck_name`, `ck_state`, `ck_regdate`) VALUES
|
||
('A', '도/특별시/광역시 구분', 1, NOW()),
|
||
('B', '특별시/광역시/시/군 코드', 1, NOW()),
|
||
('C', '구 코드', 1, NOW()),
|
||
('D', '동 코드', 1, NOW()),
|
||
('E', '봉투구분', 1, NOW()),
|
||
('F', '봉투재질', 1, NOW()),
|
||
('G', '용량별', 1, NOW()),
|
||
('H', '무상지급 구분', 1, NOW()),
|
||
('I', '판매형태', 1, NOW()),
|
||
('J', '반품형태', 1, NOW()),
|
||
('K', '반품사유', 1, NOW()),
|
||
('L', '지정판매소 변경사유', 1, NOW()),
|
||
('M', '수불구분', 1, NOW()),
|
||
('N', '동판종류', 1, NOW()),
|
||
('O', '봉투명', 1, NOW()),
|
||
('P', '작업권한', 1, NOW()),
|
||
('Q', '예산과목', 1, NOW()),
|
||
('R', '은행목록', 1, NOW()),
|
||
('S', '소속', 1, NOW()),
|
||
('T', '직위', 1, NOW());
|
||
|
||
|
||
/* =========================================================
|
||
* 3. code_detail 초기 데이터
|
||
* 3-1. 행정구역 관련 (대구광역시, 북구 및 북구 동)
|
||
* =======================================================*/
|
||
|
||
-- A: 도/특별시/광역시 구분 – 대구는 광역시만 사용
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, '11', '광역시', 10, 1, NOW()
|
||
FROM code_kind WHERE ck_code = 'A';
|
||
|
||
-- B: 특별시/광역시/시/군 코드 – 대구광역시
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, '1102', '대구광역시', 10, 1, NOW()
|
||
FROM code_kind WHERE ck_code = 'B';
|
||
|
||
-- C: 구코드 – 북구 (CSV: 110209, 북구)
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, '110209', '북구', 10, 1, NOW()
|
||
FROM code_kind WHERE ck_code = 'C';
|
||
|
||
-- D: 동코드 – 대구 북구의 동들 (11020901~11020928)
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '11020901' AS cd_code, '검단동' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '11020902', '고성동1가', 20 UNION ALL
|
||
SELECT '11020903', '고성동2가', 30 UNION ALL
|
||
SELECT '11020904', '고성동3가', 40 UNION ALL
|
||
SELECT '11020905', '관음동', 50 UNION ALL
|
||
SELECT '11020906', '구암동', 60 UNION ALL
|
||
SELECT '11020907', '국우동', 70 UNION ALL
|
||
SELECT '11020908', '금호동', 80 UNION ALL
|
||
SELECT '11020909', '노곡동', 90 UNION ALL
|
||
SELECT '11020910', '노원동1가', 100 UNION ALL
|
||
SELECT '11020911', '노원동2가', 110 UNION ALL
|
||
SELECT '11020912', '노원동3가', 120 UNION ALL
|
||
SELECT '11020913', '대현동', 130 UNION ALL
|
||
SELECT '11020914', '도남동', 140 UNION ALL
|
||
SELECT '11020915', '동변동', 150 UNION ALL
|
||
SELECT '11020916', '동천동', 160 UNION ALL
|
||
SELECT '11020917', '동호동', 170 UNION ALL
|
||
SELECT '11020918', '매천동', 180 UNION ALL
|
||
SELECT '11020919', '복현동', 190 UNION ALL
|
||
SELECT '11020920', '사수동', 200 UNION ALL
|
||
SELECT '11020921', '산격동', 210 UNION ALL
|
||
SELECT '11020922', '서변동', 220 UNION ALL
|
||
SELECT '11020923', '연경동', 230 UNION ALL
|
||
SELECT '11020924', '읍내동', 240 UNION ALL
|
||
SELECT '11020925', '조야동', 250 UNION ALL
|
||
SELECT '11020926', '칠성동1가', 260 UNION ALL
|
||
SELECT '11020927', '칠성동2가', 270 UNION ALL
|
||
SELECT '11020928', '침산동', 280
|
||
) AS v
|
||
ON code_kind.ck_code = 'D';
|
||
|
||
|
||
/* =========================================================
|
||
* 3-2. 봉투·재질·용량·무상·판매형태 (E~I)
|
||
* =======================================================*/
|
||
|
||
-- E: 봉투구분
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '10' AS cd_code, '일반용' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '20' AS cd_code, '공공용' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '30' AS cd_code, '무료용' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '40' AS cd_code, '공동주택용' AS cd_name, 40 AS cd_sort UNION ALL
|
||
SELECT '50' AS cd_code, '재사용' AS cd_name, 50 AS cd_sort UNION ALL
|
||
SELECT '60' AS cd_code, '음식물 봉투' AS cd_name, 60 AS cd_sort UNION ALL
|
||
SELECT '61' AS cd_code, '음식물 스티커' AS cd_name, 70 AS cd_sort UNION ALL
|
||
SELECT '62' AS cd_code, '음식물 용기' AS cd_name, 80 AS cd_sort UNION ALL
|
||
SELECT '70' AS cd_code, '대형폐기물 스티커' AS cd_name, 90 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'E';
|
||
|
||
-- F: 봉투재질
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '2' AS cd_code, '고밀도' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '6' AS cd_code, 'PP마대' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '7' AS cd_code, '스티커' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '8' AS cd_code, '용기' AS cd_name, 40 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'F';
|
||
|
||
-- G: 용량별
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '10' AS cd_code, '2L' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '11' AS cd_code, '3L' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '12' AS cd_code, '5L' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '13' AS cd_code, '10L' AS cd_name, 40 AS cd_sort UNION ALL
|
||
SELECT '15' AS cd_code, '20L' AS cd_name, 50 AS cd_sort UNION ALL
|
||
SELECT '16' AS cd_code, '30L' AS cd_name, 60 AS cd_sort UNION ALL
|
||
SELECT '17' AS cd_code, '50L' AS cd_name, 70 AS cd_sort UNION ALL
|
||
SELECT '18' AS cd_code, '60L' AS cd_name, 80 AS cd_sort UNION ALL
|
||
SELECT '19' AS cd_code, '75L' AS cd_name, 90 AS cd_sort UNION ALL
|
||
SELECT '20' AS cd_code, '100L' AS cd_name, 100 AS cd_sort UNION ALL
|
||
SELECT '21' AS cd_code, '120L' AS cd_name, 110 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'G';
|
||
|
||
-- H: 무상지급 구분
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1' AS cd_code, '시설보호대상자' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '10' AS cd_code, '생보자' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '3' AS cd_code, '대한민국무공수훈자' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '4' AS cd_code, '사회복지시설' AS cd_name, 40 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'H';
|
||
|
||
-- I: 판매형태
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1' AS cd_code, '무상지급' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '2' AS cd_code, '일반판매' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '3' AS cd_code, '관내판매' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '4' AS cd_code, '교환판매' AS cd_name, 40 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'I';
|
||
|
||
|
||
/* =========================================================
|
||
* 3-3. 반품·수불·동판·봉투명·작업권한·예산·은행 (J~R)
|
||
* =======================================================*/
|
||
|
||
-- J: 반품형태
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1' AS cd_code, '일반반품' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '2' AS cd_code, '관내반품' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '3' AS cd_code, '반품' AS cd_name, 30 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'J';
|
||
|
||
-- K: 반품사유
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1' AS cd_code, '봉투훼손' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '2' AS cd_code, '지정판매소 폐업' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '4' AS cd_code, '스티커 미사용' AS cd_name, 30 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'K';
|
||
|
||
-- L: 지정판매소 변경사유
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1' AS cd_code, '지정판매소변경사유1' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '2' AS cd_code, '지정판매소변경사유2' AS cd_name, 20 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'L';
|
||
|
||
-- M: 수불구분
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '10' AS cd_code, '실사입고' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '11' AS cd_code, '신청입고' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '12' AS cd_code, '무료입고' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '13' AS cd_code, '발주입고' AS cd_name, 40 AS cd_sort UNION ALL
|
||
SELECT '16' AS cd_code, '반품입고(정상)' AS cd_name, 50 AS cd_sort UNION ALL
|
||
SELECT '17' AS cd_code, '반품입고(불용)' AS cd_name, 60 AS cd_sort UNION ALL
|
||
SELECT '18' AS cd_code, '이동입고' AS cd_name, 70 AS cd_sort UNION ALL
|
||
SELECT '1A' AS cd_code, '교환입고' AS cd_name, 80 AS cd_sort UNION ALL
|
||
SELECT '1B' AS cd_code, '기타입고' AS cd_name, 90 AS cd_sort UNION ALL
|
||
SELECT '20' AS cd_code, '실사출고' AS cd_name, 100 AS cd_sort UNION ALL
|
||
SELECT '21' AS cd_code, '신청불출' AS cd_name, 110 AS cd_sort UNION ALL
|
||
SELECT '22' AS cd_code, '무료불출' AS cd_name, 120 AS cd_sort UNION ALL
|
||
SELECT '23' AS cd_code, '일반판매' AS cd_name, 130 AS cd_sort UNION ALL
|
||
SELECT '24' AS cd_code, '공공출고' AS cd_name, 140 AS cd_sort UNION ALL
|
||
SELECT '26' AS cd_code, '반품출고(정상)' AS cd_name, 150 AS cd_sort UNION ALL
|
||
SELECT '27' AS cd_code, '반품출고(불용)' AS cd_name, 160 AS cd_sort UNION ALL
|
||
SELECT '28' AS cd_code, '이동출고' AS cd_name, 170 AS cd_sort UNION ALL
|
||
SELECT '29' AS cd_code, '파기처리' AS cd_name, 180 AS cd_sort UNION ALL
|
||
SELECT '2A' AS cd_code, '교환출고' AS cd_name, 190 AS cd_sort UNION ALL
|
||
SELECT '2B' AS cd_code, '기타출고' AS cd_name, 200 AS cd_sort UNION ALL
|
||
SELECT '99' AS cd_code, '시찰' AS cd_name, 210 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'M';
|
||
|
||
-- N: 동판종류
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '10' AS cd_code, '동판 종류1' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '20' AS cd_code, '동판 종류2' AS cd_name, 20 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'N';
|
||
|
||
-- O: 봉투명 (상세 봉투 코드)
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '10112' AS cd_code, '일반용 3L' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '10122' AS cd_code, '일반용 5L' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '10132' AS cd_code, '일반용 10L' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '10152' AS cd_code, '일반용 20L' AS cd_name, 40 AS cd_sort UNION ALL
|
||
SELECT '10162' AS cd_code, '일반용 30L' AS cd_name, 50 AS cd_sort UNION ALL
|
||
SELECT '10172' AS cd_code, '일반용 50L' AS cd_name, 60 AS cd_sort UNION ALL
|
||
SELECT '10192' AS cd_code, '일반용 75L' AS cd_name, 70 AS cd_sort UNION ALL
|
||
SELECT '10202' AS cd_code, '일반용 100L' AS cd_name, 80 AS cd_sort UNION ALL
|
||
SELECT '20172' AS cd_code, '공공용 50L' AS cd_name, 90 AS cd_sort UNION ALL
|
||
SELECT '40152' AS cd_code, '공동주택용 20L' AS cd_name, 100 AS cd_sort UNION ALL
|
||
SELECT '40182' AS cd_code, '공동주택용 60L' AS cd_name, 110 AS cd_sort UNION ALL
|
||
SELECT '40212' AS cd_code, '공동주택용 120L' AS cd_name, 120 AS cd_sort UNION ALL
|
||
SELECT '50122' AS cd_code, '재사용 5L' AS cd_name, 130 AS cd_sort UNION ALL
|
||
SELECT '60102' AS cd_code, '음식물 2L' AS cd_name, 140 AS cd_sort UNION ALL
|
||
SELECT '60132' AS cd_code, '음식물 10L' AS cd_name, 150 AS cd_sort UNION ALL
|
||
SELECT '61107' AS cd_code, '음식물 스티커 2L' AS cd_name, 160 AS cd_sort UNION ALL
|
||
SELECT '61117' AS cd_code, '음식물 스티커 3L' AS cd_name, 170 AS cd_sort UNION ALL
|
||
SELECT '61127' AS cd_code, '음식물 스티커 5L' AS cd_name, 180 AS cd_sort UNION ALL
|
||
SELECT '61157' AS cd_code, '음식물 스티커 20L' AS cd_name, 190 AS cd_sort UNION ALL
|
||
SELECT '61187' AS cd_code, '음식물 스티커 60L' AS cd_name, 200 AS cd_sort UNION ALL
|
||
SELECT '61217' AS cd_code, '음식물 스티커 120L' AS cd_name, 210 AS cd_sort UNION ALL
|
||
SELECT '62128' AS cd_code, '음식물용기 5L' AS cd_name, 220 AS cd_sort UNION ALL
|
||
SELECT '62158' AS cd_code, '음식물용기 20L' AS cd_name, 230 AS cd_sort UNION ALL
|
||
SELECT '70707' AS cd_code, '폐기물 스티커 1,000원' AS cd_name, 240 AS cd_sort UNION ALL
|
||
SELECT '70727' AS cd_code, '폐기물 스티커 3,000원' AS cd_name, 250 AS cd_sort UNION ALL
|
||
SELECT '70747' AS cd_code, '폐기물 스티커 5,000원' AS cd_name, 260 AS cd_sort UNION ALL
|
||
SELECT '70787' AS cd_code, '폐기물 스티커 10,000원'AS cd_name, 270 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'O';
|
||
|
||
-- P: 작업권한 (예시 – 실제 운영 정책에 맞게 확장 가능)
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, '111', '등록-다시', 10, 1, NOW()
|
||
FROM code_kind WHERE ck_code = 'P';
|
||
|
||
-- Q: 예산과목 (대표 코드만 선행 입력)
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1000' AS cd_code, '관' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '1001' AS cd_code, '보건및생활환경개선비' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '2000' AS cd_code, '항' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '2010' AS cd_code, '환경관리' AS cd_name, 40 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'Q';
|
||
|
||
-- R: 은행목록
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '4' AS cd_code, '국민은행' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '7' AS cd_code, '수협' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '20' AS cd_code, '우리은행' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '32' AS cd_code, '부산은행' AS cd_name, 40 AS cd_sort UNION ALL
|
||
SELECT '99' AS cd_code, '새마을금고' AS cd_name, 50 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'R';
|
||
|
||
|
||
/* =========================================================
|
||
* 3-4. 소속(S), 직위(T)
|
||
* =======================================================*/
|
||
|
||
-- S: 소속
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1' AS cd_code, '청소과' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '2' AS cd_code, '청소행정과' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '3' AS cd_code, '자원순환과' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '4' AS cd_code, '도시미화과' AS cd_name, 40 AS cd_sort UNION ALL
|
||
SELECT '5' AS cd_code, '영업부' AS cd_name, 50 AS cd_sort UNION ALL
|
||
SELECT '6' AS cd_code, '관리부' AS cd_name, 60 AS cd_sort UNION ALL
|
||
SELECT '7' AS cd_code, '기술부' AS cd_name, 70 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'S';
|
||
|
||
-- T: 직위
|
||
INSERT INTO `code_detail` (`cd_ck_idx`, `cd_code`, `cd_name`, `cd_sort`, `cd_state`, `cd_regdate`)
|
||
SELECT ck_idx, v.cd_code, v.cd_name, v.cd_sort, 1, NOW()
|
||
FROM code_kind
|
||
JOIN (
|
||
SELECT '1' AS cd_code, '7급' AS cd_name, 10 AS cd_sort UNION ALL
|
||
SELECT '2' AS cd_code, '8급' AS cd_name, 20 AS cd_sort UNION ALL
|
||
SELECT '3' AS cd_code, '9급' AS cd_name, 30 AS cd_sort UNION ALL
|
||
SELECT '4' AS cd_code, '기능' AS cd_name, 40 AS cd_sort UNION ALL
|
||
SELECT '5' AS cd_code, '계장' AS cd_name, 50 AS cd_sort UNION ALL
|
||
SELECT '6' AS cd_code, '과장' AS cd_name, 60 AS cd_sort UNION ALL
|
||
SELECT '7' AS cd_code, '사장' AS cd_name, 70 AS cd_sort UNION ALL
|
||
SELECT '8' AS cd_code, '상무' AS cd_name, 80 AS cd_sort
|
||
) AS v
|
||
ON code_kind.ck_code = 'T';
|
||
|