mysql如何匹配_如何在mysql中匹配

我有两个表,一个用于候选人和他们的技能,另一个用于工作和工作所需的技能,如下所示：

CSID =候选技能ID(PK)

CID =候选人ID(FK)

S_CODE =技能代码(FK)

这是candidate_skill表

+------------+---------+---------+

| CSID | CID | S_CODE |

+------------+---------+---------+

| 1 | 1 | 5 |

| 2 | 1 | 9 |

| 3 | 2 | 5 |

| 4 | 2 | 10 |

+------------+---------+---------+

SJID =技能工作ID(PK)

JID =工作ID(FK)

S_CODE =技能代码(FK)

这是skill_job表：

+------------+---------+---------+

| SJID | JID | S_CODE |

+------------+---------+---------+

| 12 | 50 | 5 |

| 13 | 50 | 9 |

| 14 | 51 | 1 |

| 15 | 52 | 10 |

+------------+---------+---------+

因此在这个例子中,作业50的唯一候选者将是1,因为技能代码(S_CODE)对于两者都是5和9,但我也希望候选人2与52匹配,因为它具有所需的工作技能和额外的一个.

我已经尝试了几种方法来匹配工作和候选人,但我失败了,这种方式的一个例子是下面的代码：

SELECT * , COUNT(skill_job.S_CODE) AS cnt FROM candidate_skill,

skill_job WHERE candidate_skill.S_CODE = skill_job.S_CODE HAVING (cnt >=2)

问题是,这只限制找到的候选人,如果我删除了COUNT子句,它列出了只与一种技能匹配的所有候选人,因此候选人2也将与工作50匹配.

这是phpmyadmin中表的mysql代码：

-- phpMyAdmin SQL Dump

-- version 3.4.5

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Jul 16, 2012 at 09:27 PM

-- Server version: 5.5.16

-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `employment`

--

-- --------------------------------------------------------

--

-- Table structure for table `candidate`

--

