如何在where子句中使用别名？

小编典典

如何在where子句中使用别名？

sql

我正在尝试搜索文本和备注的多列，以查找某些我不想看到的特定短语和黑名单短语。

假设下表

stories: 
id, title, author, publisher, content

前任。我想找到所有提到（在任何领域中）“苹果”但黑名单中的“苹果酱”的故事。

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子句中使用别名？我找不到有关此主题的任何文档：

1）这种方法可行吗？
2）替代方法是否意味着我将在每次行迭代中执行多个字符串连接？

阅读 153

2021-04-28

共1个答案

小编典典

我不能在where子句中使用别名。

1. 这种方法可行吗？

当然，将其放在子查询中。

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. 替代方法是否意味着我将在每次行迭代中执行多个字符串连接？

是的，没错，替代方法是重复该表达式。我不会为您使用这种替代方法的代码。

对于您的特定查询，您也可以使用此

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*")

2021-04-28