问题描述
我正在尝试在多列文本和备忘录中搜索我不想看到的某些短语和黑名单短语.
I'm trying to search multiple columns of text and memos for certain phrases and blacklist phrases I don't want to see.
假设下表
stories:
id, title, author, publisher, content
例如.我想找到所有提到(在任何领域)苹果"但将苹果酱"列入黑名单的故事.
Ex. I want to find all stories that mention (in any field) 'apples' but blacklist 'applesauce'.
SELECT stories.id, [stories.title] & " " & [stories.author] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"));
如何在 where 子句中使用我的别名?我找不到有关该主题的任何文档:
How do I use my alias in the where clause? I can't find any documentation on the subject:
1) 这种方法可行吗?
2) 替代方案是否意味着我将在每行迭代中执行多个字符串连接?
1) Is this approach possible?
2) Wouldn't the alternative mean that I'd be performing multiple string concatenations on every row iteration?
推荐答案
1. 这种方法可行吗?
当然，把它放在子查询中.
Sure, put it in a subquery.
SELECT *
FROM
(
SELECT stories.id, [stories.title] & " " & [stories.author] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
) AS SUBQ
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"));
2. 替代方案是否意味着我将在每行迭代中执行多个字符串连接?
2. Wouldn't the alternative mean that I'd be performing multiple string concatenations on every row iteration?
是的，没错，替代方法是重复表达式.我不会让你厌烦这个替代方案的代码.
Yes that is right, the alternative is to repeat the expression. I won't bore you with the code for this alternative.
对于您的特定查询，您也可以使用此
For your particular query, you can also use this
SELECT stories.id, [stories.title] & " " & [stories.author] & " " & [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories
WHERE ([stories.title] Like "*apples*" OR [stories.author] Like "*apples*"
OR [stories.publisher] Like "*apples*" OR [stories.memo] Like "*apples*")
AND NOT ([stories.title] Like "*applesauce*" OR [stories.author] Like "*applesauce*"
OR [stories.publisher] Like "*applesauce*" OR [stories.memo] Like "*applesauce*")
这篇关于如何在 where 子句中使用我的别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！