如何在底层select-join语句中使用create table语句的列别名？

发布于 2024-12-27 18:20:11 字数 657 浏览 1 评论 0原文

我想要构建的查询遇到了一个小问题。这个想法是基于同一个旧表上的联接构建一个新表，在该旧表上进行某种“昂贵”的计算。

问题

我无法在底层 select 的 where（或 on）子句中使用字段列表的计算结果（别名）。

为什么不再计算一次？

我想避免再次计算结果，因为它是一个 1.5 Mio 条目表，并且我加入了所有内容。 on 子句中还有一些条件，但它并没有真正影响操作数量。因此，我不想每次连接都执行两次。
我现在只想如果可能的话:)

SQL 语句

CREATE TABLE `newTable` AS (
    SELECT (*COMPUTATION*) AS `calculated` 
    FROM oldTable AS t1 
    JOIN oldTable as t2 ON (
        t1.user != t2.user
        AND *other conditions*
    )
    WHERE `calculated` < *some number*
);

我已经考虑过使用函数或其他东西，但我认为它应该可以在一个简单的查询中实现。

原文

I've run into a minor problem with a query I want to build.
The idea is to construct a new table based on a join on the same old table where some kind of "expensive" calculation is done.

The problem

I can not use the calculated result (alias) of the field list in the where (or on) clause of the underlying select.

Why not calculate it again?

I would like to avoid calculating the result again as it is a 1.5 Mio entry table and I join about everything. There are some more conditions in the on clause but it's not really making a difference in number of operations. Therefore I'd rather not do it twice per join.
I just wanna now if it is possible :)

SQL Statement

CREATE TABLE `newTable` AS (
    SELECT (*COMPUTATION*) AS `calculated` 
    FROM oldTable AS t1 
    JOIN oldTable as t2 ON (
        t1.user != t2.user
        AND *other conditions*
    )
    WHERE `calculated` < *some number*
);

I've already thought of using functions or something else, but I think it should be doable in one simple query.

分享到QQ

分享到微博

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

发布评论

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

つ可否回来 2025-01-03 18:20:11

您是否尝试过将内部查询再包装一层？

CREATE TABLE `newTable` AS 
(   select PreQuery.Calculated
       from ( SELECT (*COMPUTATION*) AS `calculated` 
                 FROM oldTable AS t1 
                 JOIN oldTable as t2 ON 
                   ( t1.user != t2.user
                     AND *other conditions* 
                   ) ) PreQuery
       where PreQuery.Calculated < *some number*
);

Have you tried wrapping your inner query one more level??

CREATE TABLE `newTable` AS 
(   select PreQuery.Calculated
       from ( SELECT (*COMPUTATION*) AS `calculated` 
                 FROM oldTable AS t1 
                 JOIN oldTable as t2 ON 
                   ( t1.user != t2.user
                     AND *other conditions* 
                   ) ) PreQuery
       where PreQuery.Calculated < *some number*
);

回复收藏 0 原文

~没有更多了~