我有下面的book
张表
id | time | teacher | book | class |
---|---|---|---|---|
1 | 22-05-25 03:20:39 | 21 | null | 1 |
2 | 22-05-25 03:20:39 | 21 | 7853 | 2 |
3 | 22-05-25 03:20:39 | 21 | 7854 | 2 |
4 | 22-05-25 03:20:39 | 21 | 7855 | 2 |
5 | 22-05-25 03:20:39 | 21 | 7856 | 2 |
和以下SQL
SELECT t0_.*
FROM book t0_
LEFT JOIN book t1_ ON (t0_.teacher= t1_.teacher AND t0_.id < t1_.id)
WHERE
t0_.book IS NOT NULL AND
t0_.teacher IN (21) AND
t1_.id IS NULL;
这就像预期的一样,返回id
5
.但当t0_.book IS NOT NULL
改为t0_.book IS NULL
时,我得到的是空白结果,而不是id
1
.所以我try 了另一种方法
SELECT t0_*, MAX(t0_.id) AS uid
FROM book t0_
WHERE
t0_.book IS NOT NULL AND
t0_.teacher IN (21) AND
GROUP BY t0_.time
这次我得到了id
2
而不是id
5
.
EDIT:
在书本为空的情况下,我如何才能在每个班级找到最好的老师?我不知道是否需要进一步澄清.