获取当前记录的前后各3条记录mysql_MYSQL获取所选记录的上一条记录

我有两张表表1:学时和表2:试用期,我想知道学生是否存在于试用期表中,我想在当前学期之前检查学生是否在前一学期处于试用期。所以如果活跃学期是

我想看看这个学生是否在试用期

学期,每次我更改活动期,它都应该查询并检查以前的记录。期间按年度和期限排列。

像这样的东西,我真的没有MYSQL的深厚背景,所以任何帮助都会。

SELECT

period.code,

period.name,

period.`year`,

period.term,

probationary.student,

probationary.onprob

FROM

probationary

Inner Join period ON probationary.period = period.id

WHERE

period.id = (PREVIOUS PERIOD OF CURRENT PERIOD)

-- PERIOD TABLE

CREATE TABLE IF NOT EXISTS `period` (

`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`code` varchar(12) NOT NULL DEFAULT '',

`name` varchar(40) NOT NULL DEFAULT '',

`year` smallint(5) unsigned NOT NULL DEFAULT '0',

`term` char(1) NOT NULL DEFAULT '',

`nstart` date NOT NULL DEFAULT '0000-00-00',

`nend` date NOT NULL DEFAULT '0000-00-00',

`ext` date NOT NULL DEFAULT '0000-00-00',

`enrstart` date NOT NULL DEFAULT '0000-00-00',

`enrend` date NOT NULL DEFAULT '0000-00-00',

`enrext` date NOT NULL DEFAULT '0000-00-00',

`addstart` date NOT NULL DEFAULT '0000-00-00',

`addend` date NOT NULL DEFAULT '0000-00-00',

`addext` date NOT NULL DEFAULT '0000-00-00',

`orvalidate` date NOT NULL DEFAULT '0000-00-00',

`idmask` varchar(12) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `code` (`code`),

KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- PERIOD VALUES

-- Dumping data for table `period`

--

INSERT INTO `period` (`id`, `code`, `name`, `year`, `term`, `nstart`, `nend`, `ext`, `enrstart`, `enrend`, `enrext`, `addstart`, `addend`, `addext`, `orvalidate`, `idmask`) VALUES

(1, '20181', 'First Semester, 2018-2019', 2018, '1', '2018-08-13', '2018-12-13', '2019-05-01', '2018-07-13', '2018-09-13', '2019-03-29', '2018-08-13', '2018-09-13', '2019-03-29', '2018-12-13', '181'),

(2, '20182', 'Second Semester, 2018-2019', 2018, '2', '2019-01-14', '2019-05-14', '2019-05-14', '2018-12-14', '2019-02-14', '2019-05-27', '2019-01-14', '2019-02-14', '2019-05-27', '2019-05-14', '182'),

(3, '20171', 'First Semester, 2017-2018', 2017, '1', '2017-08-14', '2017-12-14', '2017-12-14', '2017-07-14', '2017-09-14', '2017-09-14', '2017-08-14', '2017-09-14', '2017-09-14', '2017-12-14', '171'),

(4, '20172', 'Second Semester, 2017-2018', 2017, '2', '2017-01-09', '2017-05-09', '2017-05-09', '2016-12-09', '2017-02-09', '2017-02-09', '2017-01-09', '2017-02-09', '2017-02-09', '2017-05-09', '172'),

(5, '20173', 'Short Term 2017', 2017, '3', '2017-06-05', '2017-08-05', '2017-08-05', '2017-05-05', '2017-07-05', '2017-07-05', '2017-06-05', '2017-07-05', '2017-07-05', '2017-08-05', '173');

-- PROBATIONARY TABLE

-- Table structure for table `probationary`

CREATE TABLE IF NOT EXISTS `probationary` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`student` int(11) NOT NULL,

`period` int(11) NOT NULL,

`totalunits` varchar(5) NOT NULL,

`passedunits` varchar(5) NOT NULL,

`onprob` int(1) NOT NULL,

PRIMARY KEY (`id`)

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

-- PROBATIONARY VALUES

INSERT INTO `probationary` (`id`, `student`, `period`, `totalunits`, `passedunits`, `onprob`) VALUES

(1, 753, 1, '29', '12', 1),

(2, 753, 3, '29', '12', 1),

(3, 753, 4, '29', '12', 1),

(5, 754, 5, '29', '10', 1),

(6, 754, 4, '29', '10', 1);

我有上面的样本记录,如果

20182年