sql - 在CASE语句中重用SELECT语句的结果sqlite

Question

我想创建一个SQL语句（使用sqllite ..因此我不能使用函数，IF语句等。），该语句可重用select语句的结果。这就是当前的样子

INSERT INTO search_email(many_fields, threadid) VALUES ('many_fields',
    CASE 
    WHEN
            (
                (SELECT COUNT(tableX.threadid) %threadIDquery% 
                ) > 0
            )
    THEN

                (SELECT tableX.threadid %threadIDquery% LIMIT 1)

    ELSE 
            0
    END
)

我想重用首次选择的结果，而不必执行另一个（几乎相同）选择语句。

更新：对于那些想知道我要做什么的人..这是查询的完整版本：

INSERT INTO search_email(meta, subject, body, sender, tos, ccs, folder, threadid) VALUES ('meta1','subject1','body1','sender1', 'tos1',' ccs1','folder1',
    CASE 
    WHEN
            (
                (SELECT COUNT(search_email.threadID) FROM search_email 
                                                            WHERE search_email.subject MATCH '%query%'  AND 
                                                            (
                                                                (search_email.sender = '%sender' AND search_email.tos = '%receiver%')
                                                                OR 
                                                                (search_email.sender = '%receiver%' AND search_email.tos = '%sender%')
                                                            )
                ) > 0
            )
    THEN

            (SELECT search_email.threadID FROM search_email 
                                                        WHERE search_email.subject MATCH '%query%'  AND 
                                                        (
                                                            (search_email.sender = '%sender%' AND search_email.tos = '%receiver%')
                                                            OR 
                                                            (search_email.sender = '%receiver%' AND search_email.tos = '%sender%')
                                                        )  LIMIT 1
            )

    ELSE 
            //generate new thread ID
    END
)

基本上，我试图找出传入的电子邮件是否已经存在一个电子邮件线程..因此，我检查主题是否匹配，如果匹配，我会检查电子邮件的发送者和接收者是否匹配（在任一方向上） ..如果电子邮件线程存在，我只是插入相同的电子邮件threadID，否则我会生成一个新的threadID

更新2：为澄清起见，我正在寻找一种方法来保存sqllite编译器以免两次进行相同的搜索..而不是简单地节省键入（或使其更具可读性等）

更新3：我想知道是否有一种方法可以使该语句返回从dbase中检索到的线程ID，而不是从dbase检索到的线程ID。您可以找到答案 here

Answer 1

这是构造查询的另一种方法：

INSERT INTO search_email(meta, subject, body, sender, tos, ccs, folder, threadid)
    SELECT 'meta1', 'subject1', 'body1', 'sender1', 'tos1', 'ccs1', 'folder1',
            coalesce((SELECT search_email.threadID
                      FROM search_email 
                      WHERE search_email.subject MATCH '%query%' AND 
                            ((search_email.sender = '%sender%' AND search_email.tos = '%receiver%') OR
                             (search_email.sender = '%receiver%' AND search_email.tos = '%sender%')
                            )
                      LIMIT 1
                     ),
                     <generate new thread id here>
                    )

这使用的是 select而不是 values。它获取与条件匹配的线程ID；如果不匹配，则为NULL。当第一个为NULL时，将运行 coalesce的第二个子句。您可以在那里生成新的ID。

我确实对这种方法有疑问。对我来说，似乎应该有一个管理线程的Thread表。在此表中，ThreadId应该是一个自动递增的ID。然后，电子邮件表可以引用此ID。换句话说，我认为数据模型需要更详细地考虑。

以下查询将无法查询工作，但可以将线程移至子查询：

INSERT INTO search_email(meta, subject, body, sender, tos, ccs, folder, threadid)
    SELECT 'meta1', 'subject1', 'body1', 'sender1', 'tos1', 'ccs1', 'folder1',
            coalesce(t.threadID,
                     <generate new thread id here>
                    )
    from (SELECT search_email.threadID
          FROM search_email 
          WHERE search_email.subject MATCH '%query%' AND 
                ((search_email.sender = '%sender%' AND search_email.tos = '%receiver%') OR
                 (search_email.sender = '%receiver%' AND search_email.tos = '%sender%')
                )
          LIMIT 1
         ) t

之所以不起作用，是因为 from子句将不返回任何行，而不返回1个具有NULL值的行。因此，要获得所需的内容，可以使用：

    from (SELECT search_email.threadID
          FROM search_email 
          WHERE search_email.subject MATCH '%query%' AND 
                ((search_email.sender = '%sender%' AND search_email.tos = '%receiver%') OR
                 (search_email.sender = '%receiver%' AND search_email.tos = '%sender%')
                )
          union all
          select NULL
          order by (case when threadId is not null then 1 else 0 end) desc
          LIMIT 1
         ) t

这样可以确保在没有线程时返回NULL值。