sql - CTE 必要或多个连接
问题描述
我有一张名为朋友的表,如下所示:
column type
user_id integer
friend_user_id integer
列出的页面如下所示:
page_likes
column type
user_id integer
page_id integer
我想输出以下字段: 基本上显示喜欢该页面的 user_id、page_id 和朋友的数量,但不显示实际用户已经喜欢的页面。
user_id, page_id, count(distinct friend_user_id)
WITH t1 AS (
SELECT
f.user_id
, f.friend_user_id
, pl.page_id
FROM friends AS f
INNER JOIN page_likes AS pl
ON f.friend_user_id = pl.user_id
)
SELECT t1.user_id, t1.page_id, COUNT(DISTINCT t1.friend_user_id)
FROM t1
LEFT JOIN page_likes AS pl
ON t1.page_id = pl.page_id
AND t1.user_id = pl.user_id
WHERE pl.user_id IS NULL
GROUP BY 1
我也可以在没有 cte 的情况下执行上面的相同查询并再次加入同一个表吗?
SELECT t1.user_id, t1.page_id, COUNT(DISTINCT t1.friend_user_id)
FROM friends f
INNER JOIN page_likes pl
ON f.friend_user_id = pl.user_id
LEFT JOIN page_likes AS pll
ON t1.page_id = pll.page_id
AND t1.user_id = pll.user_id
WHERE pll.user_id IS NULL
GROUP BY 1
谢谢!
解决方案
为什么不:
SELECT f.user_id,pl.page_id,COUNT(*) friends_page_like_count
FROM friends f
INNER JOIN page_likes pl ON f.friend_user_id = pl.user_id
GROUP BY f.user_id,pl.page_id
推荐阅读
- r - 计算百分比变化并将其放在表格中
- java - 如何将 id 传递给 th:value 并获取 List 类型的数据
? - android - 如何在 ConstraintLayout 中将视图居中而不重叠不均匀的邻居
- hibernate - Cannot able to display the list through jsp
- objective-c - Objective-C/Cocoa 到 JXA
- windows - Unable to use libcurl with cffi on Windows 10
- flutter - 写 NFC 卡
- karate - OpenID connect authentication is supported in karate
- swift - In my case, you can star and change stop shimmer animation?
- php - AWS Lambda php:放置 php.ini 的位置