在 CASE 语句 sqllite 中重用 SELECT 语句的结果 [英] reusing result of SELECT statement within a CASE statement sqllite
问题描述
我想创建一个重用 select 语句结果的 SQL 语句(使用 sqllite .. 所以我不能使用函数、IF 语句等..).这是它目前的样子
I want to create an SQL statement (using sqllite.. so I can't use functions, IF statements etc..) that reuses the result of a select statement.. this is what it currently looks like
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
)
我想重用第一个 select 的结果，而不必再做另一个(几乎相同的)select 语句.
I want to reuse the result of first select instead of having to make another (almost identical) select statement.
更新:对于那些想知道我正在尝试做什么的人..这是查询的完整版本:
update: for those wondering what i'm trying to do.. here is the full version of the query:
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
)
基本上，我试图找出传入电子邮件的电子邮件线程是否已经存在..所以我检查主题是否匹配，如果匹配，我检查电子邮件的发件人和收件人是否匹配(在任一方向)..如果电子邮件线程存在，我只需插入相同的电子邮件线程 ID，否则我生成一个新的线程 ID
basically i'm trying to find out if an email thread already exists for an incoming email.. so i check if the subject matches and if it does, i check to see if the sender and receiver of the email match (in either direction).. if the email thread exists i just insert the same email threadID, else i generate a new threadID
更新 2:只是为了澄清，我正在寻找一种方法来避免 sqllite 编译器进行两次相同的搜索......而不是简单地节省输入(或使其更具可读性等))
update 2:just to clarify, I'm looking for a way to save the sqllite compiler from making the same search twice.. rather than simply saving on typing (or making it more readable etc)
更新 3: 我想知道如果 threadID 是从 dbase 中检索的，而不是生成的，则此语句是否有办法返回生成的 threadID.您可以找到答案
update 3: i was wondering if there was a way for this statement to return the generated threadID iff the threadID was retrieved from the dbase, rather than generated.. you can find the answer here
推荐答案
这是构建查询的另一种方法:
This is an alternative way of structuring the query:
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.coalesce 的第二个子句然后在第一个为 NULL 时运行.您可以在那里生成新 ID.
This is using a select instead of values. It gets the thread id that matches the conditions, or NULL if none match. The second clause of the coalesce is then run when the first is NULL. You can generate the new id there.
我对这种方法有问题.在我看来，您似乎应该有一个管理线程的 Thread 表.ThreadId 应该是该表中自动递增的 id.emails 表然后可以引用这个 id.换句话说，我认为需要更详细地考虑数据模型.
I do have a problem with this approach. To me, it seems that you should have a Thread table that manages the threads. The ThreadId should be an autoincremented id in this table. The emails table can then reference this id. In other words, I think the data model needs to be thought out in more detail.
以下查询将不起作用，但它提供了将线程移动到子查询的想法:
The following query will not query work, but it gives the idea of moving the thread to the subquery:
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 值.所以，为了得到你想要的，你可以使用:
The reason it will not work is because the from clause will return no rows rather than 1 row with a NULL value. So, to get what you want, you can use:
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 值.
This ensures that a NULL value is returned when there is no thread.
这篇关于在 CASE 语句 sqllite 中重用 SELECT 语句的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
