在关系表中显示至少发生一次的行？

发布于 2025-01-29 11:20:38 字数 454 浏览 3 评论 0原文

我有下表模式：

user (id, name, alias, password, active, mail, age)
comment(id, news(FK), user(FK), text, date)
new_cat(news(FK), category(FK))

我正在尝试选择所有对每个类别的新 的新用户。

这是我在没有任何成功的情况下尝试的事情：

SELECT * FROM user AS u, comment AS c
WHERE u.id = c.user AND c.news IN (SELECT news FROM new_cat);

我相信这不是正确的迭代并检查每个类别，并且只检查条件是否仅适用于一个类别。

我该如何正确执行此操作？

原文

I have the following table schema:

user (id, name, alias, password, active, mail, age)
comment(id, news(FK), user(FK), text, date)
new_cat(news(FK), category(FK))

I'm trying to select all the users who have commented on AT LEAST one new of EVERY category.

This is what I'm trying without any success:

SELECT * FROM user AS u, comment AS c
WHERE u.id = c.user AND c.news IN (SELECT news FROM new_cat);

I believe this is not iterating properly and checking for EVERY category and just checks if the condition applies just on one category.

How can I properly do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

归属感 2025-02-05 11:20:38

加入表，按用户组组，并在子句中设置条件。

如果有一个类别表，您可以在其中存储所有类别：

SELECT u.*
FROM user u
INNER JOIN comment c ON c.user = u.id
INNER JOIN new_cat n ON n.news = c.news
GROUP BY u.id
HAVING COUNT(DISTINCT n.category) = (SELECT COUNT(*) FROM category);

Join the tables, group by user and set the condition in the HAVING clause.

If there is a category table where you store all the categories:

SELECT u.*
FROM user u
INNER JOIN comment c ON c.user = u.id
INNER JOIN new_cat n ON n.news = c.news
GROUP BY u.id
HAVING COUNT(DISTINCT n.category) = (SELECT COUNT(*) FROM category);

回复收藏 0 原文

~没有更多了~