我有 3 張桌子：
CREATE TABLE PropertyManagers(
pmNo INT NOT NULL AUTO_INCREMENT,
userNo INT,
PRIMARY KEY(pmNo),
FOREIGN KEY(userNo) REFERENCES Users(userNo)
);
CREATE TABLE Addresses(
addressNo INT NOT NULL,
city varchar(30),
province varchar(5),
postalCode varchar(6),
buildingName varchar(20),
pmNo INT,
PRIMARY KEY(addressNo),
FOREIGN KEY(pmNo) REFERENCES PropertyManagers(pmNo)
);
CREATE TABLE Units(
unitId INT NOT NULL AUTO_INCREMENT,
unitNo INT NOT NULL,
userNo INT,
addressNo INT,
PRIMARY KEY(unitId),
FOREIGN KEY(userNo) REFERENCES Users(userNo),
FOREIGN KEY(addressNo) REFERENCES Addresses(addressNo)
);
我有 2 個 pmNo 為 1 的地址，這兩個地址共有 3 個單元，2 個單元，其中 addressNo = 1 和 1 個單元，其中 addressNo = 2。
我想獲取 pmNo = 1 的所有地址以及每個地址的單元總數。我的 SQL 查詢是這樣的：
SELECT a.*, COUNT(u.unitId) AS totalUnits
FROM Units AS u
JOIN Addresses AS a ON a.addressNo = u.addressNo
JOIN PropertyManagers AS pm ON pm.pmNo = a.pmNo
WHERE pm.pmNo = 1
這給了我以下結果：
addressNo city province postalCode buildingName pmNo totalUnits
1 city1 prov1 V9F3b6 building1 Apt. 1 3
這是正確的，但它只給了我第一個地址欄位，然后將兩個地址單元合并到總單元中。
我希望結果是：
addressNo city province postalCode buildingName pmNo totalUnits
1 city1 prov1 V9F3b6 building1 Apt. 1 2
2 city2 prov2 V0N1U8 building2 Apt. 1 1
uj5u.com熱心網友回復：
COUNT對于非簡單情況的聚合函式需要指定發生的聚合。在您的情況下，您需要地址。作為addressNo唯一地址的主鍵和最簡單的識別符號使用GROUP BY查詢的：
SELECT a.*, COUNT(u.unitId) AS totalUnits
FROM Units AS u
JOIN Addresses AS a ON a.addressNo = u.addressNo
JOIN PropertyManagers AS pm ON pm.pmNo = a.pmNo
WHERE pm.pmNo = 1
GROUP BY a.addressNo
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/454016.html
上一篇：如何找到每個人擁有的寵物數量