创建复杂的IF、OR、NOT和AND SQL查询（MySQL）

Question

创建复杂的IF、OR、NOT和AND SQL查询（MySQL）

phpmysqlsqlcodeigniter

3

非常抱歉如果这是一篇重复的文章 - 我已经尝试搜索了，但我可能不知道我试图实现的正确术语 - 请随意更正我！

背景

我有一个基于PHP的应用程序（Codeigniter，但我在这部分使用普通的SQL语言），它具有一个MySQL数据库，其中包括2个表 -“联系人”和“订单”。

为简单起见，让我们假设：

'contact'有3列：Id，FirstName，LastName
'order'有4列：Id，ContactId，ItemBought，ItemValidDate
'order'表中的一行示例：22, 11，Adult Membership，2012/13
Id是两个表的主键，ContactId是“contact”的外键，ItemBought和ItemValidDate都是简单的varchar（我们存储的是“季节”，而不是日期 - 我知道，这不是理想的，但这是客户所需的）

我知道，总有一天，我将不得不扩展为三个表并使用OrderItem表，以允许一个订单具有多个项目，因此我希望找到一个可建立的解决方案。但目前，我甚至不了解基础知识，所以我只保留了两个表

问题

我想创建一个搜索页面，允许用户根据许多不同的标准查找记录子集。

查看搜索页面的屏幕截图

此表单以如下方式提交为一组标准：

[order_type_operator] => Array
    (
        [0] => equal
        [1] => equalor
        [2] => notequal
    )

[order_type] => Array
    (
        [0] => Adult Membership
        [1] => Adult Membership
        [2] => Adult Membership
    )

[order_expire] => Array
    (
        [0] => 2005/06
        [1] => 2006/07
        [2] => 2010/11
    )

[submit] => Start Search

我遍历这个数组，测试是否提交了值，并构建我的SQL查询。

因此，我希望我已经正确解释了它，以便清楚地表明用户可以使用此表单搜索符合许多不同条件的记录 - 理论上，无限数量的条件 - 以获得符合此标准的联系人列表。

我尝试过的

示例1-简单的WHERE

"查找有一个订单记录为'成年会员'在'2009/10'的联系人记录"
即：SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10')

这个很好用。

示例2-WHERE或WHERE

"查找有一个订单记录为'成年会员'在 '2009/10'" 或 有一个订单记录为'成年会员'在 '2010/10'
即：SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10') OR (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2010/11')

只要用户所请求的每个条件都是OR查询，这个很好用。我假设我可以使用括号和OR构建此查询，无论它有多大？例如，查找2005/06、2006/07、2007/08、2008/09等成年会员的情况，就像上面的SQL一样，只是连接了更多的括号，并用'OR'连接?

示例3-WHERE与WHERE-我卡住了！

"查找有一个订单记录为'成年会员'在'2009/10或2010/11'并且有一个订单记录为'成年会员'在'2012/13'的联系人记录

目前，我一直在尝试使用UNION，但是如果要继续执行更多查询（例如2008年或2009年和2010年的成年会员），这意味着需要执行多个SELECT语句。（也许这就是答案？）

例如：SELECT * FROM contact JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2009/10') OR (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2010/11')

UNION

