MYSQL子集操作 [英] MYSQL subset operation

问题描述

有没有办法实现以下目标:

Is there a way to achieve something like:

SELECT *
FROM tb_values
WHERE (value1, value2, value3) SUBSET_OF
    ( SELECT value
      FROM tb_value
      WHERE isgoodvalue = true
    )

更多信息: 我有一张桌子，叫做项目.每个项目都有标签.一个标签可以被多个项目共享.有一个名为projectTagMap的映射表.现在，用户可以使用标签来过滤项目.使用UI上的复选框选择标签.因此，用户选择几个标签来过滤项目.应该从包含用户选择的所有标签的项目表中选择项目.

More information: I have a table called projects. Each project has tags. A tag can be shared by multiple projects. There is a mapping table called projectTagMap. Now users use tags to filter the projects. Tags are selected using checkboxes on the UI. So a user selects several tags to filter the projects. Am supposed to select the projects from projects table that contains all the tags selected by the user.

推荐答案

从您的伪代码中，我猜您想检查一个(动态)值列表是否为SELECT提供的另一个列表的子集.如果是，则将显示整个表格.如果没有，则不会显示任何行.

From your pseudo code I guess that you want to check if a (dynamic) list of values is a subset of another list provided by a SELECT. If yes, then a whole table will be shown. If not, no rows will be shown.

这是实现该目标的方法:

Here's how to achieve that:

SELECT *
FROM tb_values
WHERE 
    ( SELECT COUNT(DISTINCT value)
      FROM tb_value
      WHERE isgoodvalue = true
        AND value IN (value1, value2, value3)
    ) = 3

---

更新，在OP进行了解释之后:

---

UPDATED after OP's explanation:

SELECT *
FROM project
  JOIN 
    ( SELECT projectid
      FROM projectTagMap
      WHERE isgoodvalue = true
        AND tag IN (tag1, tag2, tag3)
      GROUP BY projectid
      HAVING COUNT(*) = 3
    ) AS ok
    ON ok.projectid = project.id

这篇关于MYSQL子集操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！