我有一个包含两个表的数据库。客户表和用户表。

客户表

ClientID ClientName UserId
1 Client A 1
1 Client A 2
1 Client A 3
2 Client B 1
2 Client B 2
2 Client C 4
3 Client C 1
3 Client C 5

用户表

UserID userName
1 User 1
2 User 2
3 User 3
4 User 4
5 User 5

我想得到这个结果

ClientID ClientName UserName occurence of user with other client
1 Client A User 1 2
1 Client A User 2 1
1 Client A User 3 0

请你帮助我好吗 ？这是我尝试过的

SELECT DISTINCT CT.clientid, 
                CT.clientname, 
                UT.username, 
                Count(UT.userid IN (SELECT ct.userid 
                                      FROM   ct)) 
FROM   clienttable CT 
       LEFT JOIN usertable UT 
              ON UT.userid = CT.userid 
WHERE  CT.clientid = 1
分析解答
SELECT c.ClientID, 
       c.ClientName, 
       u.userName,
       ( SELECT COUNT(*)
         FROM Client c1
         WHERE c.UserID = c1.UserID
           AND c.ClientID != c1.ClientID ) others
FROM Client c
JOIN Users u USING (UserID) 
-- WHERE c.ClientID = 1

小提琴