회사 SQL 쿼리 기록2
SELECT * FROM tb_tchnlgy_growth_entrprs_evl_idx WHERE WGHTVAL_NM = '진희 test'; -- 삭제 전에 먼저 확인!
DELETE FROM tb_tchnlgy_growth_entrprs_evl_idx WHERE WGHTVAL_NM = '진희 test';
SELECT * FROM tb_tchnlgy_growth_entrprs_evl_idx;
select count(*)
from tb_tchnlgy_growth_entrprs_evl_idx
where WGHTVAL_CD like
select count(*)
from tb_tchnlgy_growth_entrprs_evl_idx
where UPPER_DP_SN = 27;
select WGHTVAL_CD, UPPER_DP_SN, count(UPPER_DP_SN)
from tb_tchnlgy_growth_entrprs_evl_idx
where WGHTVAL_CD = '1.2.1.'
limit 1;
select count(*)
from tb_tchnlgy_growth_entrprs_evl_idx
where UPPER_DP_SN = 27;
select UPPER_DP_SN
from tb_tchnlgy_growth_entrprs_evl_idx
where WGHTVAL_CD = '1.1.1.'
limit 1;
select count(UPPER_DP_SN) as upperSn
from tb_tchnlgy_growth_entrprs_evl_idx
where UPPER_DP_SN = (
select UPPER_DP_SN
from tb_tchnlgy_growth_entrprs_evl_idx
where WGHTVAL_CD = '2.1.'
limit 1
);
select UPPER_DP_SN
from tb_tchnlgy_growth_entrprs_evl_idx
where WGHTVAL_CD = '2.1.'
limit 1;
select * from tb_tchnlgy_growth_entrprs_evl_idx;
-- 테이블 구조만 복사하기
CREATE TABLE jinhee_board_evl_idx AS SELECT * FROM tb_tchnlgy_growth_entrprs_evl_idx WHERE 1=2;
-- 테이블이 존재할경우, 데이터만 복사하기(구조가 같은경우)
INSERT INTO jinhee_board_evl_idx SELECT * FROM tb_tchnlgy_growth_entrprs_evl_idx [WHERE];
-- 테이블 구조 및 데이터 복사하기
CREATE TABLE jinhee_board_evl_idx2 AS SELECT * FROM tb_tchnlgy_growth_entrprs_evl_idx [WHERE];
SELECT EVL_IDX_SN,EVL_INDUST_CL_CD,WGHTVAL_CD,WGHTVAL_NM,WGHTVAL_DC,CNSDR_MATTER,WGHTVAL_DP,UPPER_DP_SN,BEST_DP_SN
FROM jinhee_board_evl_idx
WHERE EVL_INDUST_CL_CD = 'MD'
and USE_YN = 'Y'
order by WGHTVAL_CD;
select * from jinhee_board_evl_idx2
where EVL_INDUST_CL_CD ='MD';
WGHTVAL_CD
-- 복사 테이블 jinhee_board_entrprs_wghtval, jinhee_board_evl_idx2 WGHTVAL_DC , TB_TCHNLGY_GROWTH_ENTRPRS_EVL_IDX(원본테이블) TB_TCHNLGY_GROWTH_ENTRPRS_WGHTVAL(가중치테이블)
select * from jinhee_board_evl_idx2
order by WGHTVAL_CD, WGHTVAL_DP;
SELECT count(UPPER_DP_SN) as upperSn
FROM jinhee_board_evl_idx2
WHERE UPPER_DP_SN = (SELECT UPPER_DP_SN
FROM jinhee_board_evl_idx2
WHERE WGHTVAL_CD = '1.1.'
limit 1
);
SELECT UPPER_DP_SN
FROM jinhee_board_evl_idx
WHERE WGHTVAL_CD = '1.1.'
limit 1;
SELECT EVL_IDX_SN -- 일련번호
,EVL_INDUST_CL_CD -- 분류
,WGHTVAL_DC -- /*가중치 설명*/
,CNSDR_MATTER -- 설명2
,
FROM jinhee_board_evl_idx2
WHERE EVL_IDX_SN = 6 -- 평가지표 일련번호
AND WGHTVAL_CD = '1.2.' -- 평가지표 코드
and WGHTVAL_DP = 2 -- 본인 뎁스
and UPPER_DP_SN = 1 -- 상위 뎁스
and BEST_DP_SN = 1 -- 최상위 뎁스
AND USE_YN = 'Y';
-- 기존 테이블의 구조(컬럼, 인덱스 등)를 그대로 복사
CREATE TABLE jinhee_board_entrprs_wghtval LIKE TB_TCHNLGY_GROWTH_ENTRPRS_WGHTVAL; -- 기존 테이블의 구조(컬럼, 인덱스 등)를 그대로 복사
INSERT INTO jinhee_board_evl_idx2 SELECT * FROM tb_tchnlgy_growth_entrprs_evl_idx; -- 기존 테이블의 데이터를 그대로 삽입
SELECT IDX.EVL_IDX_SN /*평가지표 일련번호*/
,IDX.EVL_INDUST_CL_CD /*평가 산업 분류 코드*/
,FN_GETCODENAME('COM171',IDX.EVL_INDUST_CL_CD) AS EVL_INDUST_CL_NM /*평가 산업 분류명*/
,WGHTVAL.WGHTVAL_SN /*가중치 일련번호*/
,WGHTVAL.TCHNLGY_GROWTH_ENTRPRS_TY /*기술성장기업 유형*/
,FN_GETCODENAME('COM160',WGHTVAL.TCHNLGY_GROWTH_ENTRPRS_TY) AS TCHNLGY_GROWTH_ENTRPRS_TY_MD /*기술성장기업 유형명_의약품*/
,FN_GETCODENAME('COM165',WGHTVAL.TCHNLGY_GROWTH_ENTRPRS_TY) AS TCHNLGY_GROWTH_ENTRPRS_TY_ME /*기술성장기업 유형명_의료기기*/
,WGHTVAL.CLINC_STEP /*임상단계*/
,IFNULL(FN_GETCODENAME('COM161',WGHTVAL.CLINC_STEP),'-') AS CLINC_STEP_NM /*임상단계명*/
,WGHTVAL.WGHTVAL_CD /*가중치 코드*/
,RPAD(REPLACE(WGHTVAL.WGHTVAL_CD,'.',''), '4', '0') AS WGHTVAL_CD_SUB
,IDX.WGHTVAL_NM /*가중치명*/
,WGHTVAL.WGHTVAL /*가중치*/
,WGHTVAL.LAST_WGHTVAL /*최종 가중치*/
,IDX.WGHTVAL_DC /*가중치 설명*/
,IDX.CNSDR_MATTER
,IDX.WGHTVAL_DP /*가중치 뎁스*/
,IDX.UPPER_DP_SN /*상위 뎁스 일련번호*/
,IDX.BEST_DP_SN /*최상위 뎁스 일련번호*/
FROM jinhee_board_evl_idx2 IDX
LEFT OUTER JOIN jinhee_board_entrprs_wghtval WGHTVAL
ON IDX.EVL_IDX_SN = WGHTVAL.EVL_IDX_SN
AND IDX.EVL_INDUST_CL_CD = WGHTVAL.EVL_INDUST_CL_CD
WHERE IDX.EVL_INDUST_CL_CD = 'MD'
AND IDX.EVL_IDX_SN = 2
AND IDX.USE_YN = 'Y';
CREATE TABLE jinhee_simple_board (
id INT AUTO_INCREMENT PRIMARY KEY, -- ID (게시글/댓글/대댓글 공통)
parent_id INT null, -- 부모 ID (NULL이면 게시글, 값이 있으면 댓글/대댓글)
super_id INT null, -- 최상위 부모 ID (NULL이면 게시글, 값이 있으면 댓글/대댓글)
user TEXT NULL, -- 작성자
depth INT null, -- 뎁스
content TEXT NOT NULL, -- 내용 (게시글/댓글/대댓글 공통)
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 작성 시간
FOREIGN KEY (parent_id) REFERENCES jinhee_simple_board(id) ON DELETE CASCADE -- 부모 삭제 시 대댓글도 삭제
);
select * from jinhee_simple_board;
where depth != 1
order by super_id , depth ;
SELECT * FROM jinhee_simple_board
ORDER BY super_id, id;
SELECT id, parent_id, super_id, user, depth, content
FROM jinhee_simple_board
ORDER BY super_id, date;
select * from jinhee_simple_board;
SELECT id, parent_id, super_id, user, depth, content
FROM jinhee_simple_board
ORDER BY parent_super_idid, depth;
SELECT * FROM jinhee_simple_board;
ORDER BY super_id, id;
-- CONNECT BY절 이용하여 계층형 구조 테이블 구현 하기.
-- 마리아 DB에서는 CONNECT BY절을 지원하지 않음.
SELECT id, parent_id, super_id, user, depth, content
FROM jinhee_simple_board
start with parent_id is null
connect by PRIOR id = parent_id
order SIBLINGS BY super_id;
-- Maria DB에서는 CONNECT BY절을 지원하지 않음.
-- 그래서 WITH RECURSIVE로 변환 해주어야함. 그럼 위 쿼리와 동일한 결과가 나온다.
-- 진희 작성 무한 대댓글 구현 WITH RECURSIVE 쿼리.
WITH RECURSIVE CTE AS (
-- 최상위 부모 찾기 (parent_id가 NULL인 경우)
SELECT id, parent_id, super_id, jinhee_simple_board.user as user, 1 AS depth, content, CAST(id AS CHAR(100)) AS tree_path
FROM jinhee_simple_board
WHERE parent_id IS NULL
UNION ALL
-- 재귀적으로 자식 노드 찾기
SELECT jb.id, jb.parent_id, jb.super_id, jb.user, bt.depth + 1 as depth, jb.content, CONCAT(bt.tree_path, ' -> ', jb.id) AS tree_path
FROM jinhee_simple_board jb
JOIN CTE as bt ON bt.id = jb.parent_id
)
select * from CTE
order by tree_path;
-- 이건 마지막 추가한 부모가 맨상단으로 가도록 정렬 쿼리.
order by super_id desc, tree_path;
-- 이부장님 계획 정렬된 쿼리.
select id, parent_id, super_id, user, depth, content
from jinhee_simple_board jsb
group by jsb.super_id, id
order by super_id desc, depth;
SELECT * FROM jinhee_simple_board;
DELETE FROM jinhee_simple_board;
DELETE FROM jinhee_simple_board where super_id = 85;
UPDATE jinhee_simple_board SET content = "내용 수정 테스트" WHERE id = 27 AND depth = 2;
SELECT VERSION();
insert into jinhee_simple_board (parent_id, super_id, user, depth, content, date) values (#{}, #{}, #{}, #{},#{});
select id, parent_id, super_id, user, depth, content from jinhee_simple_board;
-- id 의 next 시퀀스 값 가져오기.
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'jinhee_simple_board'
AND table_schema = DATABASE();
-- id 의 next 시퀀스 값 가져와서 super_id 칼럼에 넣어줌.
INSERT INTO jinhee_simple_board (parent_id, super_id, user, depth, content, date)
VALUES (#{parentId},
(SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'jinhee_simple_board'
AND table_schema = DATABASE()),
#{user},
1,
#{content},
now())
select EVL_IDX_SN, EVL_INDUST_CL_CD, WGHTVAL_CD, WGHTVAL_NM,WGHTVAL_DC, CNSDR_MATTER, WGHTVAL_DP, UPPER_DP_SN, BEST_DP_SN
from jinhee_board_evl_idx2
where EVL_IDX_SN = 6
and EVL_INDUST_CL_CD = 'MD'
and WGHTVAL_CD = '1.2.'
and USE_YN = 'Y';
-- 이건 마지막 추가한 부모가 맨상단으로 가도록 정렬 쿼리.
order by BEST_DP_SN desc, tree_path;
select * from jinhee_board_evl_idx2
where EVL_INDUST_CL_CD = 'MD';
select *
from jinhee_board_evl_idx2 jsb
where EVL_INDUST_CL_CD = 'MD'
group by jsb.BEST_DP_SN, EVL_IDX_SN
order by BEST_DP_SN , WGHTVAL_CD, ;
WITH RECURSIVE CTE AS (
SELECT
EVL_IDX_SN,
UPPER_DP_SN,
BEST_DP_SN,
1 AS WGHTVAL_DP,
WGHTVAL_NM,
WGHTVAL_DC,
CNSDR_MATTER,
CAST(EVL_IDX_SN AS CHAR(200)) AS tree_path -- 숫자를 문자열로 변환
FROM jinhee_board_evl_idx2
WHERE UPPER_DP_SN IS NULL -- 최상위 노드 선택
UNION ALL
SELECT
jb.EVL_IDX_SN,
jb.UPPER_DP_SN,
jb.BEST_DP_SN,
bt.WGHTVAL_DP + 1 AS WGHTVAL_DP,
jb.WGHTVAL_NM,
jb.WGHTVAL_DC,
jb.CNSDR_MATTER, -- 중복 필드 수정
CONCAT(bt.tree_path, ' > ', CAST(jb.EVL_IDX_SN AS CHAR(200))) AS tree_path
FROM jinhee_board_evl_idx2 jb
JOIN CTE AS bt ON bt.EVL_IDX_SN = jb.UPPER_DP_SN -- 재귀 조인
)
SELECT * FROM CTE
ORDER BY tree_path;
select EVL_IDX_SN, EVL_INDUST_CL_CD, WGHTVAL_CD, WGHTVAL_NM,WGHTVAL_DC, CNSDR_MATTER, 1 as WGHTVAL_DP,AST(id AS CHAR(100)) AS tree_path , UPPER_DP_SN, BEST_DP_SN,
from jinhee_board_evl_idx2
where EVL_INDUST_CL_CD = 'MD';
select *
from jinhee_simple_board
where super_id = '85'
order by
;
select * from tb_prjct_sumry_iem;
-- 이렇게 쿼리 작성하면 댓글의 해당 대댓글이 순차적 정렬되서 필요한 순서대로 나온다.
SELECT id, parent_id, super_id, user, depth, content,date
FROM jinhee_simple_board
ORDER BY super_id, date;
select * from jinhee_simple_board;
with recursive TMP as (
-- 최상위 부모
select id, parent_id, super_id, user, depth, content
from jinhee_simple_board
where parent_id is null
union all
-- 단계별 부보와 자식 맵핑
select J.id, J.parent_id, J.super_id, J.user, T.depth+1, J.content
from jinhee_simple_board
where parent_id is null
from TMP as T
inner join jinhee_simple_board as J
on T.id = J.parent_id
)
select *
from TMP;
WITH RECURSIVE TMP AS (
-- 최상위 부모 선택 (parent_id가 NULL인 항목)
SELECT
id,
parent_id,
super_id,
user,
1 as depth,
content
FROM jinhee_simple_board
WHERE parent_id IS NULL
UNION ALL
-- 부모와 자식 매핑
SELECT
J.id,
J.parent_id,
J.super_id,
J.user,
T.depth+1,
J.content
FROM jinhee_simple_board AS J
INNER JOIN TMP AS T ON J.parent_id = T.id
)
SELECT * FROM TMP;
SELECT
id,
parent_id,
super_id,
user,
1 as depth,
content
FROM jinhee_simple_board
WHERE parent_id IS null;w
SELECT * FROM jinhee_board_evl_idx2;
SELECT * FROM jinhee_board_evl_idx2;
-- TB_TCHNLGY_GROWTH_ENTRPRS_EVL_IDX(원본)
select *
from jinhee_board_evl_idx4 where EVL_INDUST_CL_CD = 'MD' ;
UPDATE jinhee_board_evl_idx3
SET UPPER_DP_SN = 2, BEST_DP_SN = 1, UPDUSR_SN=22
where EVL_IDX_SN =5;
-- 진희 작성 무한 대댓글 구현 WITH RECURSIVE 쿼리.(보산진 테이블에도 적용해보자.)
WITH RECURSIVE CTE AS (
SELECT
EVL_IDX_SN,
EVL_INDUST_CL_CD,
UPPER_DP_SN,
BEST_DP_SN,
1 AS WGHTVAL_DP,
WGHTVAL_CD,
WGHTVAL_NM,
WGHTVAL_DC,
CNSDR_MATTER,
CAST(LPAD(EVL_IDX_SN, 5, '0') AS CHAR(200)) AS tree_path
FROM TB_TCHNLGY_WGHTVAL_SUB_EVL_IDX
WHERE UPPER_DP_SN IS null
UNION all
SELECT
jb.EVL_IDX_SN,
jb.EVL_INDUST_CL_CD,
jb.UPPER_DP_SN,
jb.BEST_DP_SN,
bt.WGHTVAL_DP + 1 as WGHTVAL_DP,
jb.WGHTVAL_CD,
jb.WGHTVAL_NM,
jb.WGHTVAL_DC,
jb.CNSDR_MATTER,
CONCAT(bt.tree_path, ' > ', LPAD(jb.EVL_IDX_SN, 5, '0')) AS tree_path
FROM TB_TCHNLGY_WGHTVAL_SUB_EVL_IDX jb
JOIN CTE as bt ON bt.EVL_IDX_SN = jb.UPPER_DP_SN
)
select * from CTE
where EVL_INDUST_CL_CD = 'MD'
order by tree_path, WGHTVAL_CD;
SELECT
EVL_IDX_SN,
EVL_INDUST_CL_CD,
UPPER_DP_SN,
BEST_DP_SN,
WGHTVAL_DP,
WGHTVAL_CD,
WGHTVAL_NM,
WGHTVAL_DC,
CNSDR_MATTER,
CAST(EVL_IDX_SN AS CHAR(200)) AS tree_path
FROM jinhee_board_evl_idx3
WHERE UPPER_DP_SN IS null and EVL_INDUST_CL_CD = 'MD';
SELECT COUNT(WGHTVAL_DP) FROM TB_TCHNLGY_WGHTVAL_SUB_EVL_IDX where EVL_INDUST_CL_CD = 'MD'AND WGHTVAL_DP = 1;
SELECT * FROM TB_TCHNLGY_WGHTVAL_SUB_EVL_IDX where EVL_INDUST_CL_CD = 'MD';
delete from TB_TCHNLGY_WGHTVAL_SUB_EVL_IDX where EVL_IDX_SN = 151;
UPDATE TCHNLGY_WGHTVAL_SUB_EVL_IDX
SET UPPER_DP_SN = 1, BEST_DP_SN=1
where EVL_IDX_SN =1;
UPDATE jinhee_board_evl_idx3
SET WGHTVAL_CD = '1.1.4.'
where EVL_IDX_SN =59;
<!-- 더 간략한 이부장님 쿼리 -->
select EVL_IDX_SN, UPPER_DP_SN, BEST_DP_SN, WGHTVAL_CD,WGHTVAL_DP
from jinhee_board_evl_idx4 jsb
where EVL_INDUST_CL_CD = 'MD'
group by jsb.BEST_DP_SN, EVL_IDX_SN
order by BEST_DP_SN, WGHTVAL_DP;
-- 테이블 구조와 데이터 까지 그대로 복사하는 쿼리
CREATE TABLE jinhee_board_evl_idx4 LIKE TB_TCHNLGY_GROWTH_ENTRPRS_EVL_IDX;
INSERT INTO jinhee_board_evl_idx4 SELECT * FROM TB_TCHNLGY_GROWTH_ENTRPRS_EVL_IDX;
-- 테이블 구조와 데이터 까지 그대로 복사하는 쿼리
CREATE TABLE jinhee_sample_copy_tb_realm_accto LIKE tb_realm_accto_cl_sttus;
INSERT INTO jinhee_sample_copy_tb_realm_accto SELECT * FROM tb_realm_accto_cl_sttus;
-- 테이블명 변경
RENAME TABLE TCHNLGY_WGHTVAL_SUB_EVL_IDX TO TB_TCHNLGY_WGHTVAL_SUB_EVL_IDX; wghtvalGROWTH_ENTRPRS_EVL_IDX;
select * from tb_realm_accto_cl_sttus where REALM_CD = 'MDLP';
delete from tb_realm_accto_cl_sttus;
select * from tb_realm_accto_cl_sttus;
--- 회의실 예약 프로그램 SQL쿼리 ---
CREATE SEQUENCE RESERVATION2_SEQ START WITH 1 INCREMENT BY 1;
CREATE TABLE RESERVATION2 (
ID NUMBER DEFAULT RESERVATION2_SEQ.NEXTVAL PRIMARY KEY,
USER_ID VARCHAR2(50) NOT NULL,
PASSWORD VARCHAR2(100) NOT NULL,
RESERVE_DATE VARCHAR2(100) NOT NULL,
START_TIME VARCHAR2(50) NOT NULL,
END_TIME VARCHAR2(50) NOT NULL,
PURPOSE VARCHAR2(255)
);
SELECT * FROM RESERVATION2;
ALTER TABLE RESERVATION2 RENAME COLUMN PURPOSE TO TITLE;
hi