SELECT Categories.Name,count(DISTINCT categories.name) FROM Categories
JOIN Domains ON Categories.ID=Domains.CID
JOIN Records ON Records.DID=Domains.ID
GROUP BY Categories.Name
经过以下设置测试:
CREATE TABLE Categories (Name nvarchar(50), ID int NOT NULL IDENTITY(1,1))
CREATE TABLE Domains (Name nvarchar(50), ID int NOT NULL IDENTITY(1,1), CID int)
CREATE TABLE Records (Name nvarchar(50), ID int NOT NULL IDENTITY(1,1), DID int)
INSERT INTO Records (DID) VALUES (1)
INSERT INTO Records (DID) VALUES (1)
INSERT INTO Records (DID) VALUES (2)
INSERT INTO Records (DID) VALUES (2)
INSERT INTO Records (DID) VALUES (3)
INSERT INTO Records (DID) VALUES (3)
INSERT INTO Domains (Name,CID) VALUES ('D1',1)
INSERT INTO Domains (Name,CID) VALUES ('D2',1)
INSERT INTO Domains (Name,CID) VALUES ('D5',1)
INSERT INTO Domains (Name,CID) VALUES ('D3',2)
INSERT INTO Domains (Name,CID) VALUES ('D4',2)
INSERT INTO Categories (Name) VALUES ('1')
INSERT INTO Categories (Name) VALUES ('2')
INSERT INTO Categories (Name) VALUES ('3')