时间戳

首页 > 解决方案 > MYSQL 获取所选记录的上一条记录

mysql - MYSQL 获取所选记录的上一条记录

问题描述

我有两个表 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

标签： mysql

解决方案

我认为这个查询会给你你想要的结果。您将“活动”学期代码放入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

dbfiddle 上的演示

推荐阅读