“AS”在SQL中意味着什么？

Below is the synopsis of SELECT from the PostgreSQL documentation. It seems to me that sometimes we write <expression> AS <name> and sometimes it's <name> AS <expression>. In ordinary English, I tend to think <expression> AS <name> is much more common (e.g. "Address her as. Doctor Smith, please., and I'm having trouble understanding how to think about <name> AS <expression>.

以下是PostgreSQL文档中SELECT的概要。在我看来，有时我们写 AS ，有时它是 AS 。在普通英语中，我倾向于认为 AS 更为常见（例如“请将她称为。史密斯博士，请。我很难理解如何考虑 AS 。

1. How can we distinguish between where to use <name> AS <expression> and <expression> as <name>?
2. 我们如何区分在哪里使用 AS 和 作为 ？
3. What are minimal obvious examples of each?
4. 什么是每个最小的明显例子？
5. Are there parallels of each kind in ordinary language, which would make it intuitively obvious when to use what?
6. 在普通语言中是否存在各种类型的相似之处，这将使得何时使用什么直观明显？

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
 where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
 and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

2 个解决方案

#1

I like the question.

我喜欢这个问题。

Here is how I see it and how I explain it to people, hope it helps:

这是我如何看待它以及如何向人们解释它，希望它有所帮助：

Let's start with <expression> as <name>. The simplest analogy from real life is an abbreviation. It was created to make the code cleaner, easier to read and simply shorter. Let's imagine a scenario: we have data on all students from the Massachusetts Institute of Technology as well as Massachusetts Department of Motor Vehicles in our database and we want to find students that have speeding tickets and how much they have paid.

让我们从 开始，作为 。现实生活中最简单的比喻是缩写。它的创建是为了使代码更清晰，更易于阅读和简单。让我们想象一个场景：我们在我们的数据库中有来自麻省理工学院和马萨诸塞州机动车部的所有学生的数据，我们希望找到那些有超速票和学费的学生。

SELECT
    government.SocialSecurityAdministration.FirstName,
    government.SocialSecurityAdministration.LastName,
    education.MasachusettsInstituteOfTechnology.Faculty
    government.DepartmentOfMotorVehiclesTickets.TicketTotal,
    government.SocialSecurityAdministration.SocialSecurityNumber
FROM
    education.MasachusettsInstituteOfTechnology
    INNER JOIN government.DepartmentOfMotorVehiclesTickets ON education.MasachusettsInstituteOfTechnology.SocialSecurityNumber = government.DepartmentOfMotorVehicles.SocialSecurityNumber
    INNER JOIN government.SocialSecurityAdministration ON government.DepartmentOfMotorVehicles.SocialSecurityNumber = government.SocialSecurityAdministration.SocialSecurityNumber

Looks ugly, doesn't it? In real life, we've abbreviated the Massachusetts Institute of Technology to be MIT and the Department of Motor Vehicles to be DMV. I'm not aware of the official abbreviation for Social Security Administration (but we can come up with one) though we say SSN when we mean Social Security Number. Let's implement this idea:

看起来很难看，不是吗？在现实生活中，我们将麻省理工学院简称为麻省理工学院，将机动车系简称为DMV。我不知道社会保障管理局的官方缩写（但我们可以提出一个）虽然我们说的是社会安全号码时的SSN。让我们实现这个想法：

SELECT
        ssnAdm.FirstName,
        ssnAdm.LastName,
        ssnAdm.Faculty
        dmv.TicketTotal,
        ssnAdm.SocialSecurityNumber AS ssn
    FROM
        education.MasachusettsInstituteOfTechnology AS mit
        INNER JOIN government.DepartmentOfMotorVehiclesTickets AS dmv ON mit.SocialSecurityNumber = dmv.SocialSecurityNumber
        INNER JOIN government.SocialSecurityAdministration AS ssAdm ON dmv.SocialSecurityNumber = dmv.SocialSecurityNumber

Looks better now, doesn't it?

现在看起来更好，不是吗？

Now to the <name> as <expression> portion of it. This is done to simplify the code as well as some performance optimizations but let's focus on simplification for now. Using the same example I've used above, you might want to get/ask the following: "For every MIT student that has received a ticket I need to know the last 4 digits of their SSN, their last name, the amount of money in their bank account and their last VISA transaction amount". Yes, you work for CIA.

现在以 作为 部分。这样做是为了简化代码以及一些性能优化，但现在我们将重点放在简化上。使用我上面使用的相同示例，您可能想要/询问以下内容：“对于每个已收到机票的麻省理工学院学生，我需要知道他们的SSN的最后4位数字，他们的姓氏，金额在他们的银行账户和他们的最后一笔签证交易金额“。是的，你为CIA工作。

Let's write it:

我们来写吧：

SELECT
    RIGHT(4,ts.ssn) as LastFourDigitsSsn,
    ts.LastName,
    bad.TotalAmount,
    ISNULL(visa.TransactionAmt,'Student uses MasterCard') AS VisaTransaction
