我有两张表,然后用一张关系表建立多对多关系。
比如,用户表(User)、城市表(City)、和用户去过的城市(UserCity)。
我想把即去过北京又去过上海的人找出来,请问sql该怎么写。
楼上的 : in('上海','北京') ,并不是即去过北京又去过上海的。exists ()子句中去过上海或北京或都去过的 都会成立。
首先呢(三表联查)查找出所有去过北京或是上海的人(username,cityname)再用distinct将(username,cityname)去重,最后将以上结果集作为子查询表,以username分组取其count(username)=2 的username。That's OK!
我开始也是这个思路,但感觉有点繁琐,不知道有没有更好的
select UserId from UserCity
where CityName in ('北京', '上海')
group by UserId having count(UserId) = 2
用inner join
select * from User a inner join UserCity b on a.ID=b.userID
and exists (select 1 from City c where c.ID=B.cityID and C.City in('上海','北京'))
--写了好一会,你感受一下。 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
十分感动,谢谢!
有心之人呐,挣点豆豆真是不容易啊。