MySQL-选择具有覆盖一组值的组 [英] MySQL - select groups having values covering a set

问题描述

我有两个表:

CREATE TABLE user (
    user_id INT PRIMARY KEY,
    ...
);
CREATE TABLE action (
    action_id INT PRIMARY KEY,
    user_id INT FOREIGN KEY REFERENCES user(user_id),
    action_type TINYINT,
    ...
);

每次用户执行特定操作时，都会在action表中插入一行.

Each time a user performs a particular action, a row is inserted into the action table.

现在，我想查找执行了所有某些操作集的所有用户.像这样:

Now I want to find all users who have performed all of some set of actions. Something like this:

SELECT user_id
FROM user, action
HAVING SET(action_type) INTERSECT (0,3,4,5) = (0,3,4,5);

但是，当然，我刚刚完成了最后一行.在SQL中有什么好方法吗?

But of course I've just made up that last line. Is there a good way of doing this in SQL?

推荐答案

这是组内查询"的示例.我喜欢使用group by和having解决它们，因为这是非常灵活的:

This is an example of a set-within-sets query. I like to solve them using group by and having, because this is a very flexible:

SELECT user_id
FROM action a
WHERE action_type IN (0, 3, 4, 5)
GROUP BY user_id
HAVING COUNT(DISTINCT action_type) = 4;

这篇关于MySQL-选择具有覆盖一组值的组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！