FROM
    (SELECT
        ssnAdm.FirstName,
        ssnAdm.LastName,
        ssnAdm.Faculty
        dmv.TicketTotal,
        ssnAdm.SocialSecurityNumber AS ssn
    FROM
        education.MasachusettsInstituteOfTechnology AS mit
        INNER JOIN government.DepartmentOfMotorVehiclesTickets AS dmv ON mit.SocialSecurityNumber = dmv.SocialSecurityNumber
        INNER JOIN government.SocialSecurityAdministration AS ssAdm ON dmv.SocialSecurityNumber = dmv.SocialSecurityNumber
    ) AS ts
    INNER JOIN business.BankAccountsData AS bad ON ts.ssn = bad.SocialSecurityNumber
    OUTER APPLY (SELECT TOP 1 TransactionAmt FROM business.VisaProcessingData vpd WHERE vpd.BankAccountID = bad.ID ORDER BY TransactionDateTime DESC) as visa

Well, looks ugly again. But what if we simplify it a bit and express certain things outside of the actual statement? That's when <name> as <expression> comes in. Let's do it:

好吧，再看起来很难看。但是如果我们简化它并在实际陈述之外表达某些事情呢？那是 作为 进来的时候。让我们这样做：

WITH MitTicketedStudents AS (
    SELECT
        ssnAdm.LastName,
        ssnAdm.SocialSecurityNumber as ssn,
        RIGHT(4,ssnAdm.SocialSecurityNumber) as LastFourDigitsSsn
    FROM
        education.MasachusettsInstituteOfTechnology AS mit
        INNER JOIN government.DepartmentOfMotorVehiclesTickets AS dmv ON mit.SocialSecurityNumber = dmv.SocialSecurityNumber
        INNER JOIN government.SocialSecurityAdministration AS ssAdm ON dmv.SocialSecurityNumber = dmv.SocialSecurityNumber
),
LatestVisaTransactions AS (
    SELECT DISTINCT
        BankAccountID,
        FIRST_VALUE(TransactionAmt) OVER (PARTITION BY BankAccountId ORDER BY TransactionDateTime DESC) as TransactionAmt
    FROM
        business.VisaProcessingData
)

-- And let's use our expressions now

SELECT
    mts.LastFourDigitsSsn,
    mts.LastName,
    bad.TotalAmount,
    ISNULL(lvt.TransactionAmt,'Student uses MasterCard') AS VisaTransaction
FROM
    MitTicketedStudents mts
    INNER JOIN business.BankAccountsData AS bad ON mts.ssn = bad.SocialSecurityNumber
    LEFT OUTER JOIN LatestVisaTransactions lvt ON bad.ID = lvt.BankAccountID;

Looks better, doesn't it?

看起来更好，不是吗？

Conclusion: when you want to separate code you use <name> as <expression>, when you want to give something an alias to simplify code you use <expression> as <name>.

结论：当您想要分隔代码时，使用 作为 ，当您想要为别名提供别名以简化代码时，可以使用 作为 。

#2

What matters is where it appears.

重要的是它出现的地方。

mytable:
mycolumn myothercolumn
----------------------
       1             a
       2             b

SELECT myrow.mycolumn * 2 AS mylabel
FROM (SELECT * FROM mytable) AS myrow
WHERE myrow.mycolumn > 1

mylabel
-------
      4

In SELECT, we refer to the value of an expression AS some output column name ("column alias"). In FROM, we refer to (a typical row of) the value of a table expression AS some name ("table alias", "correlation name").

在SELECT中，我们引用表达式AS的值作为某些输出列名（“列别名”）。在FROM中，我们将表格AS的值（一个典型的行）称为某个名称（“表别名”，“相关名称”）。

(It turns out that because of details of the grammar typos are less problematic if we use AS in SELECT clauses but don't use AS in FROM clauses.)

（事实证明，如果我们在SELECT子句中使用AS但在FROM子句中不使用AS，那么由于语法错别字的细节问题较少。）

There are other uses of AS. The context also determines what they mean, and they also correspond to using using a name to refer to something.

AS还有其他用途。上下文也决定了它们的含义，它们也对应于使用名称来引用某些内容。

In technical contexts it turns out not to be helpful to try to make sense of what something means based on the everyday meanings of technical terms, including making sense of what a thing is based on its name. The SQL language designers [sic] didn't choose to always have either <expression> AS <name> or <name> AS <expression>. That is just how it is. That is just how you write stuff to get your program to do stuff to stuff. (Accepted but more modern principles of computer language design do suggest more regular notations.)

在技​​术背景下，根据技术术语的日常含义，尝试理解某些东西的含义，包括理解一个基于其名称的东西，结果证明没有帮助。 SQL语言设计者[sic]没有选择始终具有 AS 或 AS 。就是这样。这就是你如何写东西让你的程序做东西。 （接受但更现代的计算机语言设计原则确实建议更多的常规符号。）