我有兩個具有以下值的 Microsoft Access 表：
Devices
| N | Desc | O | P |
------ -------- --- ---
| 3560 | ABC | 0 | 0 | <-
| 3559 | DEF | 0 | 1 |
| 3558 | GHI | 1 | 0 |
| 3557 | JKL | 1 | 0 |
| 3548 | MNO | 0 | 0 | <-
| 3549 | PQR | 0 | 0 | <-
| 3540 | STU | 0 | 0 | <-
Notifications
id | N | Email |
--- ------ -------------
1 | 3559 | [email protected] | <-
2 | 3548 | [email protected] | <-
3 | 3548 | [email protected] |
4 | 3547 | [email protected] |
5 | 3549 | [email protected] |
我想用 and 提取所有記錄Devices并添加一個名為Subscribed的計算欄位。只有當通知表中存在相同的欄位時，此欄位才必須是我提供的引數，否則。O = 0P = 0TrueNEmailFalse
具有例如Email = [email protected]引數（即為此目的在 SQL 中硬編碼），那么這應該是所需的結果：
| N | Desc | O | P | Subscribed |
------ -------- --- --- ------------
| 3560 | ABC | 0 | 0 | False |
| 3548 | MNO | 0 | 0 | True |
| 3549 | PQR | 0 | 0 | False |
| 3540 | STU | 0 | 0 | False |
考慮到這兩個表將來可能會很大，我如何在 Access 中執行此查詢？
uj5u.com熱心網友回復：
可能我錯過了子查詢 SQL，這有效：
SELECT Devices.N, Devices.Desc, Devices.O, Devices.P,
(SELECT Count(*) FROM Notifications
WHERE Notifications.N = Devices.N
AND Notifications.Email = "[email protected]") > 0 AS Subscribed
FROM Devices
WHERE ((Devices.O=0) AND (Devices.P=0))
編輯：@iDevlop 評論后，我能夠正確創建 LEFT JOIN 變體，謝謝！
SELECT Devices.N, Devices.Desc, Devices.O, Devices.P, Not IsNull(Id) AS Subscribed
FROM Devices
LEFT JOIN (SELECT Notifications.N, Notifications.Id
FROM Notifications
WHERE (Notifications.Email = "[email protected]")) AS qNotif
ON Devices.N = qNotif.N
WHERE ((Devices.O=0) AND (Devices.P=0));
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/439028.html