个人中心
gpt4 book ai didi

Using an Alias in SQL Calculations(在SQL计算中使用别名)

转载 作者：bug小助手 更新时间：2023-10-24 20:20:48 32 4
gpt4 key购买 nike



Why won't this query work?

为什么此查询不起作用？



SELECT 10 AS my_num, my_num*5 AS another_number
FROM table


In this example, I'm trying to use the my_num alias in other calculations. This results in unknown column "my_num"

在本例中，我尝试在其他计算中使用my_num别名。这将导致未知列“my_num”



This is a simplified version of what I am trying to do, but basically I would like to use an alias to make other calculations. My calculations are much more complicated and thats why it would be nice to alias it since I repeat it several times different ways.

这是我尝试做的事情的简化版本，但基本上我想使用别名来进行其他计算。我的计算要复杂得多，这就是为什么它会很好的别名，因为我重复了几次不同的方式。


更多回答
优秀答案推荐

Simply wrap your reused alias with (SELECT alias) :

只需用(选择别名)包装您重复使用的别名：



SELECT 10 AS my_num, 
       (SELECT my_num) * 5 AS another_number
FROM table


You'll need to use a subselect to use that aliases that way

您需要使用子选择以这种方式使用该别名



SELECT my_num*5 AS another_number FROM
(
    SELECT 10 AS my_num FROM table
) x


Aliases in sql are not like variables in a programming language. Aliases can only be referenced again at certain points (particularly in GROUP BY and HAVING clauses). But you can't reuse an alias in the SELECT clause. So you can use a derived query (such as suggested by Rubens Farias) which lets you basically rename your columns and/or name any computed columns.

SQL中的别名不像编程语言中的变量。别名只能在某些点再次引用（特别是在GROUP BY和HAVING子句中）。但不能在SELECT子句中重用别名。因此，您可以使用派生查询（如Rubens Farias建议的），它基本上允许您重命名列和/或命名任何计算列。



Or you could use a VIEW if your formulas are generally fixed

或者，如果您的公式通常是固定的，则可以使用视图



CREATE VIEW table10 AS SELECT 10 AS my_num FROM table;
SELECT my_num * 5 AS another_number FROM table10;


I believe that will be slightly faster than using a derived query but it probably depends a lot on your real query.

我相信这会比使用派生查询稍微快一些，但这可能在很大程度上取决于您的实际查询。



Or you could just duplicate the work:

或者，您可以只复制这项工作：



SELECT 10 AS my_num, 10 * 5 AS another_number FROM table;


Which might be convenient in something like php/perl:

这在php/perl之类的代码中可能很方便：



my $my_num = 10;
my $query = "SELECT $my_num AS my_num, $my_num * 5 AS another_number FROM table";


``Another option is to use the APPLY operator

``另一个选项是使用Apply运算符



SELECT my_num, my_num*5 AS another_number
FROM table
CROSS APPLY 
(SELECT 5 AS my_num) X


Easy peasy. You can use variables.

很简单。您可以使用变量。


SELECT @my_num := 10 AS my_num, @my_num*5 AS another_number;
SHOW WARNINGS;

This will throw a warning: "Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'."

这将引发警告：“不建议在表达式中设置用户变量，在将来的版本中将删除该设置。请考虑使用其他选项：‘set Variable=Expression，...’或‘SELECT Expression(S)Into Variables(S)’。”


更多回答

I am wondering where that functionality is documented. I've searched in the whole MySQL documentation and I have not found it. I don't know how this "alias reusing" works and I am curious about portability or performance impact. If someone knows more about this I will appreciate a link. Thanks you.

我想知道这个功能是在哪里记录的。我在整个MySQL文档中进行了搜索，但没有找到。我不知道这种“别名重用”是如何工作的，我对可移植性或性能影响很好奇。如果有人知道更多关于这一点，我将感谢链接。谢谢你。

this just made my week.. maybe month. super useful especially with aliases built from flow control statements like IF(), CASE(), etc

这让我的一周..。也许是一个月。特别适用于从IF()、Case()等流控制语句构建的别名

@CarlosGant: The (SELECT alias) part is treated as a "dependent subquery", at least according to MySQL's EXPLAIN and Visual Explain functions.

@CarlosGant：至少在MySQL的解释和可视化解释函数中，(选择别名)部分被视为“依赖子查询”。

Nevermind, it didn't work for me. I got "unknown column".

不要紧，这对我不管用。我收到了《无名专栏》。

SELECT kabupaten.nama AS kabupaten, kecamatan.nama AS kecamatan, SUM(pihaks.luas) AS target_luas, SUM(realisasi.luas) AS realisasi_luas, SELECT(realisasi_luas)/SELECT(target_luas)*100 AS persentase_luas. Tried like this but not working

选择kabupaten.nama作为kabupaten，kecamatan.nama作为kecamatan，sum(pihaks.luas)作为Target_luas，sum(realisasi.luas)作为realisasi_luas，SELECT(realisasi_luas)/SELECT(target_luas)*100作为persentase_luas。像这样试过了，但没有奏效

@RyanHeneise, it's a subselect alias; that way you could go with x.my_num

@RyanHeneise，它是一个子选别名；这样您就可以使用x.my_num

Note that CROSS APPLY does not appear to be available in MySQL (yet).

注意，在MySQL中似乎不能使用交叉应用(目前还没有)。

32 4 0
文章推荐： How to make image inside a div bigger in HTML(如何在Html中放大div中的图像)
文章推荐： Changing the icon of the back button on the Navigation Bar in .net MAUI(在.Net Maui中更改导航栏上后退按钮的图标)
文章推荐： Why does this output "7 9 35 5 27" and not "7 35 5 9 27"?(为什么输出的是“7 9 35 5 27”而不是“7 35 5 9 27”？)
文章推荐： WPF - WPFUI Library - Icons doesn't render on the Navigation bar C#(WPF-WPFUI库-图标不呈现在导航栏C#上)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com