Sql查询错误，应仅返回在其他表中存在一次的供应商

Question

如果我试图执行它，我会遇到一个错误：

create View vwEinzellieferant
as
Select P.SupplierID, S.CompanyName, S.ContactName, S.Address + ' ' + S.City + ' ' + S.Region + ' ' + S.PostalCode + ' ' + S.Country, S.Phone
from Suppliers S inner join Products P on S.SupplierID = P.SupplierID
group by P.SupplierID, S.CompanyName, S.ContactName, S.Address, S.City, S.Region, S.PostalCode, S.Country, S.Phone
having (Count(S.SupplierID in (Select SupplierID from Products))) > 2;

在有一个问题，但我不知道什么。解释：查询应该创建一个视图，但只能与"产品"列表中的供应商一起创建。有人能帮我吗？

Answer 1

似乎需要重新排列HAVING子句，以便返回等于1的相关COUNT值，例如

SELECT P.SupplierID,
S.CompanyName,
S.ContactName,
S.Address + ' ' + S.City + ' ' + S.Region + ' ' + S.PostalCode + ' ' +
S.Country,
S.Phone
FROM Suppliers S
JOIN Products P
ON S.SupplierID = P.SupplierID
GROUP BY P.SupplierID,
S.CompanyName,
S.ContactName,
S.Address,
S.City,
S.Region,
S.PostalCode,
S.Country,
S.Phone
HAVING ( SELECT COUNT(*) FROM Products WHERE SupplierID = S.ID ) = 1