查詢選擇卡和交易

我在查詢定義方面遇到了一些問題。我的數據庫是關於銀行業務的，我想顯示所有 ATM 交易中使用的卡號。

這就是我的表 DDL 的方式：

CREATE TABLE Cards(
   card_id INT PRIMARY KEY IDENTITY(1,1),
   number VARCHAR(25),
   CVV CHAR(3),
   bankAccount_id INT REFERENCES BankAccount(account_id)
);
CREATE TABLE Transactions(
   transaction_id INT PRIMARY KEY IDENTITY(1,1),
   ATM_id INT REFERENCES ATM(id),
   card_number INT REFERENCES Cards(card_id),
   sum_money INT,
   transaction_time DATETIME
);

我嘗試了以下查詢：

SELECT *
FROM Cards C
WHERE C.card_id = ALL (SELECT *
                       FROM Transactions T
                       WHERE T.card_number = C.card_id
                       )

而且它不起作用，因為我在兩邊都使用了 card_id。有人可以幫我嗎？

你可以這樣做：

SELECT T.card_number
FROM Cards AS C 
INNER JOIN Transactions AS T
   ON C.card_id = T.card_number
GROUP BY T.card_number -- Grouping up on Card_Number to get the count of unique ATMs each card was used at in the HAVING clause
HAVING COUNT(DISTINCT ATM_id) = (SELECT COUNT(1) FROM ATM) -- Count of distinct ATMs each card was used at needs to equal the total number of ATMs

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