用Perl将简化的SQL查询解析成SQLite

Question

我正在尝试将一条“简化”的SQL查询转换成可用于XnViews数据库的SQLite查询，这意味着数据库布局对我试图做的事情来说至少是次优的，并且我无法改变任何东西。

例如："(cat_10 and cat_12) and (cat_5 or cat_7)"。

这应该针对表"t3"使用，该表具有字段"if"（文件ID）和"ic"（类别ID）。

记录看起来像这样：

if, ic
7, 10
7, 12
7, 4
9, 10
9, 12
9, 5
10, 10
10, 12
10, 7

简化后的查询应该只选择文件9和10，因为文件7虽然有所需的类别10和12，但没有5或7。

实际问题现在是构建可怕的查询语句，因为我已经花了几个小时才让两个类别之间的AND工作。

SELECT if FROM t3 WHERE ic IN (10, 12) GROUP BY if HAVING count(if) = 2

这个语句给出了所有包含类别10和12的文件ID，但我不知道如何将其与剩余的“并且（cat_5或cat_7）”组合起来。

当我计划这些简化的SQL语句时（通过HTML和JS构建的单击生成器），我打算将“cat_5”替换为“t3.ic = 5”，并保留其余部分。

当然，我没有预料到它不起作用，因为WHERE检查整个条目，ic = 5 AND ic = 7不能同时存在。这几乎破坏了一切。

所以我想知道是否有人知道如何将这些简单的查询转换为实际工作的查询，同时记住它可能不仅限于（x和y）对。

编辑：我已经解决了我给出的示例，至少我认为是这样：

SELECT if FROM t3 WHERE ic IN (10, 12) GROUP BY if HAVING count(if) = 2
INTERSECT 
SELECT if FROM t3 WHERE ic IN (5, 7) GROUP BY if

但现在的主要问题是以正确的顺序解决括号内的内容。

编辑2：我想试着用group_concat()将类别分组到一个字段中，然后我应该能够简单地使用cats LIKE“ ”来将它们组合成小块，然后只需添加括号就可以了。强调一下“应该”。

- BloodyRain2k

Answer 1

您的原始查询并没有达到预期的目的。使用WHERE ic IN (10, 12) GROUP BY if HAVING count(if) = 2，即使您有两个值为10的ic但是没有12，也会产生正确的结果。这与您对所需结果的文本描述不符。这就需要一个内部查询来获取12和10的结果。您可以在我下面提供的fiddle链接中测试您的查询是否失败。

有点棘手，但这是我如何直接解释它的方式。

SELECT DISTINCT ifc
FROM   t3
WHERE  ifc IN (
               SELECT   ifc
               FROM     t3 
               WHERE    ic = 10  
               GROUP BY ifc
               HAVING   COUNT(*) > 0             

               INTERSECT

               SELECT   ifc
               FROM     t3 
               WHERE    ic = 12
               GROUP BY ifc
               HAVING   COUNT(*) > 0
              )            
AND ic IN (5, 7)

试一下

我没有加入任何优化，你可以尝试自己的。这个fiddle链接是Postgres的，但这应该可以工作（我的浏览器无法使用SQLite :(）。

编辑： CL.指出了一个有趣的事情，关于不必在内部查询中包含HAVING子句是正确的。我正在用SQL术语解释OP的要求，以便清晰明了地表达，而没有考虑任何优化。

这是一个更好的查询：

SELECT DISTINCT ifc
FROM   t3
WHERE  ifc IN (
               SELECT   ifc
               FROM     t3 
               WHERE    ic = 10            

               INTERSECT

               SELECT   ifc
               FROM     t3 
               WHERE    ic = 12
              )            
AND ic IN (5, 7)

- nawfal

---

与我昨晚解决的问题不同：SELECT if FROM t3 WHERE ic IN (10, 12) GROUP BY if HAVING count(if) = 2 INTERSECT SELECT if FROM t3 WHERE ic IN (5, 7)现在我正在尝试弄清楚如果我能正确解析（）的顺序，哪个更容易使用。我还考虑稍微改变简单语法，通过执行（[AND | OR] cat_1 cat_2 cat_3）来将（）限制为AND或OR，但是（）的解析问题仍然存在。 - BloodyRain2k

@CL。非常好，+1，我错过了它。谢谢指出，我会编辑我的答案 :) 正如我所说，我是根据OP的要求将其解释为SQL，而不是优化查询。 - nawfal

@BloodyRain2k，您发布的查询有误，请查看我的回答中对其的评论。 - nawfal

Answer 2

好的，出乎意料地，我按照最初的计划让它工作了。

SELECT Folders.Pathname || Images.Filename AS File FROM Images
JOIN Folders ON Images.FolderID = Folders.FolderID
LEFT JOIN (
    SELECT f, Cats, t4.if AS Tagged FROM t2
    JOIN (
        SELECT if, ' ' || group_concat(ic,' ') || ' ' AS Cats FROM t3 GROUP BY if
    ) st3 ON t2.i = st3.if
    LEFT JOIN t4 ON t2.i = t4.if
) st2 ON File = st2.f
$selectWhereImage $sqlqry
ORDER BY ModifiedDate $order LIMIT $offset, $limit

我知道这是一个非常复杂的查询，但它结合了我需要的所有内容（类别ID、标记与否、评分、颜色），可以按日期排序并返回完整文件路径。

可能这不是最好的方法，但如果有人找到更好的方法，可以简单地替换像“cat_5”这样的占位符，同时保留括号和运算符，那么我会听取意见的 :D

哦，$selectWhereImage只包含一个更长的WHERE，用于限制文件以图像格式结尾，$sqlqry是上面重新调整过的东西，“cat_5”将变成“cats LIKE '% 5 %'”，由于左右两侧的额外空格，我可以匹配任何数字而不会在“10”中找到“1”，因为“ 1 ”不在“ 10 ”中:D

- BloodyRain2k

---

请发布一个回答，解决你所提出的问题。SO上没有人对阅读你的代码库感兴趣。 - nawfal

Answer 3

一种取巧的方法，更简单且我相信更快的方法是：

SELECT DISTINCT ifc
FROM   t3
WHERE  ifc IN (
               SELECT   ifc
               FROM     t3 
               WHERE    ic = 10
              ) 
   AND ifc IN (
               SELECT   ifc
               FROM     t3 
               WHERE    ic = 12
              )               
AND ic IN (5, 7)

- nawfal

Answer 4

如果你必须像你所做的那样使用交集，那么你应该更改错误的上层查询。由于你必须确保每个if都有10和12作为ic，所以你不能没有两个单独的查询。类似这样：

SELECT ifc
FROM   t3
WHERE  ifc IN (
               SELECT   ifc
               FROM     t3 
               WHERE    ic = 10
              ) 
   AND ifc IN (
               SELECT   ifc
               FROM     t3 
               WHERE    ic = 12
              )

INTERSECT

SELECT ifc FROM t3 WHERE ic IN (5, 7)

INTERSECT在此处处理了分组操作，因此您无需显式添加。但是，与我的其他查询相比，这种方法可能不够高效。如果您想避免使用子查询，可以使用JOIN：

SELECT DISTINCT t.ifc
FROM   t3 AS t
JOIN   t3 AS v ON v.ifc = t.ifc
JOIN   t3 AS p ON p.ifc = t.ifc
WHERE  v.ic = 10 AND p.ic = 12 AND t.ic IN (5, 7)

第二个的优点是它可以在不支持像MySQL这样的INTERSECT操作的数据库上工作。

- nawfal