我有一个表,其中包含针对学生的所有最新测试日期和他们参加的测试类型。
类似以下内容:
加入表
+--------------+--------+--------+
| Name | Test | Date |
+--------------+--------+--------+
| Test Taker A | Math | May 9 |
| Test Taker B | Math | May 9 |
| Test Taker A | Math 2 | May 10 |
| Test Taker C | Math | May 8 |
| Test Taker A | Spanish| May 12 |
+--------------+--------+--------+
+----------------+---------+-------+
|Registration-ID | Name ID |Test Id|
+----------------+---------+-------+
| 1 | 453 |134534 |
| 2 | 34534 |134534 |
| 3 | 2343 |134534 |
| 4 | 453 |12343 |
| 5 | 453 | 4531 |
+--------------+-----------+-------+
+--------------+--------+----+
|Roster Name | Name ID|demo|
+--------------+--------+----+
| Test Taker A | 453 | M |
| Test Taker B | 34534 | F |
| Test Taker C | 2343 | M |
+--------------+--------+----+
+---------+---------+--------+
|Test Name| test ID |Session |
+---------+---------+--------+
| Spanish | 4531 | 3 |
| Math | 134534 | 2 |
| Math2 | 12343 | 4 |
| Math | 134534 | 1 |
+---------+---------+--------+
+------------+-------+
| Session | Date |
+--------------------+
| 2 | May 9|
| 2 | May 9 |
| 4 | May 10|
| 1 | May 8 |
| 3 |May 12 |
+------------+-------+
现在我有
select R.Name, T.Test, D.Date
from Roster ro
join registration re on re.name = ro.name
join test t on t.test_id = re.test_id
join date d on d.session = t.session
where t.Test = "Math".
这将为我提供测试A,B,c。但是我希望结果显示测试仪B和C,因为他们进行的最后一次测试是数学。我只有在那个人参加的最后一个测试并且没有进一步的条目时才希望结果。
对糟糕的桌子表示歉意,不确定如何格式化。
我建议在您的where
子句中使用not exists
使用相关的子查询,以寻求以后测试 same same same sest tester不是 Math
,例如:
select ro.Name, t.Test, d.Date
from
Roster ro
join registration re on re.name = ro.name
join test t on t.test_id = re.test_id
join date d on d.session = t.session
where
t.Test = 'Math' and not exists
(
select 1
from
Roster ro2
join registration re2 on re2.name = ro2.name
join test t2 on t2.test_id = re2.test_id
join date d2 on d2.session = t2.session
where
ro2.name = ro.name and
t2.test <> 'Math' and
d2.date > d.date
)
我相信您的原始代码中有一个错字,因为没有别名r
(在select
子句中使用(,并且您也可以在别名ro
上加入,也不存在。