首页 新闻 搜索 专区 学院

sql求指教

0
悬赏园豆:50 [已解决问题] 解决于 2013-05-22 17:11

我有两张表,然后用一张关系表建立多对多关系。

比如,用户表(User)、城市表(City)、和用户去过的城市(UserCity)。

我想把即去过北京又去过上海的人找出来,请问sql该怎么写。

sql
l3oz的主页 l3oz | 小虾三级 | 园豆:586
提问于:2013-05-22 15:07
< >
分享
最佳答案
1

楼上的 : in('上海','北京')   ,并不是即去过北京又去过上海的。exists ()子句中去过上海或北京或都去过的 都会成立。

首先呢(三表联查)查找出所有去过北京或是上海的人(username,cityname)再用distinct将(username,cityname)去重,最后将以上结果集作为子查询表,以username分组取其count(username)=2 的username。That's OK!

收获园豆:25
yyutudou | 小虾三级 |园豆:997 | 2013-05-22 15:53

我开始也是这个思路,但感觉有点繁琐,不知道有没有更好的

l3oz | 园豆:586 (小虾三级) | 2013-05-22 16:19

select UserId from UserCity
where CityName in ('北京', '上海') 
group by UserId having count(UserId) = 2

l3oz | 园豆:586 (小虾三级) | 2013-05-22 17:32
其他回答(3)
0

用inner join 

yjheimi | 园豆:209 (菜鸟二级) | 2013-05-22 15:09
-1

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('上海','北京'))

丫的 | 园豆:1575 (小虾三级) | 2013-05-22 15:15
0
--写了好一会,你感受一下。

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
收获园豆:25
写代码的小2B | 园豆:4346 (老鸟四级) | 2013-05-22 16:13

十分感动,谢谢!

支持(0) 反对(0) l3oz | 园豆:586 (小虾三级) | 2013-05-22 16:15

有心之人呐,挣点豆豆真是不容易啊。

支持(0) 反对(0) yyutudou | 园豆:997 (小虾三级) | 2013-05-22 16:44
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册