SELECT * FROM contact
JOIN order ON contact.Id = order.ContactId WHERE (order.ItemBought = 'Adult Membership' AND order.ItemValidDate = '2012/13)`

示例4- 但没有记录......让我震惊

"查找在“2009/10”年有一个“成人会员”的订单记录并且在“2010/10”年有一个“成人会员”的订单记录，但是在“2007/08”年没有“赞助”的订单记录的联系人记录。

我考虑过运行这些查询，将结果存储在PHP数组中，然后执行IN (*已选择的ID数组*)，但这似乎不是正确使用SQL的方式。

聪明的人们 - 我做错了什么？

非常感谢您提前的帮助。

PS. 我并不要求您为我编写代码！

PPS. 如果您知道任何好的教程，我很乐意跟随它们！

PPPS. 如果这是重复的，请接受我的道歉！

- al_manchester

抱歉，但如果“这个运行正常”的话，有什么问题呢？ - Maxim Kumpan

2

不是答案，但也许可以帮助理解mysql中的“JOIN”。对我很有帮助！SQL-Joins的可视化解释 - ferdynator

抱歉，Maxim - 我表达不清楚。示例1和2按照我的预期工作。示例3和4是我卡住的地方。 - al_manchester

byf-ferdy: 我非常喜欢那篇博客文章！它对我来说真的很有用。但是，如果示例3和4是更大、更复杂的查询的一部分，我就看不出它们如何适合这些帖子了。 - al_manchester

3个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Kickstart · Answer 1

正如ZorleQ所说，它很快就会变得混乱不堪。

对于您的第三个问题，使用子查询连接的可能解决方案如下：

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
INNER JOIN (SELECT DISTINCT ContactId 
            FROM order 
            WHERE (ItemBought = 'Adult Membership' AND ItemValidDate = '2009/10') 
            OR (ItemBought = 'Adult Membership' AND ItemValidDate = '2010/11')) Sub1
ON contact.Id = Sub1.ContactId
INNER JOIN (SELECT DISTINCT ContactId
            FROM order 
            WHERE (ItemBought = 'Adult Membership' AND ItemValidDate = '2012/13')) Sub2
ON contact.Id = Sub2.ContactId

您可以使用普通连接而不是子查询来完成这个操作，如下所示

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
LEFT OUTER JOIN order Sub1
ON contact.Id = Sub1.ContactId AND Sub1.ItemBought = 'Adult Membership' AND Sub1.ItemValidDate = '2009/10'
LEFT OUTER JOIN order Sub2
ON contact.Id = Sub2.ContactId AND Sub2.ItemBought = 'Adult Membership' AND Sub2.ItemValidDate = '2010/11'
INNER JOIN order  Sub3
ON contact.Id = Sub3.ContactId AND Sub3.ItemBought = 'Adult Membership' AND Sub3.ItemValidDate = '2012/13'
WHERE Sub1.ContactId IS NOT NULL OR Sub2 IS NOT NULL

你的第四个问题可以使用LEFT OUTER JOIN来查找购买了2007/08赞助的记录，并且仅返回未找到匹配项的行（即，检查LEFT OUTER JOIN表上的ContactId是否为NULL）。

SELECT contact.*, order.*
FROM contact
INNER JOIN order
ON contact.Id = order.ContactId
INNER JOIN order Sub1
ON contact.Id = Sub1.ContactId AND Sub1.ItemBought = 'Adult Membership' AND Sub1.ItemValidDate = '2009/10'
INNER JOIN order Sub2
ON contact.Id = Sub2.ContactId AND Sub2.ItemBought = 'Adult Membership' AND Sub2.ItemValidDate = '2010/10'
LEFT OUTER JOIN order Sub3
ON contact.Id = Sub3.ContactId AND Sub3.ItemBought = 'Sponsorship' AND Sub3.ItemValidDate = '2007/08'
WHERE Sub3.ContactId IS NULL

- Rodney P. Barbati · Answer 2

对于用户可能或可能不提供多个条件的所有类似问题，我的解决方案如下...（此示例适用于Oracle，但也应该能够在MySQL中完成）...

您传递所有过滤变量，无论它们是null还是填充了值。在这个例子中，我将说我有3个值，用户可能或可能不会填充，作为SELECT的过滤器。

SELECT
   *
FROM
    table
WHERE
    NVL2(InputVariable1, InputVariable1, Column1) = Column1
OR  NVL2(InputVariable2, InputVariable2, Column2) = Column2 
OR  NVL2(InputVariable3, InputVariable3, Column3) = Column3

NVL2 - 这是一个Oracle函数。如果第一个值不为空，则返回第二个值，否则返回第三个值。如果您没有使用Oracle，并且没有等效的NVL2函数，则可以自己编写该函数。

因此，使用上述示例，即使它们为NULL，代码始终将所有三个InputVariables传递到select语句中。通过使用NVL2或等效函数，仅当InputVariable不为空时，比较才在InputVariable和Column之间进行；否则，在Column和Column之间进行比较，这当然始终为真，从而有效地忽略了该过滤变量，这正是您想要的（即空过滤值匹配所有行-即如果用户未指定LastName，则包括所有LastNames）。

此解决方案允许您使用许多过滤变量，而无需事先进行大量处理-每次只需将它们全部传递到SELECT中，无论它们是否为空。

如果您有一组过滤变量（例如，用户通过复选框或某些类似机制启用一组输入值），则可以在CASE语句中执行上述操作。每个CASE应检查给定集合的启用值，并返回评估整个过滤变量集的结果（与上述完全相同）。然后将整个CASE结构的结果与1进行比较，如下所示...

WHERE

CASE [ expression ]

    WHEN enableSet1 
    THEN     NVL2(InputVariable1, InputVariable1, Column1) = Column1
         OR  NVL2(InputVariable2, InputVariable2, Column2) = Column2 
         OR  NVL2(InputVariable3, InputVariable3, Column3) = Column3

   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

END = 1

这能够工作是因为 CASE 结构的值是经过评估的 THEN 块的结果。

这将允许您在单个 SELECT 语句的范围内完成所有或大多数所需的过滤操作 - 再次强调，不需要进行大量预处理来构建 SELECT。

- ZorleQ · Answer 3

我认为你需要退一步，先在纸上试着将你的问题可视化。例如1和2非常简单，但让我们看看示例3。

对于所有条件都是“AND”或“OR”的情况-事情就非常简单了。只需像以前一样进行长时间的WHERE查询即可。然而，当您开始混合它们时，您必须问自己一个严肃的问题：

您如何分割条件？

假设有人选择了这些条件：

并且A
或B
并且C

这会给您的查询带来如此多的排列组合！例如：

（A或B）并且C
A或（B和C）
（A和C）或B

如果再添加一个“OR”，您将获得更多的组合！这会让您陷入猜测该怎么做的境地。更不用说如果涉及NOT会发生什么...

这不是直接回答你的问题，而更多是指向可能的解决方案。上次我们需要做类似的事情时，我们最终将条件分组到块中。

您可以在块内添加条件或添加新的搜索块。将块视为上述示例中的括号。块中的所有内容都是“AND”或“NOT AND”，在块之间，您可以指定“and”或“or”。这样，您立即知道如何构建查询。这在独立应用程序中效果很好。在页面上实现得很好可能有点棘手，但你会明白的。