考考你 sql 和其他语言的思维上的区别

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

10 replies • 2019-03-03 09:12:14 +08:00

yuikns

Feb 3, 2019 via iPad

sql 当然是自己 join 自己然后 filter。

程序语言构造 set 然后 filter

rootzeal

Feb 3, 2019 via Android

@yuikns 这有点复杂可以考虑不用 join 的情况

FrailLove

Feb 3, 2019 via iPhone

a b union all b a group by

sql 语言要用集合的方式思考问题

widewing

Feb 3, 2019 via Android

取交集不用 inner join 用啥

jjplay

Feb 3, 2019 via iPhone

大过年的，饶了我 8⃣️

mingyun

Feb 3, 2019 via Android

哈哈，大过年的

yiyi11

Feb 4, 2019 via Android

@FrailLove 老哥厉害了，sql 做统计真是神器。

yiyi11

Feb 4, 2019 via Android

@FrailLove 老哥，这样对吗？
select count （*） / 2 as '互关对数' from
(
select uid as a, fid as b from t t1
union all
select fid as a, uid as b form t t2
) group by a, b having count(*) ＞ 1

CRVV

Feb 4, 2019

用 SQL 至少有两种写法吧

SELECT count(*) / 2 AS pairs
FROM user_relation a
INNER JOIN user_relation b ON a.friend_uid = b.user_id
WHERE a.user_id = b.friend_uid;

SELECT count(*) / 2 AS pairs FROM (
SELECT user_id, friend_uid FROM user_relation
INTERSECT
SELECT friend_uid, user_id FROM user_relation) AS subquery;

个人认为 JOIN 是 SQL 里最普通的写法

rootzeal

Mar 3, 2019 via Android

这里是完整的回答其实只要把 groupby 一下然后再 cout 过滤出 count 等于 2 的就行不用 join https://mp.weixin.qq.com/s/VOfzuZ5OaG3imxpTcEM0EA