mysql - 如何在 MySQL 中请求包含某些项目的列表

Question

在我正在开发的应用程序中，用户必须设置参数来定义他将获得的最终产品。

我的表格如下所示:

Categories
-------------
Id   Name
1     Material
2     Color
3     Shape


Parameters
-------------
Id   CategoryId  Name
1     1           Wood
2     1           Plastic
3     1           Metal
4     2           Red
5     2           Green
6     2           Blue
7     3           Round
8     3           Square
9     3           Triangle

Combinations
-------------
Id
1
2
...

ParametersCombinations
----------------------
CombinationId  ParameterId
1               1
1               4
1               7
2               1
2               5
2               7

现在只有一些参数组合可供用户使用。在我的例子中，他可以获得一个红色的圆形木头或一个绿色的圆形木头，但不能得到蓝色的，因为我无法生产它。

假设用户选择了木材和圆形参数。我如何请求知道只有红色和绿色可用，以便我可以为他禁用蓝色选项？或者有更好的方法来建模我的数据库吗？

Answer 1

假设您按以下格式提供所选参数 ID

    // I call this a **parameterList** for convenience sake.
    (1,7) // this is parameter id 1 and id 7. 

我还假设您正在使用某种脚本语言来帮助您开发应用程序。就像 ruby​​ 或 php 一样。

我还假设您希望尽可能避免在存储过程或 MySQL 查询中放入尽可能多的逻辑。

另一个假设是您正在使用快速应用程序 MVC 框架之一，例如 Rails、Symfony 或 CakePHP。

你的逻辑是:

1. 查找包含 parameterList 中所有参数的所有组合，并将这些找到的组合放入名为 relevantCombinations 的列表中
2. 查找relevantCombinations列表中至少包含 1 个组合的所有parameters_combinations。仅检索唯一的参数值。

前两个步骤可以使用简单的 Model::find 方法和我上面描述的框架中的 forloop 来解决。

如果您不使用框架，使用原始脚本语言也很酷。

如果您在 MySQL 查询中需要它们，这里有一些可能的查询。请注意，这些不一定是最好的查询。

第一个是

SELECT * FROM (
  SELECT `PossibleList`.`CombinationId`, COUNT(`PossibleList`.`CombinationId`) as number
    FROM (
      SELECT `CombinationId` FROM `ParametersCombinations` 
        WHERE `ParameterId` IN (1, 7)
    ) `PossibleList` GROUP BY `PossibleList`.`CombinationId`
) `PossibleGroupedList` WHERE `number` = 2;
-- note that the (1, 7) and the number 2 needs to be supplied by your app. 
-- 2 refers to the number of parameters supplied. 
-- In this case you supplied 1 and 7 therefore 2.

要确认，请查看 http://sqlfiddle.com/#!2/16831/3 。

请注意，我故意使用组合 3，其中只有参数 1，但没有参数 7。因此，查询不会返回 3，而只返回 1 和 2。请随意调整第一行中的星号 *。

第二个是

SELECT DISTINCT(`ParameterID`) 
FROM `ParametersCombinations`
WHERE `CombinationId` IN (1, 2);
-- note that (1, 2) is the result we expect from the first step. 
-- the one we call relevantCombinations

要确认，请查看 http://sqlfiddle.com/#!2/16831/5

我不建议成为一个受虐狂并尝试通过单个查询获得答案。

我也不建议使用我提供的 MySQL 查询。它不那么受虐。但我不推荐这种方式，这已经足够受虐了。

由于你没有指出除mysql以外的任何标签，我怀疑你对mysql更强。因此我的答案包含mysql。

我最强烈的建议是我的第一个建议。充分利用已经建立的框架，把你的逻辑放在业务逻辑层。不在数据层。即使您不使用框架而只使用原始 php 和 ruby​​，这仍然是比 MySQL 更好的放置逻辑的地方。

我看到 T 在单个 MySQL 查询中给出了答案，但我可以告诉你，他只考虑 1 个参数。

查看这部分:

WHERE ParameterId = 7  -- 7 is the selected parameter

您可以使用 forloop 和附加 OR 子句通过一些技巧来调整他/她的答案。

再说一遍，我不建议在构建应用程序的大局中这样做。

我还用 http://sqlfiddle.com/#!2/2eda4/2 测试了他/她的答案。可能存在 1 或 2 个小错误。

总而言之，我的建议按强度降序排列:

1. 使用 Rails 或 CakePHP 等框架以及伪代码步骤 1 和 2 以及所需的任意数量的 find。 (最强)
2. 使用原始脚本语言和伪代码步骤 1 和 2，以及您需要的任意数量的简单查询。
3. 使用我创建的原始 MySQL 查询。 (最弱)

附注我在查询中省略了有关如何获取参数名称的部分。但鉴于您可以从我的答案中获取 ParameterID，我认为这很简单。我还遗漏了您可能需要如何删除已选择的参数 (1, 7)。再说一次，这对你来说应该是微不足道的。