我有两张tablet1和t2。这里t1包含博客，t2包含喜欢帖子的用户列表。t1数据库架构post\u id，post和t2数据库架构post\u id，user\u id我只想选择那些帖子，是至少10个用户喜欢。
hmae6n7t1#
在表t2中，出现超过10行的post\u id是所需的post，因此您可以选择表t2中至少有10行出现post\u id的post
8ehkhllq2#
您可以使用join选择文章。
SELECT p.post_id, p.post, count(pl.user_id) as likes FROM `post` as pjoin post_likes as pl on p.post_id = pl.post_idwhere 1 group by pl.post_idhaving likes >= 10;
SELECT p.post_id, p.post, count(pl.user_id) as likes
FROM `post` as p
join post_likes as pl on p.post_id = pl.post_id
where 1 group by pl.post_id
having likes >= 10;
du7egjpx3#
SELECT T1.post_id, T1.post, COUNT(T2.user_id]) as TotalFROM T1 JOIN T2 ON T1.post_id = T2.post_id GROUP BY T1.post_id, T1.post HAVING Total >= 10
SELECT T1.post_id, T1.post, COUNT(T2.user_id]) as Total
FROM T1 JOIN T2 ON T1.post_id = T2.post_id
GROUP BY T1.post_id, T1.post
HAVING Total >= 10
4bbkushb4#
你能用一个副选择器吗？
SELECT post_id,COUNT(post_id) AS count,(SELECT post FROM T1 where post_id=T2.post_id) AS postGROUP BY post_idHAVING count >= 10
SELECT post_id,COUNT(post_id) AS count,
(SELECT post FROM T1 where post_id=T2.post_id) AS post
GROUP BY post_id
HAVING count >= 10
wn9m85ua5#
您可以将所选内容分组，然后使用 having ```SELECT posts.*, COUNT(likes.id) as likesCountFROM posts, likesGROUP BY posts.id HAVING (likesCount >= 10)
having
gj3fmq9x6#
具有最佳性能和避免“笛卡尔积”使用的目的 join ，如下所示：
join
SELECT p.post_id, COUNT(l.user_id) AS TotalLikesCountFROM posts AS pJOIN likes AS l ON p.post_id = l.post_idGROUP BY p.post_idHAVING TotalLikesCount >= 10
SELECT p.post_id, COUNT(l.user_id) AS TotalLikesCount
FROM posts AS p
JOIN likes AS l ON p.post_id = l.post_id
GROUP BY p.post_id
HAVING TotalLikesCount >= 10
6条答案
按热度按时间hmae6n7t1#
在表t2中，出现超过10行的post\u id是所需的post，因此您可以选择表t2中至少有10行出现post\u id的post
8ehkhllq2#
您可以使用join选择文章。
du7egjpx3#
4bbkushb4#
你能用一个副选择器吗？
wn9m85ua5#
您可以将所选内容分组，然后使用
having```SELECT posts.*, COUNT(likes.id) as likesCount
FROM posts, likes
GROUP BY posts.id HAVING (likesCount >= 10)
gj3fmq9x6#
具有最佳性能和避免“笛卡尔积”使用的目的
join，如下所示：