我尝试获取我公司去年未分配任何任务的所有客户的列表。
SELECT MAX(assignment_date), full_name
FROM assignments
CROSS JOIN customers
WHERE assignments.customer_id = customers.id
AND assignment_date < '2017-01-01' -- Dynamic value from backend
GROUP BY full_name
ORDER BY assignment_date DESC
但是,这似乎没有按预期工作,因为它只返回我们在该时间范围内确实有任务的一些客户。我将如何实现这样的功能?
试试这段代码:
SELECT MAX(assignment_date), full_name
FROM customers
where id not in (SELECT id FROM customers inner join assigments on customers.id = assignments.customer_id WHERE assignment_date > '2017-01-01' )
这将返回数据库中的所有客户,并删除去年确实有过同工关系的所有客户。因此,您应该在"2017-01-01"之前获得所有没有识别的客户
将客户加入assignments.customer_id为 NULL 的作业 assignment_date大于"2017-01-01",即
SELECT MAX(assignment_date), full_name
FROM assignments
WHERE assignments.customer_id IN
(SELECT customers.id
FROM customers
LEFT JOIN assignments ON assignments.customer_id = customers.id
WHERE assignments.customer_id IS NULL
AND assignment_date > '2017-01-01')
GROUP BY full_name
ORDER BY assignment_date DESC
我建议left join
,group by
和having
:
SELECT MAX(assignment_date), full_name
FROM customers c LEFT JOIN
assignments a
ON a.customer_id = c.id
GROUP BY c.full_name
HAVING MAX(a.assignment_date) < '2017-01-01' OR
MAX(a.assignment_date) IS NULL
ORDER BY MAX(assignment_date) DESC;
您似乎希望显示所有客户及其上次分配日期,但您希望将该列表限制为自 2017-01-01 以来没有分配的客户。这意味着对于从未有过分配的客户,您将显示的日期将为空,而对于其他客户,将显示的日期在 2017-01-01 之前
。因此,将最后日期外部联接给客户,并仅保留该日期早于 2017-01-01 或 null 的行:
select c.full_name, a.max_date
from customers c
left join
(
select customer_id, max(assignment_date) as max_date
from assignments
group by customer_id
) a on a.customer_id = c.customer_id
where a.max_date < date '2017-01-01'
or a.max_date is null
order by a.max_date desc;