CREATE TABLE IF NOT EXISTS `candidate` (

`CID` int(4) NOT NULL AUTO_INCREMENT,

`title` varchar(5) NOT NULL,

`fname` varchar(30) NOT NULL,

`lname` varchar(30) NOT NULL,

`dob` date NOT NULL,

`email` varchar(50) NOT NULL,

`address` varchar(255) NOT NULL,

`city` varchar(50) NOT NULL,

`postcode` varchar(10) NOT NULL,

`phone_num` varchar(11) NOT NULL,

`username` varchar(40) NOT NULL,

`password` varchar(40) NOT NULL,

`regdate` datetime NOT NULL,

`acc_type` enum('c','s') NOT NULL DEFAULT 'c',

`emailactivate` enum('0','1') NOT NULL DEFAULT '0',

`cv_name` varchar(60) NOT NULL,

`cv` blob NOT NULL,

PRIMARY KEY (`CID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='// this is the table for the candidates' AUTO_INCREMENT=175 ;

--

-- Dumping data for table `candidate`

--

INSERT INTO `candidate` (`CID`, `title`, `fname`, `lname`, `dob`, `email`, `address`, `city`, `postcode`, `phone_num`, `username`, `password`, `regdate`, `acc_type`, `emailactivate`, `cv_name`, `cv`) VALUES

(128, 'Mr', 'clement', 'Chilingulo', '0000-00-00', 'chlngl@yahoo.com', '28 oakfield Road', 'london', 'E6 1LW', '07771611873', 'casante', 'b59c67bf196a4758191e42f76670ceba', '0000-00-00 00:00:00', 'c', '0', '', '');

INSERT INTO `candidate` (`CID`, `title`, `fname`, `lname`, `dob`, `email`, `address`, `city`, `postcode`, `phone_num`, `username`, `password`, `regdate`, `acc_type`, `emailactivate`, `cv_name`, `cv`) VALUES

(134, 'Mr', 'rverv', 'revrb', '0000-00-00', 'tdbsdrt', 'trsbrtd', 'trbtrtrb', 'tbrfbgrts', 'trfbtrgbrfg', 'clement', 'b59c67bf196a4758191e42f76670ceba', '0000-00-00 00:00:00', 's', '0', '', '');

INSERT INTO `candidate` (`CID`, `title`, `fname`, `lname`, `dob`, `email`, `address`, `city`, `postcode`, `phone_num`, `username`, `password`, `regdate`, `acc_type`, `emailactivate`, `cv_name`, `cv`) VALUES

(165, 'Mr', 'oinInINOioni', 'ioin', '0000-00-00', 'inioimn', 'in', 'oin', 'oni', 'io', 'k', '7b8b965ad4bca0e41ab51de7b31363a1', '0000-00-00 00:00:00', 'c', '0', '', ''),

(166, 'Mr', 'pjINoNlkinoinoi', 'ino', '0000-00-00', 'oimpnponi', 'inoi', 'no', 'nj', 'nio', 'nio', 'eb5bc837d01b911029ae378e8a1c9f5d', '0000-00-00 00:00:00', 'c', '0', '', ''),

(167, 'Mr', 'vrae', 'ergvaer', '0000-00-00', 'aerbg', 'aergvera', 'aergvrea', 'aergvear', 'aergarev', 'grebvarvf', '609c1b136ec8d0d2dfdf9a2105fb605f', '0000-00-00 00:00:00', 'c', '0', '', ''),

(168, 'Mr', 'vrae', 'ergvaer', '0000-00-00', 'aerbg', 'aergvera', 'aergvrea', 'aergvear', 'aergarev', 'grebvarvf', '609c1b136ec8d0d2dfdf9a2105fb605f', '0000-00-00 00:00:00', 'c', '0', '', ''),

(169, 'Mr', 'ubp', 'bu', '0000-00-00', 'ubip', ';ub', 'ubi', 'ubo', 'buo', 'ubiipbu', '9f44ce1389a3e7372834ed730b559a5e', '0000-00-00 00:00:00', 'c', '0', '', ''),

(170, 'Mr', 'rvev', 'ferhbgetb', '0000-00-00', 'tsbtrb', 'trbstrb', 'trbsfb ', 'stb stb', 'vvfs', 'csdcdsvarewdcv', 'd41d8cd98f00b204e9800998ecf8427e', '0000-00-00 00:00:00', 'c', '0', '', ''),

(171, 'Mr', 'rvev', 'ferhbgetb', '0000-00-00', 'tsbtrb', 'trbstrb', 'trbsfb ', 'stb stb', 'vvfs', 'csdcdsvarewdcv', 'd41d8cd98f00b204e9800998ecf8427e', '0000-00-00 00:00:00', 'c', '0', '', ''),

(172, '', '', '', '0000-00-00', '', '', '', '', '', '', 'd41d8cd98f00b204e9800998ecf8427e', '0000-00-00 00:00:00', 'c', '0', '', ''),

(173, '', 'dds', 'vrv av', '0000-00-00', 'rvear', 'vreverav', 'rvedfsdv', 'frvdvf', 'fveavd', 'vdrareavdv', '84c71ac76340092398a1ed4bc7d6fe19', '0000-00-00 00:00:00', 'c', '0', '', ''),

(174, '', 'dds', 'vrv av', '0000-00-00', 'rvedwear', 'vreverav', 'rvedfsdv', 'frvdvf', 'fveavd', 'vdraredfcavdv', 'd41d8cd98f00b204e9800998ecf8427e', '0000-00-00 00:00:00', 'c', '0', '', '');

-- --------------------------------------------------------

--

-- Table structure for table `candidate_skill`

--

CREATE TABLE IF NOT EXISTS `candidate_skill` (

`CSID` int(4) NOT NULL AUTO_INCREMENT,

`CID` int(4) NOT NULL,

`S_CODE` int(4) NOT NULL,

PRIMARY KEY (`CSID`),

KEY `CID` (`CID`),

KEY `S_CODE` (`S_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='//match candidate and skill' AUTO_INCREMENT=131 ;

--

-- Dumping data for table `candidate_skill`

--

INSERT INTO `candidate_skill` (`CSID`, `CID`, `S_CODE`) VALUES

(114, 134, 2),

(116, 134, 9),

(121, 134, 5),

(126, 128, 1);

-- --------------------------------------------------------

--

-- Table structure for table `job`

--

CREATE TABLE IF NOT EXISTS `job` (

`JID` int(4) NOT NULL AUTO_INCREMENT,

`job_title` varchar(40) NOT NULL,

`job_desc` varchar(255) NOT NULL,

`start_date` date NOT NULL,

`end_date` date NOT NULL,

PRIMARY KEY (`JID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='// this is the table for the job vacancies' AUTO_INCREMENT=10 ;

--

-- Dumping data for table `job`

--

INSERT INTO `job` (`JID`, `job_title`, `job_desc`, `start_date`, `end_date`) VALUES

(1, 'engineer', ' fix engineering ish that is messed ', '0000-00-00', '0000-00-00'),

(5, 'pilot', '', '0000-00-00', '0000-00-00'),

(8, 'Charity Helper', ' ', '0000-00-00', '0000-00-00'),

(9, 'accountant', ' ', '0000-00-00', '0000-00-00');

-- --------------------------------------------------------

--

-- Table structure for table `skill`

--

CREATE TABLE IF NOT EXISTS `skill` (

`S_CODE` int(4) NOT NULL AUTO_INCREMENT COMMENT '// this is the skill primary key',

`skill_name` varchar(40) NOT NULL,

`skill_desc` varchar(255) NOT NULL,

PRIMARY KEY (`S_CODE`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--

-- Dumping data for table `skill`

--

INSERT INTO `skill` (`S_CODE`, `skill_name`, `skill_desc`) VALUES

(1, 'speaking', 'English is your first language'),

(2, ' writing', 'You can write English very well'),

(3, ' public', ''),

(5, 'initiative', ''),

(6, 'interviewing', ''),

(7, 'negotiating', ''),

(8, 'leading', ''),

(9, ' energy', ''),

(10, ' organisation', '');

-- --------------------------------------------------------

--

-- Table structure for table `skill_job`

--

CREATE TABLE IF NOT EXISTS `skill_job` (

`SJID` int(4) NOT NULL AUTO_INCREMENT,

`JID` int(4) NOT NULL,

`S_CODE` int(4) NOT NULL,

PRIMARY KEY (`SJID`),

KEY `S_CODE` (`S_CODE`),

KEY `JID` (`JID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=106 ;

--

-- Dumping data for table `skill_job`

--

INSERT INTO `skill_job` (`SJID`, `JID`, `S_CODE`) VALUES

(91, 5, 2),

(94, 5, 5),

(95, 5, 9),

(98, 1, 1),

(102, 1, 8),

(105, 8, 8);

--

-- Constraints for dumped tables

--

--

-- Constraints for table `candidate_skill`

--

ALTER TABLE `candidate_skill`

ADD CONSTRAINT `candidate_skill_ibfk_3` FOREIGN KEY (`CID`) REFERENCES `candidate` (`CID`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `candidate_skill_ibfk_4` FOREIGN KEY (`S_CODE`) REFERENCES `skill` (`S_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;

--

-- Constraints for table `skill_job`

--

ALTER TABLE `skill_job`

ADD CONSTRAINT `skill_job_ibfk_4` FOREIGN KEY (`JID`) REFERENCES `job` (`JID`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `skill_job_ibfk_5` FOREIGN KEY (`S_CODE`) REFERENCES `skill` (`S_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;