一道Facebook DS SQL题

直接用inner join呢？

select A.userid, B.pageid
from tb_userfriends A
inner join tb_userpages B
on A.friendid = B.userid
except
select A.userid, B.pageid
from tb_userfriends A
inner join tb_userpages B
on A.userid= B.userid



补充内容 (2019-5-5 11:22):
下一段错了，多此一举

kela0011 发表于 2018-7-22 09:57
Does this work?

Select f.user_id, p.pg

验证了下，这个不对呀。

请问一下 fb ds 面试都只考sql吗 考不考coding的？

谢谢楼主，非常有用的信息！！

with user1 AS (
SELECT DISTINCT users.user_id as userid, pages.page_id as pageid1
FROM users JOIN pages ON users.friend_id = pages.user_id),
page1 AS (
SELECT DISTINCT users.user_id as userid, pages.page_id as pageid2
FROM users JOIN pages ON users.user_id = pages.user_id)
SELECT user1.userid, user1.pageid1
FROM user1 LEFT JOIN page1
ON user1.userid = page1.userid and user1.pageid1 = page1.pageid2
WHERE page1.pageid2 IS NULL

之前几个点赞高的query我拿sql试了下感觉都不是很对。然后自己写了个，刚用SQL试了下，出来结果感觉是对的。但是觉得好像写得太麻烦了。。。

加一句，我觉得上面有楼主说的except很好用！都没有想到！我要给他点赞！
SELECT DISTINCT users.user_id as userid, pages.page_id as pageid1
FROM users JOIN pages ON users.friend_id = pages.user_id
EXCEPT
SELECT DISTINCT users.user_id as userid, pages.page_id as pageid1
FROM users JOIN pages ON users.user_id = pages.user_id

hazel712 发表于 2018-8-14 11:18
第二个应该是left join而不是inner join？

你好，可以请问为什么应该是left join吗？

感谢楼主提出，最近也在准备

scorpiofay 发表于 2018-12-11 12:54
为啥大家都写得这么复杂？我怎么觉得一个EXCEPT就可以了啊。。。

SELECT T1.UID, T2.LIKED_PID

elegant! 看了下别的回复都太复杂了 这个比较靠谱