“倒置"指的是“反向". SQL SELECT-查找在某个日期范围内没有打过电话的员工

问题描述

我不确定在这里我在做什么错，但是我有点困惑.这是我的SQL语句，用于选择在日期和时间之间DID进行冷呼的所有员工:

I'm not sure what I'm doing wrong here but I've gotten my brain in a bit of a muddle. Here is my SQL statement to select all staff who DID make cold calls between a date and time:

SELECT staff.name
FROM staff

JOIN staff_prospects ON staff_prospects.staff_id = staff.id
JOIN staff_prospect_activity ON staff_prospect_activity.prospect_id = staff_prospects.id

WHERE DATE_FORMAT(staff_prospect_activity.date_of_activity, "%Y-%m-%d") BETWEEN "2013-05-31" AND "2013-05-31"

GROUP BY staff.name

这将列出所有被称为潜在客户的员工姓名. 但是我需要反转"此查询，以便在相同的日期范围内将所有未查询的员工都获取.

That will list all staff names who have cold-called a prospect. But I need to 'invert' this query so it gets all staff who didn't, between the same date range.

感谢您的帮助！

推荐答案

使用LEFT JOIN

SELECT  a.name
FROM    staff a
        INNER JOIN staff_prospects b
            ON b.staff_id = a.id
        LEFT JOIN staff_prospect_activity c
            ON  c.prospect_id = b.id AND
                c.date_of_activity >= '2013-05-31' AND 
                c.date_of_activity < '2013-05-31' + INTERVAL 1 DAY
WHERE   c.prospect_id IS NULL
GROUP   BY a.name

这篇关于“倒置"指的是“反向". SQL SELECT-查找在某个日期范围内没有打过电话的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！