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

This topic created in 4601 days ago, the information mentioned may be changed or developed.

表 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 replies • 1970-01-01 08:00:00 +08:00

yangqi

Oct 20, 2013

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

zxy

Oct 20, 2013

很久没写了，不知对错，还望指正
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

Oct 20, 2013

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

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

thinkif

Oct 20, 2013

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

用 exists 比 In 快些