mysql - 使用多重选择来连接不同表中的数据

Question

我有以下架构

CREATE TABLE years (
year_id INT NOT NULL AUTO_INCREMENT,
year_name_in_nums int NOT NULL,
year_name_in_text varchar(255) NOT NULL,
PRIMARY KEY (year_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE classes (
class_id INT NOT NULL AUTO_INCREMENT,
class_name varchar(255) NOT NULL,
PRIMARY KEY (class_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE students (
student_id INT NOT NULL AUTO_INCREMENT,
student_names varchar(255) NOT NULL,
student_telephone_number int NOT NULL,
PRIMARY KEY (student_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE terms (
term_id INT NOT NULL AUTO_INCREMENT,
term_name_in_nums int NOT NULL,
term_name_in_text varchar(255) NOT NULL,
term_year int NOT NULL,
PRIMARY KEY (term_id),
FOREIGN KEY (term_year) REFERENCES years(year_id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE class_students (
cs_id INT NOT NULL AUTO_INCREMENT,
cs_class_student int NOT NULL,
cs_class_id int NOT NULL,
cs_term_id int NOT NULL,
PRIMARY KEY (cs_id),
FOREIGN KEY (cs_class_student) REFERENCES students(student_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (cs_class_id) REFERENCES classes(class_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (cs_term_id) REFERENCES terms(term_id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

我想显示术语表中的 term_name_in_nums 和年份表中的 year_name_in_nums

我通过这种方式检索数据

(select term_name_in_nums,term_year from terms INNER JOIN class_students ON term_id=cs_term_id);

从上面可以看出，term_year 是一个外键 (term_year)，它引用了年份(year_id)

我正在尝试这个

(select term_name_in_nums from terms INNER JOIN class_students ON term_id=cs_term_id)
  LEFT JOIN
 (select term_year from terms INNER JOIN class_students ON term_id=cs_term_id);

这给了我一个 sql 语法错误。我该如何解决这个问题？

编辑:数据

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.6.11 - MySQL Community Server (GPL)
-- Server OS:                    Win32
-- HeidiSQL Version:             8.0.0.4396
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Dumping data for table algo.classes: ~10 rows (approximately)
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT IGNORE INTO `classes` (`class_id`, `class_name`) VALUES
    (1, '1'),
    (2, '2'),
    (3, '3'),
    (4, '4'),
    (5, '5'),
    (6, '6'),
    (7, '7'),
    (8, '8'),
    (9, '9'),
    (10, '10');
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;

-- Dumping data for table algo.class_students: ~16 rows (approximately)
/*!40000 ALTER TABLE `class_students` DISABLE KEYS */;
INSERT IGNORE INTO `class_students` (`cs_id`, `cs_class_student`, `cs_class_id`, `cs_term_id`) VALUES
    (1, 1, 1, 1),
    (2, 1, 1, 1),
    (3, 2, 1, 1),
    (4, 3, 1, 1),
    (5, 4, 1, 1),
    (6, 5, 1, 1),
    (7, 6, 1, 1),
    (8, 7, 1, 1),
    (9, 8, 1, 1),
    (10, 9, 1, 1),
    (11, 10, 1, 1),
    (12, 11, 1, 1),
    (13, 12, 1, 1),
    (14, 13, 1, 1),
    (15, 14, 1, 1),
    (16, 15, 1, 1);
/*!40000 ALTER TABLE `class_students` ENABLE KEYS */;

-- Dumping data for table algo.examinations: ~3 rows (approximately)
/*!40000 ALTER TABLE `examinations` DISABLE KEYS */;
INSERT IGNORE INTO `examinations` (`examination_id`, `examination_name`, `examination_term_id`) VALUES
    (1, 'start of term', 1),
    (2, 'mid term', 1),
    (3, 'end of term', 1);
/*!40000 ALTER TABLE `examinations` ENABLE KEYS */;

-- Dumping data for table algo.examination_data: ~0 rows (approximately)
/*!40000 ALTER TABLE `examination_data` DISABLE KEYS */;
/*!40000 ALTER TABLE `examination_data` ENABLE KEYS */;

-- Dumping data for table algo.students: ~20 rows (approximately)
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT IGNORE INTO `students` (`student_id`, `student_names`, `student_telephone_number`) VALUES
    (1, 'aa', 123456),
    (2, 'bb', 5576899),
    (3, '23050359165419520', 788399),
    (4, '23050359165419521', 7892020),
    (5, '23050359165419522', 33772),
    (6, '23050359165419523', 86729),
    (7, '23050359165419524', 526890),
    (8, '23050359165419525', 444332),
    (9, '23050359165419526', 937678),
    (10, '23050359165419527', 44),
    (11, '23050359165419528', 774),
    (12, '23050359165419529', 2147483647),
    (13, '23050359165419531', 2147483647),
    (14, '23050359165419533', 2147483647),
    (15, '23050359165419535', 353657674),
    (16, '23050359165419536', 436),
    (17, '23050359165419537', 5467),
    (18, '23050359165419538', 565788),
    (19, '23050359165419539', 548759898),
    (20, '23050359165419540', 2443);
/*!40000 ALTER TABLE `students` ENABLE KEYS */;

-- Dumping data for table algo.terms: ~10 rows (approximately)
/*!40000 ALTER TABLE `terms` DISABLE KEYS */;
INSERT IGNORE INTO `terms` (`term_id`, `term_name_in_nums`, `term_name_in_text`, `term_year`) VALUES
    (1, 1, 'term one', 1),
    (2, 2, 'term two', 2),
    (3, 3, 'term three', 3),
    (4, 4, 'term four', 4),
    (5, 5, 'term five', 5),
    (6, 6, 'term six', 6),
    (7, 7, 'term seven', 7),
    (8, 8, 'term eight', 8),
    (9, 9, 'term nine', 9),
    (10, 10, 'term ten', 10);
/*!40000 ALTER TABLE `terms` ENABLE KEYS */;

-- Dumping data for table algo.years: ~15 rows (approximately)
/*!40000 ALTER TABLE `years` DISABLE KEYS */;
INSERT IGNORE INTO `years` (`year_id`, `year_name_in_nums`, `year_name_in_text`) VALUES
    (1, 2000, '2000/2001'),
    (2, 2001, '2001/2002'),
    (3, 2002, '2002/2003'),
    (4, 2003, '2003/2004'),
    (5, 2004, '2004/2005'),
    (6, 2005, '2005/2006'),
    (7, 2006, '2006/2007'),
    (8, 2007, '2007/2008'),
    (9, 2008, '2008/2009'),
    (10, 2009, '2009/2010'),
    (11, 2010, '2010/2011'),
    (12, 2011, '2011/2012'),
    (13, 2012, '2012/2013'),
    (14, 2013, '2013/2014'),
    (15, 2014, '2014/2015');
/*!40000 ALTER TABLE `years` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Answer 1

你不能像这样使用LEFT JOIN...你可以使用UNION ALL，但这只有在两个查询具有相同数量的列和相同的数据类型时才有效，但这样你将无法区分这两列因为他们本来就是一体的。尝试像这样重复它们:

SELECT
  term_name_in_nums,
  NULL as term_year
FROM
  terms
  INNER JOIN
    class_students ON (term_id=cs_term_id)

UNION ALL

SELECT
  NULL as term_name_in_nums,
  term_year
FROM
  terms
  INNER JOIN
    class_students ON (term_id=cs_term_id)

或者尝试执行此查询:

SELECT
  terms.term_name_in_nums,
  terms_b.term_year
FROM
  class_students
  INNER JOIN
    terms ON (terms.term_id=class_students.cs_term_id)
  LEFT JOIN
    terms as terms_b ON (terms_b.term_id=class_students.cs_term_id)

我没有可供测试的表格数据，并且您想要获得的内容并不那么清楚。所以无论如何，尝试一下，您可能可以对其进行编辑以适合您的情况。

利用以上数据来帮助了解 OP 的目的:

SELECT
  class_students.cs_id,
  class_students.cs_class_student,
  class_students.cs_class_id,
  class_students.cs_term_id,
  classes.class_name,
  students.student_names,
  students.student_telephone_number,
  terms.term_name_in_nums,
  terms.term_name_in_text,
  terms.term_year,
  years.year_name_in_nums,
  year_name_in_text
FROM
  class_students
  LEFT JOIN classes ON (class_students.cs_class_id=classes.class_id)
  LEFT JOIN students ON (class_students.cs_class_student=students.student_id)
  LEFT JOIN terms ON (class_students.cs_term_id=terms.term_id)
  LEFT JOIN years ON (terms.term_year=years.year_id)

这将为 class_students 表中的所有记录提供所有类(class)、学生、学期和年份信息

输出为 php 数组:

$class_students = array(
  array('cs_id' => '1','cs_class_student' => '1','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => 'aa','student_telephone_number' => '123456','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '2','cs_class_student' => '1','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => 'aa','student_telephone_number' => '123456','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '3','cs_class_student' => '2','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => 'bb','student_telephone_number' => '5576899','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '4','cs_class_student' => '3','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419520','student_telephone_number' => '788399','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '5','cs_class_student' => '4','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419521','student_telephone_number' => '7892020','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '6','cs_class_student' => '5','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419522','student_telephone_number' => '33772','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '7','cs_class_student' => '6','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419523','student_telephone_number' => '86729','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '8','cs_class_student' => '7','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419524','student_telephone_number' => '526890','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '9','cs_class_student' => '8','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419525','student_telephone_number' => '444332','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '10','cs_class_student' => '9','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419526','student_telephone_number' => '937678','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '11','cs_class_student' => '10','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419527','student_telephone_number' => '44','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '12','cs_class_student' => '11','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419528','student_telephone_number' => '774','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '13','cs_class_student' => '12','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419529','student_telephone_number' => '2147483647','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '14','cs_class_student' => '13','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419531','student_telephone_number' => '2147483647','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '15','cs_class_student' => '14','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419533','student_telephone_number' => '2147483647','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001'),
  array('cs_id' => '16','cs_class_student' => '15','cs_class_id' => '1','cs_term_id' => '1','class_name' => '1','student_names' => '23050359165419535','student_telephone_number' => '353657674','term_name_in_nums' => '1','term_name_in_text' => 'term one','term_year' => '1','year_name_in_nums' => '2000','year_name_in_text' => '2000/2001')
);