查询获取合并的两个表数据 [英] Query for fetch two tables data combined

问题描述

我有两张桌子.表 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
);

这篇关于查询获取合并的两个表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！