>source
SELECT DISTINCT
    *
FROM
    (
    SELECT DISTINCT
        1 AS rnk,
        `jl`.`jb_id`,
        `jl`.`jb_category`,
        `jl`.`jb_company_name`,
        `jl`.`jb_city`,
        `jl`.`jb_salary`,
        `te`.`emp_mobile`,
        `te`.`emp_name`,
        `jl`.`job_live_date`
    FROM
        `tl_job_listing` AS `jl`
    INNER JOIN `tl_employee` AS `te`
    ON
        `jl`.`jb_city` = `te`.`emp_city` AND `jl`.`jb_education` = `te`.`emp_edu` AND(
            (
                jl.ifgraduate = te.ifgraduate AND jl.ifbtech = jl.ifbtech
            ) OR(
                jl.ifgraduate = te.ifgraduate AND jl.ifpg = te.ifpg
            )
        ) AND `jl`.`jb_exp` = `te`.`emp_exp` AND jl.jb_category = te.prjrole
    WHERE
        (`jl`.`job_live_date` >= '2020-10-25')
    UNION
SELECT DISTINCT
    2 AS rnk,
    `jl`.`jb_id`,
    `jl`.`jb_category`,
    `jl`.`jb_company_name`,
    `jl`.`jb_city`,
    `jl`.`jb_salary`,
    `te`.`emp_mobile`,
    `te`.`emp_name`,
    `jl`.`job_live_date`
FROM
    `tl_job_listing` AS `jl`
INNER JOIN `tl_employee` AS `te`
ON
    `jl`.`jb_city` = `te`.`emp_city` AND `jl`.`jb_education` = `te`.`emp_edu` AND(
        (
            jl.ifgraduate = te.ifgraduate AND jl.ifbtech = jl.ifbtech
        ) OR(
            jl.ifgraduate = te.ifgraduate AND jl.ifpg = te.ifpg
        )
    ) AND `jl`.`jb_exp` = `te`.`emp_exp`
WHERE
    (`jl`.`job_live_date` >= '2020-10-25')
UNION
SELECT DISTINCT
    3 AS rnk,
    `jl`.`jb_id`,
    `jl`.`jb_category`,
    `jl`.`jb_company_name`,
    `jl`.`jb_city`,
    `jl`.`jb_salary`,
    `te`.`emp_mobile`,
    `te`.`emp_name`,
    `jl`.`job_live_date`
FROM
    `tl_job_listing` AS `jl`
INNER JOIN `tl_employee` AS `te`
ON
    `jl`.`jb_city` = `te`.`emp_city` AND `jl`.`jb_education` = `te`.`emp_edu` AND `jl`.`jb_exp` = `te`.`emp_exp`
WHERE
    (`jl`.`job_live_date` >= '2020-10-25')
UNION
SELECT DISTINCT
    4 AS rnk,
    `jl`.`jb_id`,
    `jl`.`jb_category`,
    `jl`.`jb_company_name`,
    `jl`.`jb_city`,
    `jl`.`jb_salary`,
    `te`.`emp_mobile`,
    `te`.`emp_name`,
    `jl`.`job_live_date`
FROM
    `tl_job_listing` AS `jl`
INNER JOIN `tl_employee` AS `te`
ON
    `jl`.`jb_city` = `te`.`emp_city` AND `jl`.`jb_education` = `te`.`emp_edu`
WHERE
    (`jl`.`job_live_date` >= '2020-10-25')
) tab
ORDER BY
    rnk

그러나 그것은 모든 rnk에서 일치를 반환합니다.

결과를 보려면 링크를 클릭하십시오

직원 테이블 구조 및 데이터

