데이터베이스 질문과 답변
SELECT
`mt_recruitment`.`no` AS `recruitment_no`,
`mt_recruitment`.`status` AS `recruitment_status`,
`mt_contract`.`status` AS `contract_status`,
`mt_recruitment`.`start_date` AS `recruitment_start_date`,
IF(`mt_recruitment`.`status` < 2, NULL, (SELECT `start_date` FROM `mt_contract_partner` WHERE `mt_contract_id` = `mt_contract`.`id` AND `deleted_at` is null)) AS `occupancy_date`,
`mt_recruitment`.`id` AS `mt_recruitment_id`
FROM
`mt_recruitment`
LEFT JOIN `mt_contract` ON
(
IF(
`mt_recruitment`.`mt_room_id` is null,
`mt_contract`.`mt_section_id` = `mt_recruitment`.`mt_section_id`,
`mt_contract`.`mt_room_id` = `mt_recruitment`.`mt_room_id`
)
) AND `mt_contract`.`deleted_at` is null
WHERE
`mt_recruitment`.`deleted_at` is null;
관계를 설명드리면,
mt_recruitment : mt_contract = 1 : N
mt_contract_partner : mt_contract = 1 : 1
입니다.
요점은,
mt_recruitment.mt_room_id 가 NULL일 경우 mt_recruitment.mt_section_id 을 사용해야 하고
mt_recruitment.mt_section_id 가 NULL일 경우 mt_recruitment.mt_room_id 을 사용해야 합니다.
IF문을 사용했기 때문에 풀스캔이 되고 있습니다.
mt_contract 의 mt_section_id과 mt_room_id 외래키가 걸려 있습니다.
개선한 쿼리는 아래와 같이 했는 데, 혹시 더 좋은 방법이 있을까요?
감사합니다.
select `mt_recruitment`.`no` AS `recruitment_no`, `mt_recruitment`.`status` AS `recruitment_status`, IF(`e`.`status` is null, `d`.`status`, `e`.`status`), `mt_recruitment`.`start_date` AS `recruitment_start_date`, IF(`mt_recruitment`.`status` < 2, NULL, (SELECT `start_date` FROM `mt_contract_partner` WHERE `mt_contract_id` = IF(`e`.`id` is null, `d`.`id`, `e`.`id`) AND `deleted_at` is null)) AS `occupancy_date`, `mt_recruitment`.`id` AS `mt_recruitment_id` from mt_recruitment left join mt_contract e on e.mt_room_id = mt_recruitment.mt_room_id and e.deleted_at is null left join mt_contract d on d.mt_section_id = mt_recruitment.mt_section_id and d.deleted_at is null WHERE `mt_recruitment`.`deleted_at` is null GROUP BY `mt_recruitment`.`id`;
by 마농
[2019.01.30 16:44:41]
따옴표(`) 사용과 테이블명을 full 로 사용하는 것은
제 관점에서는 쿼리 가독성이 떨어지는 것 같습니다.
간단한 알리아스 사용하는 것이 보기 좋은 것 같습니다.
SELECT a.no AS recruitment_no
, a.status AS recruitment_status
, IFNULL(b.status, c.status) AS contract_status
, a.start_date AS recruitment_start_date
, d.start_date AS occupancy_date
, a.id AS mt_recruitment_id
FROM mt_recruitment a
LEFT JOIN mt_contract b
ON a.mt_room_id IS NOT NULL
AND a.mt_room_id = b.mt_room_id
AND b.deleted_at IS NULL
LEFT JOIN mt_contract c
ON a.mt_room_id IS NULL
AND a.mt_section_id = c.mt_section_id
AND c.deleted_at IS NULL
LEFT JOIN mt_contract_partner d
ON a.status >= 2
AND d.mt_contract_id = IFNULL(b.id, c.id)
AND d.deleted_at IS NULL
WHERE a.deleted_at IS NULL
;
by 재팬종쿤
[2019.01.30 16:47:22]
답변 감사합니다.
이런 방법도 있었군요!
많은 도움이 되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.