查询获取合并的两个表数据

本文介绍了查询获取合并的两个表数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两张桌子.表 1 名称 'student' 具有如下列

I have 2 tables. Table 1 name 'student' have columns like follows

rowindex roll_no name 1 111 Peter 2 112 John

表 2 名称 'exam_dates' 有如下列

Table 2 name 'exam_dates' have columns like follows

rowindex roll_no subject date 1 111 Maths 2018-06-20 2 111 English 2018-06-21 3 112 Maths 2018-06-19 4 112 History 2018-06-22

查询条件如下:-

Condition 1. Each student's Last exam date in 1 table by using those two tables.

&

Condition 2. If Exam date is less than today's date, then it should not come into the list.

我想得到结果

1. Roll_no 111 have Maths at 2018-06-20 2. Roll_no 112 have History at 2018-06-22

为了得到这个结果，我必须写什么查询?我尝试查询如下:-

For get this result what query I have to write? I tried query as follows:-

SELECT a.roll_no, a.name,b.subject, b.date FROM test_db.student a, test_db.exam_dates b Where a.roll_no = b.roll_no and (SELECT MAX(date) FROM exam_dates) group by a.roll_no order by a.roll_no, a.name,b.subject;

但是没有成功.需要帮助.

But No success. Need Help.

推荐答案

条件 2. 如果考试日期小于今天的日期，则不应进入列表.

Condition 2. If Exam date is less than today's date, then it should not come into the list.

这是一个WHERE条件.

条件 1. 使用这两个表格将每个学生的最后一次考试日期放在一张表格中.

Condition 1. Each student's Last exam date in 1 table by using those two tables.

这是每位学生的 MAX(date).

您也想显示主题，因此您将首先获得每个学生的最大日期，然后再次查询 exam_dates 表:

You want to show the subject, too, so you'll get the max dates per student first and then query the exam_dates table again:

select s.roll_no, s.name, ed.subject, ed.date from student s join exam_dates ed on ed.roll_no = s.roll_no where (ed.roll_no, ed.date) in ( select roll_no, max(date) from exam_dates where date >= current_date group by roll_no );