查询中的字段别名，nzsql [英] Field Aliasing in queries, nzsql

问题描述

我正在Netezza工作-或者，您知道的是Analytics(分析)的纯数据-nzsql，但是我认为这是一个ANSI SQL问题.这个问题是如此基本，我什至不知道如何搜索.

I'm working in Netezza -- or, you know, pure data for Analytics -- nzsql, but I think this is an ANSI SQL question. The question is so basic, I don't even know how to search for it.

CREATE TEMPORARY TABLE DEMO1 AS SELECT 'SMORK' AS SMORK, 'PLONK' AS PLONK, 'SPROING' AS SPROING;

SELECT SMORK AS PLONK, PLONK, SPROING AS CLUNK, CLUNK
FROM DEMO1;

这将返回"SMORK，PLONK，SPROING，SPROING"，也就是说，查询可以很好地重用CLUNK别名，但是PLONK别名将被源表中的列覆盖.现在，如果我真的想要源表中的列，则可以编写SELECT SMORK AS PLONK，DEMO1.PLONK等，但是我不知道如何指定我更喜欢先前在同一目录中定义的别名. SELECT子句.

This returns 'SMORK, PLONK, SPROING, SPROING', which is to say, the query is fine reusing the CLUNK alias, but the PLONK alias is overwritten by the column from the source table. Now, if I really wanted the column from the source table, I could write SELECT SMORK AS PLONK, DEMO1.PLONK et c, but I don't know how to specify that I would prefer the alias I've defined earlier in same the SELECT clause.

有人知道吗?

推荐答案

在Netezza中，选择列时， Netezza将首先搜索表列，然后搜索别名.

In Netezza, when selecting a column, Netezza will search for table column first, and then alias.

示例: 假设我们有以下语句:

Example: Suppose we have the following statements:

CREATE TEMPORARY TABLE EMPLOYEES AS 
SELECT 1001 AS EMPLOYEE_ID
      ,'Alex' AS FIRST_NAME
      ,'Smith' AS LAST_NAME
      ,'Alex J. Smith' AS FULL_NAME;

SELECT 
     EMPLOYEE_ID
    ,FIRST_NAME
    ,LAST_NAME
    ,LAST_NAME||', '||FIRST_NAME AS FULL_NAME
    ,'My full name is :'||FULL_NAME AS DESCRIPTION
  FROM EMPLOYEES;

它将返回

EMPLOYEE_ID  FIRST_NAME  LAST_NAME   FULL_NAME      DESCRIPTION
   1001        Alex        Smith     Smith, Alex    My full name is :Alex J. Smith

在 DESCRIPTION 中的通知，FULL_NAME值是从表列而不是别名中选择的.

Notice in DESCRIPTION, the FULL_NAME value is picked from table column, not from alias.

如果要使DESCRIPTION列使用别名FULL_NAME中的值，则可以分两个步骤进行操作:

If you want DESCRIPTION column use value from alias FULL_NAME, you can do it in two steps:

步骤1.创建一个子查询，其中包含您想要的所有列.对于要重用的所有别名，您需要将其命名为FROM子句中任何表列中都不存在的名称；

Step 1. Create a sub-query includes all columns you want. For all alias names you want to reuse, you need to name them as names not exist in any table columns on your FROM clause;

第2步.仅从子查询中选择所需的列.

Step 2. SELECT only column you want from the subquery.

CREATE TEMPORARY TABLE EMPLOYEES AS SELECT 1001 AS EMPLOYEE_ID, 'Alex' AS FIRST_NAME, 'Smith' AS LAST_NAME, 'Alex J. Smith' AS FULL_NAME;

WITH EMPLOYESS_TMP AS (
SELECT 
     EMPLOYEE_ID
    ,FIRST_NAME
    ,LAST_NAME
    ,LAST_NAME||', '||FIRST_NAME AS FULL_NAME2
    ,FULL_NAME2 AS FULL_NAME
    ,'My full name is :'||FULL_NAME2 AS DESCRIPTION
  FROM EMPLOYEES)
SELECT 
     EMPLOYEE_ID
    ,FIRST_NAME
    ,LAST_NAME
    ,FULL_NAME
    ,DESCRIPTION
 FROM EMPLOYESS_TMP;   

这将返回您想要的内容:

This will return what you want:

EMPLOYEE_ID  FIRST_NAME  LAST_NAME   FULL_NAME      DESCRIPTION
   1001        Alex        Smith    Smith, Alex     My full name is :Smith, Alex

这篇关于查询中的字段别名，nzsql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！