时间戳

首页 > 解决方案 > 为逻辑句动态生成 SQL

sql - 为逻辑句动态生成 SQL

问题描述

超级棘手的问题。我有一个客户想要true/false基于代表rule.

例如，如果规则是字符串："56 AND 78"，那么这意味着“true如果Table A有 ID56和 ID的行，则返回78。请注意，这并不意味着同一行有 ID 56，78因为这是不可能的。

现在，我可以继续为简单的情况动态生成一些伪 SQL，如下所示：

RETURN QUERY (SELECT EXISTS(SELECT 1
                            FROM table_a
                            WHERE id = 56)
              AND
              SELECT EXISTS(SELECT 1
                            FROM table_a
                            WHERE id = 78))

其中WHERE子句直接从rule. 这满足了简单的句子。

但是，规则可能会变得更加复杂。
例如"(43 OR 44 OR 47) AND (2182 OR 2179 OR 2183)"，它用英语翻译为“表 A 是否有一行具有来自前三个 ID 之一的（任何）ID 和来自后三个 ID 的（任何）一个？”

sentence随着 SQL变得越来越复杂，我正在努力寻找一种动态生成 SQL 的方法。

建议将不胜感激！

标签： sqldynamic-programmingdynamic-sqlpostgresql-11

解决方案

It sounds as if your sentences follow the following rules:

  1. A number is a small integer.
  2. A term is either a single number, or a set of terms either with AND between each pair, or OR between each pair.
  3. Terms must be enclosed by parentheses in order to be combined with more terms.

If this is the case, then I think you can simply:

  • replace each number with (SELECT EXISTS(SELECT 1 FROM table_a WHERE id = *number*))
  • Leave any parentheses, ANDs and ORs where they are.
  • Enclose the string in one more set of parentheses and prefix RETURN QUERY.

This certainly won't be the simplest SQL that expresses your sentence but I think it will be equivalent.

If you want to optimize more, you can switch (5 or 6 or 7) with (SELECT 1 FROM table_a WHERE id IN (5,6,7)). But that shouldn't be necessary for a correct SQL translation.

推荐阅读