SQL查询 - 不在一组已经使用的项目中

问题描述：

Users table: id | name 
Jobs:  id | name 
Assigned: id | user_id | job_id | date_assigned

我想选择所有当前未采取的作业。例如：

用户：

id | name 
-------------- 
1 | Chris 
2 | Steve

乔布斯

id | name 
--------------- 
1 | Sweep 
2 | Skids 
3 | Mop

分配

id | user_id | job_id | date_assigned 
------------------------------------------------- 
1 | 1  | 1   | 2012-01-01 
2 | 1  | 2   | 2012-01-02 
3 | 2  | 3   | 2012-01-05

没有两个人可以分配相同的工作。所以查询将返回

[1, Sweep]

由于没有人正在努力，因为克里斯一天后搬到了斯基德。

到目前为止：

SELECT 
    * 
FROM 
    jobs 
WHERE 
    id 
NOT IN 
    (
     SELECT 
      DISTINCT(job_id) 
     FROM 
      assigned 
     ORDER BY 
      date_assigned 
     DESC 
    )

然而，这个查询在相同的数据集中返回NULL。不解决扫描工作现在处于打开状态，因为它目前尚未开始工作。

答

SELECT a.* 
FROM jobs a 
LEFT JOIN 
(
    SELECT a.job_id 
    FROM assigned a 
    INNER JOIN 
    (
     SELECT MAX(id) AS maxid 
     FROM assigned 
     GROUP BY user_id 
    ) b ON a.id = b.maxid 
) b ON a.id = b.job_id 
WHERE b.job_id IS NULL

这会得到每个用户最近的工作。一旦我们有了这些工作的清单，我们就选择不在该清单上的所有工作。

这似乎不适合我。但我会继续尝试。 – user1549575 2012-07-24 19:02:03

@ user1549575你至少可以解释为什么它不起作用？这是一个错误，错误的结果？另外，你能回答我在问题评论中提出的问题吗？ “目前采取”是什么意思？ – 2012-07-24 19:04:20

它不会工作，因为它不回答问题。问题不在于选择从未分配的作业。这是要正确排序分配历史。 – 2012-07-24 19:08:09

答

你可以试试这个变种：

select * from jobs 
where id not in (
    select job_id from (
    select user_id, job_id, max(date_assigned) 
     from assigned 
    group by user_id, job_id));

答

我想你可能想：

SELECT * 
FROM jobs 
WHERE id NOT IN (SELECT job_id 
       from assigned 
       where user_id is not null 
       )

这是假设重新分配有人更改原始分配的用户ID。这是否发生？顺便说一下，我也简化了子查询。

答

首先您需要查看仅当前作业分配的列表。订购是不够的。您设置的方式，您需要一个来自Assigned的作业分配的不同子集，它们是最近的作业。

所以，你想要一个分组子查询像

select job_id, user_id, max(date_assigned) last_assigned from assigned group by job_id, user_id

把它放在一起，你会得到

select id, name from jobs 
where id not in (
    select job_id as id from (
    select job_id, user_id, max(date_assigned) last_assigned from assigned 
    group by job_id, user_id 
) 
)

作为一个额外的功能，你可以放过去“last_assigned”的值，并将其会告诉你一份工作闲置了多久。

SQL查询 - 不在一组已经使用的项目中

相关推荐