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

发布于 2024-10-15 17:27:43 字数 539 浏览 5 评论 0原文

我试图在多列文本和备忘录中搜索我不想看到的某些短语和黑名单短语。

假设下表如下

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）替代方案是否意味着我将在每行迭代上执行多个字符串串联？

原文

I'm trying to search multiple columns of text and memos for certain phrases and blacklist phrases I don't want to see.

Assume the following table

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

How do I use my alias in the where clause? I can't find any documentation on the subject:

1) Is this approach possible?
2) Wouldn't the alternative mean that I'd be performing multiple string concatenations on every row iteration?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拿命拼未来 2024-10-22 17:27:43

我无法在 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*")

I can't use my alias in the where clause.

1. Is this approach possible?

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

回复收藏 0 原文

给不了的爱 2024-10-22 17:27:43

唯一的问题是，无论
我尝试做的事情是，我无法使用我的别名
在 where 子句中。我找不到任何
有关该主题的文档

是的，Access/Jet/ACE“SQL”语言的文档严重缺乏，而且可用的少量文档都存在令人震惊的错误。

以下是一些有关 SQL 的一般文档：

“Joe Celko 的集合思维：SQL 中的辅助表、临时表和虚拟表”，第 12 章，第 235-237 页：

以下是 SELECT 在 SQL 中的工作原理...
从 FROM 子句开始...转到
WHERE 子句...转到
可选的GROUP BY子句...转到
可选的 HAVING 子句...转到
SELECT 子句并构造
列表中的表达式。这意味着
标量子查询、函数
SELECT 中的调用和表达式
在所有其他子句之后完成
完成了。 AS 运算符还可以
为表达式中的表达式命名
选择列表。这些新名字来了
一下子就存在了，但是之后
WHERE 子句、GROUP BY 子句
和 HAVING 子句已
被处决；你不能在
SELECT 列表或 WHERE 子句
因此。

我认为这解释了为什么不能在 Access（Jet、ACE 等）的 WHERE 子句中使用 as 子句（“列别名”）。

也就是说，请注意 Access 与 SQL 不兼容，因为它允许您在 SELECT 子句中以从左到右的方向使用 as 子句，例如，这是在 Access SQL 中合法（但在标准 SQL 中非法）：

SELECT 2 AS a, 2 AS b, a + b AS c
  FROM tblMyTable

The only problem is that, no matter
what I try to do, I can't use my alias
in the where clause. I can't find any
documentation on the subject

Yes, the documentation for Access/Jet/ACE 'SQL' language is severely lacking and the little that is available has shocking errors.

Here's some documentation about SQL generally:

"Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL", ch12, pp235-237:

Here is how a SELECT works in SQL...
Start in the FROM clause... Go to
the WHERE clause... Go to the
optional GROUP BY clause... Go to
the optional HAVING clause... Go to
the SELECT clause and construct the
expressions in the list. This means
that the scalar subqueries, function
calls and expressions in the SELECT
are done after all the other clauses
are done. The AS operator can also
give names to expressions in the
SELECT list. These new names come
into existence all at once, but after
the WHERE clause, GROUP BY clause
and HAVING clause have been
executed; you cannot use them in the
SELECT list or the WHERE clause
for that reason.

I think this explains why you cannot use an as clause ("column alias") in the WHERE clause in Access (Jet, ACE, whatever).

That said, note that Access is non-compliant with SQL in that it allows you to use an as clause in the SELECT clause in left-to-right direction e.g. this is legal in Access SQL (but illegal in Standard SQL):

SELECT 2 AS a, 2 AS b, a + b AS c
  FROM tblMyTable

回复收藏 0 原文

冷默言语 2024-10-22 17:27:43

使用子查询：

Select id,allMyText 
from
(SELECT stories.id, 
[stories.title] & " " & [stories.author] & " " 
& [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories ) as w
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"))

Use Subqueries:

Select id,allMyText 
from
(SELECT stories.id, 
[stories.title] & " " & [stories.author] & " " 
& [stories.publisher] & " " & [stories.memo] AS allMyText
FROM stories ) as w
WHERE ((([allMyText]) Like "*apples*" And ([allMyText]) Not Like "*applesauce*"))

回复收藏 0 原文

~没有更多了~