Mysql
使用多個條件從兩個表中選擇不同的行
我試圖找出一個查詢。我的數據庫中有一個
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)