请教这个需求的SQL怎么写，谢谢！

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 4060 天前的主题，其中的信息可能已经有所发展或是发生改变。

表 follow
+----+---------+------------+---------------------+
| id | user_id | channel_id | created |
+----+---------+------------+---------------------+

表 channel
+----+---------+------------+---------------------+
| id | name | created |
+----+---------+------------+---------------------+

现在需要
1. 先从 follow 表中选择 user_id=xxx 的所有记录,
2. 然后再从原来的 follow 表中选出 channel_id 不等于1步骤中选出的所有记录的 channel_id 的记录
3. 最后从 channel 表中选出所有 id 和2步骤中选出的记录的 channel_id 相等的记录

请教只用一句的SQL语句怎么写？谢谢！

channel

表中

4 条回复 • 1970-01-01 08:00:00 +08:00

yangqi

2013-10-20 12:08:53 +08:00

SELECT id FROM channel WHERE channel_id IN ( SELECT channel_id FROM follow WHERE user_id != 'xxx' )

zxy

2013-10-20 12:17:09 +08:00

很久没写了，不知对错，还望指正
1、若channel_id 不重复，应该可以用这个
select *
from
channel
where channel_id in (select chnnel_id from follow where user_id <>'xxx')
2、若channel_id 重复，改为
select *
from
channel
where channel_id in
(select chnnel_id
from
follow
where channel_id not in
(select
channel_id
from
follow
where user_id ='xxx')

yangqi

2013-10-20 12:52:42 +08:00

读了N遍终于看懂楼主的意思了, 你给的步骤太复杂了,

直接 SELECT id FROM channel WHERE channel_id NOT IN ( SELECT channel_id FROM follow WHERE user_id = 'xxx' )

thinkif

2013-10-20 13:09:04 +08:00

select id, name, created from channel where exists(select 1 from follow where follow.channel_id = channel.id and user_id<>'xxx')

用 exists 比 In 快些