仅检索1行匹配的联接查询

好的,我已经重新创建了您的示例表,以便我们可以在SSMS中使用它们;

IF OBJECT_ID('tempdb..#User') IS NOT NULL DROP TABLE #User
CREATE TABLE #User (UserID int, Name nvarchar(4), Type int)
INSERT INTO #User (UserID, Name, Type)
VALUES
 (1,'John',1)
,(2,'Beth',2)
,(3,'Mike',5)
,(4,'John',5)

IF OBJECT_ID('tempdb..#Company') IS NOT NULL DROP TABLE #Company
CREATE TABLE #Company (CompanyID int, Name nvarchar(3))
INSERT INTO #Company (CompanyID, Name)
VALUES
 (1,'XYZ')
,(2,'KBC')
,(3,'MMM')

UserCompany 用户ID和公司ID与标题的顺序相同。当我看这个的时候,它显示出数据中根本没有用户3。出于这个目的,我将假设它实际上是按顺序排列的CompanyID和UserID。

IF OBJECT_ID('tempdb..#UserCompany') IS NOT NULL DROP TABLE #UserCompany
CREATE TABLE #UserCompany (CompanyID int, UserID int)
INSERT INTO #UserCompany (CompanyID, UserID)
VALUES
 (1,1)
,(1,3)
,(1,4)
,(2,3)
,(2,1)

SELECT
    c.Name AS Company_Name
    ,COUNT(u.UserID) Employees
FROM #Company c
INNER JOIN #UserCompany uc 
    ON c.CompanyID = uc.CompanyID
INNER JOIN #User u 
    ON uc.UserID = u.UserID
    AND u.Type = 5
GROUP BY c.Name
HAVING Count(u.UserID) > 1

Company_Name    Employees
XYZ             2

我只想更新一下 HAVING 条款:

SELECT UC.companyID 
FROM USER u JOIN 
     UserCompany UC 
     ON u.UserID = UC.UserID JOIN 
     Company C 
     ON uc.companyID = c.companyID
GROUP BY UC.companyID
HAVING SUM(CASE WHEN U.TYPE = 5 THEN 1 ELSE 0 END) = 1;