SQL从列表中删除结果

Question

我有一个Locations表和一个Visit表。每个地点需要根据特定的访问类型每年访问一次。当我们记录一次访问时，我们会添加访问的类型和地点(日期、时间和所有其他内容)。我需要从我们收集到的信息中推断出那些没有人去过的地方。

例如，地点A-Z(26个地点)可以有3种类型的访问(会议，审查和年度)。

一些位置可能根本不会被访问，而另一些位置可能会被访问多次。

我需要知道哪些地点没有进行年度访问。

我的SQL语句似乎给了我一个每年访问一次的位置列表，但我没有看到从更大的列表中消除一个条目或可能过滤大列表的方法。

SELECT
Location.Name
,Location.ID
,Visit.Date
,Visit.Time
,Visit.Visit_Type
FROM Location
INNER JOIN Visit
ON Location.ID = Visit.LocationID
WHERE Visit.Group_Type = 'School'
AND ( Visit.Date BETWEEN '2021' AND '2022' )

Answer 1

一种方法是检查是否缺少年度，使用EXISTS语句访问。

SELECT        
L.Name, L.ID
FROM          
Location L             
WHERE 
NOT EXISTS
(
SELECT 
* 
FROM 
Visit V2 
WHERE 
V2.LocationID = L.LocationID 
AND V2.Group_Type = 'School' 
AND ( V2.Date BETWEEN '2021' AND '2022' )
)

Answer 2

模式(MySQL v8.0)

CREATE TABLE location (
`name` VARCHAR(8),
`id` INTEGER
);
INSERT INTO location
(`name`, `id`)
VALUES
('here', '1'),
('there', '2'),
('anywhere', '3');
CREATE TABLE visit (
`id` INTEGER,
`type` VARCHAR(7)
);
INSERT INTO visit
(`id`, `type`)
VALUES
('1', 'meeting'),
('1', 'review'),
('2', 'meeting'),
('2', 'annual'),
('3', 'annual');

---

无年诊

SELECT name
FROM location
JOIN visit 
USING(id)
GROUP BY name
HAVING COUNT(CASE WHEN type = 'annual' THEN 1 END) = 0;

<表类>名称tbody><<tr>在

您可以获得今年所有访问的列表，然后使用OUTER JOIN来查找今年尚未访问的位置。

;WITH visitsThisYear AS
(
SELECT
*
FROM
Visit
WHERE 
Visit.Group_Type = 'School'
AND ( Visit.Date BETWEEN '2021' AND '2022' )
),        
SELECT 
Location.Name,
Location.ID
FROM
LOCATION
LEFT OUTER JOIN visitsThisYear
ON Location.ID = visitsThisYear.LocationID
WHERE Location.ID IS NULL