MYSQL 获取选中记录的上一条记录

Question

我有两个表 table1:PERIODS 和 table2:PROBATIONARY，我想知道该学生是否存在于 PROBATIONARY 表中，我想检查该学生是否在事件学期之前的上一个学期处于试用期。因此，如果事件学期是 (20182) 我想检查学生是否在 (20181) 学期试用，每次我更改事件期间它应该查询并检查之前的纪录。 PERIODS 按 YEAR 和 TERM 排列。

像这样，我现在真的没有很深的 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，则 STUDENT 753 将为 TRUE，而 STUDENT 754 将为 FALSE

Answer 1

我认为这个查询会给你想要的结果。您将“事件”学期代码放入子查询的 WHERE 子句中，然后它将返回以前的学期代码，然后用于选择正在试用的学生，例如

SELECT p.code,
       p.name,
       p.`year`,
       p.term,
       pr.student,
       pr.onprob
FROM probationary pr
INNER JOIN period p ON pr.period = p.id
WHERE p.code = (SELECT MAX(code)
              FROM period p2
              WHERE p2.code < 20173)

输出(对于您的示例数据):

code    name                        year    term    student onprob
20172   Second Semester, 2017-2018  2017    2       753     1
20172   Second Semester, 2017-2018  2017    2       754     1

Demo on dbfiddle