gpt4 book ai didi

mysql - 如何向具有 GROUP BY 和 HAVING 子句的查询再添加一个连接?

转载 作者:行者123 更新时间:2023-12-01 00:35:51 24 4
gpt4 key购买 nike

这是我的查询:

SELECT p.*
FROM posts p
INNER JOIN tags_pivot tp
ON p.id = tp.post_id
INNER JOIN tags t
ON t.id = tp.tag_id AND t.name IN ('mysql', 'php')
GROUP BY p.id
HAVING COUNT(*) = 2;

它选择所有带有 [mysql][php] 标签的帖子。我还有一个名为 "Category" 的存在。它类似于“标签”,只是每个帖子唯一

无论如何,我需要将其附加到上面的查询中:

INNER JOIN category_pivote cp
ON p.id = cp.post_id
INNER JOIN categories c
ON c.id = cp.category_id AND c.name = "technology"

如何组合这两个查询?

最佳答案

一种使用join的方法是:

SELECT p.*
FROM posts p INNER JOIN
category_pivote cp
ON p.id = cp.post_id INNER JOIN
categories c
ON c.id = cp.category_id AND c.name = 'technology' INNER JOIN
tags_pivot tp
ON p.id = tp.post_id INNER JOIN
tags t
ON t.id = tp.tag_id AND t.name IN ('mysql', 'php')
GROUP BY p.id
HAVING COUNT(DISTINCT t.name) = 2;

我添加了 DISTINCT 以防多个类别匹配(尽管这似乎不太可能)。您还可以使用:

HAVING GROUP_CONCAT(DISTINCT t.name ORDER BY t.name) = 'mysql,php';

关于mysql - 如何向具有 GROUP BY 和 HAVING 子句的查询再添加一个连接?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50911524/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com