sql - 仅连接另一个表中每一行的最高(最新)值

Question

我是 SQL 新手，所以现有的答案对我来说有点复杂。
我有三张 table :

WORKER
|id 
|name
|date
|...

JOB
|id
|name
|salary
|accept

APPOINTMENT
|id
|worker_id
|job_id
|date

因此，如果 worker 在一年内多次被任命，我需要知道他在某个特定时间从事什么工作。

我现在有这样的事情:

SELECT w.name,w.id FROM worker w 
INNER JOIN appointment a ON w.id = worker_id
INNER JOIN job j on job_id = j.id 
WHERE accept = 1 AND a.date <= (SELECT date FROM orders WHERE id = 2);

现在它显示所有约会小于或等于某个日期，但我只需要每个 worker 的最后一个。
我需要如何修改它？

编辑:

ORDER
|id
|accepted_by //worker_id
|...

订单用于获取日期。它可以从任何来源更改为任何来源。所以在这种情况下它并不重要。
Job 中的 Accept 只是一个 bool 值，表示指定的 worker 可以接受新的订单。
因此，这样做的全部含义是在创建订单时，在 ComboBox 的编辑表单中显示所有能够接受订单的 worker (不仅仅是现在可以接受的 worker )。

日期表示为从 1970 年开始的天数的整数值。
假装在输出中的行:

w.name  w.id  a.id  a.date  j.name        j.accept
Smith   2     7     42999   administrator 1
Joe     1     6     42994   administrator 1
Smith   2     5     42994   waiter        0
Joe     1     4     42993   waiter        0
Smith   2     3     42992   administrator 1
Smith   2     2     42991   waiter        0
Smith   2     1     42990   administrator 1

我收到的查询(在此编辑上方列出)a.date <= 42998 并接受 = 1;

Joe     1     6     42994   administrator 1
Smith   2     3     42992   administrator 1 //isn't current Smith's job
Smith   2     1     42990   administrator 1 //isn't current Smith's job

我的查询应该收到什么 a.date <= 42998;

//Last job in 42998
Joe     1     6     42994   administrator 1
Smith   2     5     42994   waiter        0

我的查询应该收到什么 a.date <= 42999;

//Last job in 42999
Smith   2     7     42999   administrator 1
Joe     1     6     42994   administrator 1

我最终需要收到的内容(a.date <= 42998 and accept=1):

//Workers which were able to accept order in 42998
Joe     1     6     42994   administrator 1

如果 (a.date <= 42999 and accept=1) 我应该收到什么；

//Workers which were able to accept order in 42999
Smith   2     7     42999   administrator 1
Joe     1     6     42994   administrator 1

表格(所有未使用的字段都被删除):

CREATE TABLE appointment (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, worker_id INTEGER NOT NULL, job_id INTEGER, date INTEGER NOT NULL);
CREATE TABLE worker (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT NOT NULL);
CREATE TABLE job (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT NOT NULL,accept INTEGER NOT NULL);

插入(如上面的示例):

INSERT INTO worker (name) VALUES ('Joe');
INSERT INTO worker (name) VALUES ('Smith');
INSERT INTO job (name,accept) VALUES ('waiter',0);
INSERT INTO job (name,accept) VALUES ('administrator',1);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,2,42990);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,1,42991);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,2,42992);
INSERT INTO appointment (worker_id,job_id,date) VALUES (1,1,42993);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,1,42994);
INSERT INTO appointment (worker_id,job_id,date) VALUES (1,2,42994);
INSERT INTO appointment (worker_id,job_id,date) VALUES (2,2,42999);

Answer 1

要获取每个工作人员的最后一次约会，请使用分组。在第二步中过滤掉不被接受的:

SELECT ...
FROM (SELECT worker_id,
             job_id,
             MAX(date) AS date
      FROM appointment
      WHERE date <= ...
      GROUP BY worker_id) AS a
JOIN worker AS w ON a.worker_id = w.id
JOIN job    AS j ON a.job_id    = j.id
WHERE accept = 1;