CREATE TABLE `tl_employee` (
  `emp_id` int(11) NOT NULL,
  `emp_language` varchar(90) NOT NULL,
  `emp_role` varchar(90) NOT NULL,
  `emp_name` varchar(100) NOT NULL,
  `emp_mobile` varchar(13) NOT NULL,
  `emp_age` int(111) NOT NULL,
  `emp_image` varchar(200) NOT NULL,
  `emp_idproof` varchar(233) NOT NULL,
  `jb_last_salary` varchar(333) NOT NULL,
  `skill` varchar(333) NOT NULL,
  `ifgraduate` varchar(333) NOT NULL,
  `ifbtech` varchar(333) NOT NULL,
  `ifpg` varchar(333) NOT NULL,
  `other_certificate` varchar(333) NOT NULL,
  `desk_emp_image` varchar(100) NOT NULL,
  `profile_image_path` varchar(1000) NOT NULL,
  `emp_city` varchar(30) NOT NULL,
  `emp_state` varchar(32) NOT NULL,
  `emp_exp` varchar(100) NOT NULL,
  `emp_edu` varchar(100) NOT NULL,
  `emp_email` varchar(100) NOT NULL,
  `emp_resume` varchar(300) NOT NULL,
  `emp_pass` varchar(32) NOT NULL,
  `emp_pas` varchar(20) NOT NULL,
  `emp_job` varchar(100) NOT NULL,
  `emp_status` int(2) NOT NULL,
  `register_date` varchar(60) NOT NULL,
  `inq_on` varchar(20) NOT NULL,
  `via` varchar(90) NOT NULL,
  `update_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `prjrole` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `tl_employee` (`emp_id`, `emp_language`, `emp_role`, `emp_name`, `emp_mobile`, `emp_age`, `emp_image`, `emp_idproof`, `jb_last_salary`, `skill`, `ifgraduate`, `ifbtech`, `ifpg`, `other_certificate`, `desk_emp_image`, `profile_image_path`, `emp_city`, `emp_state`, `emp_exp`, `emp_edu`, `emp_email`, `emp_resume`, `emp_pass`, `emp_pas`, `emp_job`, `emp_status`, `register_date`, `inq_on`, `via`, `update_on`, `prjrole`) VALUES
(10208, 'English', 'Job in your city', 'Rohit  Singh', '7011484311', 27, '', 'https://jobsgaar.com/haptik/asstes/employee-resume/125006.png', '6000', 'good driving', 'Not Available', 'Not Available', 'Not Available', 'Not Available', '', '', 'Kolkata', '', '3-4 Years', '10-12th class', 'rajeev@gmail.com', 'https://jobsgaar.com/haptik/asstes/employee-resume/692324.doc', 'f74a3490c6af63551e36a05a954dcd8c', '7011484311', '', 0, 'Mon 7th Dec 2020', '2020-12-07', 'Whatsapp Bot', '2020-12-17 06:14:18', 'Driver');

- -덤프 된 테이블에 대한 인덱스 - -테이블 인덱스 tl_employee
ALTER TABLE `tl_employee`
  ADD PRIMARY KEY (`emp_id`),
  ADD UNIQUE KEY `emp_mobile` (`emp_mobile`);

작업 목록 테이블 구조 및 데이터

-테이블의 테이블 구조 tl_job_listing
CREATE TABLE `tl_job_listing` (
  `jb_id` int(11) NOT NULL,
  `jb_category` varchar(90) NOT NULL,
  `jb_company_name` varchar(400) NOT NULL,
  `company_details` text NOT NULL,
  `company_address` text NOT NULL,
  `jb_city` varchar(90) NOT NULL,
  `jb_state` varchar(30) NOT NULL,
  `jb_salary` varchar(90) NOT NULL,
  `jb_education` varchar(200) NOT NULL,
  `jb_exp` varchar(50) NOT NULL,
  `ifgraduate` varchar(333) NOT NULL,
  `ifpg` varchar(333) NOT NULL,
  `ifbtech` varchar(333) NOT NULL,
  `other_certificate` varchar(333) NOT NULL,
  `skill` varchar(222) NOT NULL,
  `jb_gender1` varchar(20) NOT NULL,
  `jb_gender2` varchar(20) NOT NULL,
  `jb_gender3` varchar(20) NOT NULL,
  `jb_contact_person` varchar(90) NOT NULL,
  `jb_contact_no` varchar(13) NOT NULL,
  `jb_email` varchar(60) NOT NULL,
  `jb_company_proof` varchar(200) NOT NULL,
  `jb_posted_by` varchar(100) NOT NULL,
  `jb_vacancy` varchar(50) NOT NULL,
  `jb_time` varchar(90) NOT NULL,
  `call_via` varchar(20) NOT NULL,
  `job_status` varchar(90) NOT NULL,
  `reg_date` varchar(60) NOT NULL,
  `update_job_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `job_from` varchar(100) NOT NULL,
  `jld` varchar(60) NOT NULL,
  `jed` varchar(60) NOT NULL,
  `job_live_date` varchar(120) NOT NULL,
  `job_expired_date` varchar(120) NOT NULL,
  `video` text NOT NULL,
  `video_status` int(11) NOT NULL,
  `joblang` int(11) NOT NULL,
  `via` varchar(60) NOT NULL,
  `job_valid` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

- -테이블 데이터 덤핑 tl_job_listing
INSERT INTO `tl_job_listing` (`jb_id`, `jb_category`, `jb_company_name`, `company_details`, `company_address`, `jb_city`, `jb_state`, `jb_salary`, `jb_education`, `jb_exp`, `ifgraduate`, `ifpg`, `ifbtech`, `other_certificate`, `skill`, `jb_gender1`, `jb_gender2`, `jb_gender3`, `jb_contact_person`, `jb_contact_no`, `jb_email`, `jb_company_proof`, `jb_posted_by`, `jb_vacancy`, `jb_time`, `call_via`, `job_status`, `reg_date`, `update_job_date`, `job_from`, `jld`, `jed`, `job_live_date`, `job_expired_date`, `video`, `video_status`, `joblang`, `via`, `job_valid`) VALUES
(429, 'Driver', 'Social beat', '', '', 'Kolkata', '', '8000', '10-12th class', '3-4 Years', 'Not Available', 'Not Available', 'Not Available', 'Not Available', 'good driving', '', '', '', '', '', '', '', '9940193825', '', '', '', 'Live', 'Thu 10th Dec 2020', '2020-12-17 06:17:10', '', '1607538600', '1607538600', '2020-12-10', '2020-12-10', '', 0, 0, 'Whatsapp Bot', '');

- -덤프 된 테이블에 대한 인덱스 - -테이블 인덱스 tl_job_listing
ALTER TABLE `tl_job_listing`
  ADD PRIMARY KEY (`jb_id`);


  • 답변 # 1

    최소 요구 사항에 가입하겠습니다. jl . jb_city = te . emp_cityjl . jb_education = te . emp_edu -그런 다음 순위를 매기기 위해 사례 설명

    SELECT 
         case when ((jl.ifgraduate = te.ifgraduate  AND jl.ifbtech = jl.ifbtech ) OR
                        (jl.ifgraduate = te.ifgraduate   AND jl.ifpg = te.ifpg)) AND 
                          `jl`.`jb_exp` = `te`.`emp_exp` AND 
                          jl.jb_category = te.prjrole    then
                        1
                 when ((jl.ifgraduate = te.ifgraduate AND jl.ifbtech = jl.ifbtech) OR
                       (jl.ifgraduate = te.ifgraduate AND jl.ifpg = te.ifpg)) AND 
                        `jl`.`jb_exp` = `te`.`emp_exp` then 
                        2
                 when `jl`.`jb_exp` = `te`.`emp_exp` then 
                       3
                 else 4
                 end as rnk,
        `jl`.`jb_id`,
        `jl`.`jb_category`,
        `jl`.`jb_company_name`,
        `jl`.`jb_city`,
        `jl`.`jb_salary`,
        `te`.`emp_mobile`,
        `te`.`emp_name`,
        `jl`.`job_live_date`
    FROM
        `tl_job_listing` AS `jl`
    INNER JOIN `tl_employee` AS `te`
    ON  `jl`.`jb_city` = `te`.`emp_city` AND `jl`.`jb_education` = `te`.`emp_edu`
    WHERE
        (`jl`.`job_live_date` >= '2020-10-25');
    
    +-----+-------+-------------+-----------------+---------+-----------+------------+--------------+---------------+
    | rnk | jb_id | jb_category | jb_company_name | jb_city | jb_salary | emp_mobile | emp_name     | job_live_date |
    +-----+-------+-------------+-----------------+---------+-----------+------------+--------------+---------------+
    |   1 |   429 | Driver      | Social beat     | Kolkata | 8000      | 7011484311 | Rohit  Singh | 2020-12-10    |
    +-----+-------+-------------+-----------------+---------+-----------+------------+--------------+---------------+
    1 row in set (0.002 sec)
    
    

관련 자료

  • 이전 javascript - 1,2,3과 같은 정규식 패턴을 만드는 방법
  • 다음 Python pip라는 모듈 없음