时间戳

首页 > 解决方案 > MYSQL 3 连续出现基于年份的记录

mysql - MYSQL 3 连续出现基于年份的记录

问题描述

我有两个表 table1：PERIODS 和 table2：PROBATIONARY，我试图根据 PERIODS 表的 YEAR 列来获取学生是否累积了 3 个连续的 ONPROBATIONARY 状态。所以基本上我只是期待 1 或 0 的输出，因此如果结果集变为 FALSE，我想知道学生是否从上学期开始试用。我基本上是 mysql 的新手，我发现很难在单个查询中创建这个，任何帮助将不胜感激。

-- 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);

标签： mysql

解决方案

我可以想到几种方法，但让我们先尝试一件事，看看结果是否是您的目标：

SELECT P.id,P.Code,P.Name,P.Year,A.Student "20182", B.Student "20181", C.Student "20173", D.Student "20172", E.Student "20171" FROM period P LEFT JOIN (SELECT * FROM probationary a JOIN period b ON a.period=b.id WHERE a.onprob=1 AND b.code="20182") A ON P.code=A.code LEFT JOIN (SELECT * FROM probationary a JOIN period b ON a.period=b.id WHERE a.onprob=1 AND b.code="20181") B ON P.code=B.code LEFT JOIN (SELECT * FROM probationary a JOIN period b ON a.period=b.id WHERE a.onprob=1 AND b.code="20173") C ON P.code=C.code LEFT JOIN (SELECT * FROM probationary a JOIN period b ON a.period=b.id WHERE a.onprob=1 AND b.code="20172") D ON P.code=D.code LEFT JOIN (SELECT * FROM probationary a JOIN period b ON a.period=b.id WHERE a.onprob=1 AND b.code="20171") E ON P.code=E.code;

在此您将看到附加的结果： 示例结果

请注意，在probationary表格中我添加了另一个学生754，只是为了查看如果有多个学生，结果将如何显示。

推荐阅读