--写了好一会，你感受一下。

USE tempdb
GO

IF EXISTS(SELECT 0 FROM SYSOBJECTS WHERE name='U')
    DROP TABLE U
CREATE TABLE U
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
NAME NVARCHAR(20)
)
GO

INSERT INTO U
VALUES('张三'),('李四'),('王五'),('赵六')
GO

IF EXISTS(SELECT 0 FROM SYSOBJECTS WHERE name='C')
    DROP TABLE C
CREATE TABLE C
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
NAME NVARCHAR(20)
)
GO
INSERT INTO C
VALUES('北京'),('上海')
GO

IF EXISTS(SELECT 0 FROM SYSOBJECTS WHERE name='U_C')
    DROP TABLE U_C
CREATE TABLE U_C
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
UID INT,
CID INT
)
GO
INSERT INTO U_C
VALUES(1,1),(2,1),(3,1),(4,1),(1,2),(1,2)
GO

--方法一
SELECT U.* FROM U
LEFT JOIN U_C ON U.ID=U_C.UID
LEFT JOIN C ON U_C.CID=C.ID
WHERE C.NAME='北京'
INTERSECT
SELECT U.* FROM U
LEFT JOIN U_C ON U.ID=U_C.UID
LEFT JOIN C ON U_C.CID=C.ID
WHERE C.NAME='上海'
GO
--方法二
SELECT U.* FROM
(
    SELECT UC.UID FROM
    (
        SELECT UID,CID FROM U_C GROUP BY UID,CID
    ) AS UC LEFT JOIN C ON UC.CID=C.ID
    WHERE C.NAME IN('北京','上海')
    GROUP BY UC.UID HAVING COUNT(UID)>1
) AS A LEFT JOIN U ON A.UID=U.ID
GO
--方法二-扩展
IF EXISTS(SELECT 0 FROM SYSOBJECTS WHERE name='V')
    DROP VIEW V
GO
CREATE VIEW V
AS
SELECT A.CID,A.UID,U.NAME AS UNAME,C.NAME AS CNAME FROM
(
    SELECT CID,UID FROM U_C GROUP BY CID,UID
) AS A
LEFT JOIN U ON A.UID=U.ID
LEFT JOIN C ON A.CID=C.ID
GROUP BY A.CID,A.UID,U.NAME,C.NAME
GO

SELECT UID,UNAME FROM V WHERE CNAME IN('北京','上海')
GROUP BY UID,UNAME HAVING COUNT(0)>1