使用多個條件從兩個表中選擇不同的行

我試圖找出一個查詢。我的數據庫中有一個offices和meetings表。辦公室可以有多個會議（辦公室 ID 是 office 表的 FK）。有些會議允許午餐，有些則不允許。我正在嘗試查找在 1 月份舉行會議並允許午餐的所有辦公室的列表，這些辦公室也在 2 月份舉行了不允許午餐的會議。

我的淨化架構看起來像這樣：

+---------------+   +---------------+
| meetings      |   | offices       |
+---------------+   +---------------+
| id            |   | id            |
| meeting_date  |   | name          |
| allowed_lunch |   | address_id    |
| office_id     |   | phone         |
+---------------+   +---------------+

我想要一個包含辦公室 ID、姓名、電話號碼的列表，以及我需要加入的其他表中的一些資訊，例如街道地址、郵政編碼、州等。到目前為止，我只能想出這樣做的笨拙方式；我使用兩個查詢。

第一個查詢使用子查詢獲取不同的辦公室 ID 列表：

select distinct offices.id from offices
join meetings on offices.id = meetings.office_id
where offices.id in (
 select
   distinct offices.id
 from offices
 join meetings on meetings.office_id = offices.id
 where 
   DATE(meeting_date) < '2020-01-31' and DATE(meeting_date) >= '2020-01-01' and allowed_lunch = 1
)
and DATE(meeting_date) < '2020-02-28' and DATE(meeting_date) >= '2020-02-01' and allowed_lunch = 0;

然後，我手動獲取該辦公室 ID 列表並在單獨的查詢中再次查找它們，以從其他表中提取我需要的附加資訊。所以是這樣的：

SELECT office.name, office.phone, ..., address.zip, address.state
FROM offices
JOIN addresses on offices.address_id = addresses.id
WHERE office.id in (
... big list from first query ...
);

我需要的是一個不同的辦公室列表，它滿足頂部列出的兩個條件。有沒有更好的方法來做到這一點？最好在單個查詢中？

（我可以採用第一個查詢並將其粘貼在第二個查詢的 WHERE 子句下。但我在執行此操作時遇到了性能問題。第一個查詢大約需要 10 秒，第二個查詢非常快，但是當我將它們組合在一起時額外的子查詢，它變得非常慢。另外，它似乎是一種相當混亂的處理方式。）

示意圖：

SELECT offices.id
-- join tables
FROM offices
JOIN meetings ON offices.id = meetings.office_id
-- select only rows for Jan and Feb 2020
WHERE DATE(meeting_date) BETWEEN '2020-01-01' AND '2020-02-29'
-- grouping - prepare calculations for separate offices
GROUP BY offices.id
-- check that the amount of `allowed_lunch` in Feb is zero
HAVING NOT SUM(DATE(meeting_date) BETWEEN '2020-02-01' AND '2020-02-29' AND allowed_lunch)
-- but total is not zero (taking into account prev. check - they're in Jan)
  AND SUM(allowed_lunch)

使用子查詢

select off.id,
      off.name,
      off.phone,
      a.street_address,
      a.zip,
      a.city
from office as off
        inner join (select m1.office_id
                    from meeting as m1
                    where (date(m1.meeting_date) between '2020-02-01' and '2020-02-29')
                      and m1.allowed_lunch = 0) as feb
                   on feb.office_id = off.id
        inner join address as a
                   on off.address_id = a.id
where off.id in (select m2.office_id
                        from meeting as m2
                        where (date(m2.meeting_date) between '2020-01-01' and '2020-01-30')
                          and m2.allowed_lunch = 1)

引用自：https://dba.stackexchange.com/questions/268753