第七十章 SQL命令 SELECT（二）

select-item

这是所有SELECT语句的必选元素。
通常，选择项指的是FROM子句中指定的表中的一个字段。
选择项由下列一个或多个项组成，多个项之间用逗号分隔:

• 列名(字段名)，带或不带表名别名:

SELECT Name,Age FROM Sample.Person

字段名不区分大小写。
但是，结果集中与字段关联的标签使用表定义中指定的SqlFieldName的字母大小写，而不是选择项中指定的字母大小写。

包含一个或多个下划线的字段名引用嵌入的串行对象属性。
例如，对于字段名Home_City，表包含一个引用字段Home，该字段引用定义属性City的嵌入式串行对象。
对于字段名Home_Phone_AreaCode，该表包含一个引用字段Home，该字段引用嵌入式串行对象属性Phone，该属性引用定义AreaCode属性的嵌套嵌入式串行对象。
如果选择一个引用字段，如Home或Home_Phone，则以%List数据类型格式接收串行对象中所有属性的值。

要显示RowID(记录ID)，可以使用%ID伪字段变量别名，该别名显示RowID，而不管它被分配的名称是什么。
默认情况下，RowID的名称是ID，但如果存在用户定义的名为ID的字段， IRIS可能会重命名它。
默认情况下，RowID是一个隐藏字段。

stream字段上的SELECT返回打开的stream对象的oref(对象引用):

SELECT Name,Picture FROM Sample.Employee WHERE Picture IS NOT NULL

当FROM子句指定多个表或视图时，必须使用句点将表名(或表名别名)作为选择项的一部分，如下面的两个示例所示:

SELECT Sample.Person.Name,Sample.Employee.Company
      FROM Sample.Person, Sample.Employee

表名别名:

SELECT p.Name, e.Company
      FROM Sample.Person AS p, Sample.Employee AS e

但是，如果已为该表名分配了别名，则不能将完整表名作为选择项的一部分。
尝试这样做会导致SQLCODE -23错误。

可以使用排序函数指定选择项字段的排序和显示。
可以提供不带括号(SELECT %SQLUPPER Name)或带括号(SELECT %SQLUPPER(Name))的排序规则函数。
如果排序规则函数指定了截断，则括号是必需的(SELECT %SQLUPPER(Name,10))。

当选择项引用嵌入的串行对象属性(嵌入的串行类数据)时，使用下划线语法。
下划线语法由对象属性的名称、下划线和嵌入对象中的属性组成:例如，Home_City和Home_State。
(在其他上下文中，例如索引表，它们使用点语法表示:Home.City。)

SELECT Home_City,Home_State FROM Sample.Person

可以使用SELECT直接查询引用字段(例如Home)，而不是使用下划线语法。
因为返回的数据是列表格式的，所以可能需要使用$LISTTOSTRING或$LISTGET函数来显示数据。
例如:

SELECT $LISTTOSTRING(Home,'^') AS HomeAddress FROM Sample.Person

• 子查询。
  子查询返回指定表中的单个列。
  这个列可以是单个表字段(SELECT Name)的值，也可以是作为单个列返回的多个表字段的值，可以使用连接(SELECT Home_City||Home_State)或指定容器字段(SELECT Home)。
  子查询可以使用隐式连接(箭头语法)。
  子查询不能使用星号语法，即使在子查询中引用的表只有一个数据字段。

子查询的一个常见用法是指定不受GROUP BY子句约束的聚合函数。
在下面的示例中，GROUP BY子句按几十年(例如，25到34)对年龄进行分组。
AVG(Age)选择项给出了由group by子句定义的每个组的平均年龄。
为了获得所有组中所有记录的平均年龄，它使用了一个子查询:

SELECT Age AS Decade,
       COUNT(Age) AS PeopleInDecade,
       AVG(Age) AS AvgAgeForDecade,
       (SELECT AVG(Age) FROM Sample.Person) AS AvgAgeAllDecades
FROM Sample.Person
GROUP BY ROUND(Age,-1)
ORDER BY Age

• 箭头语法，用于访问from子句表以外的表中的字段。
  这被称为隐式连接。
  在下面的示例中，示例。
  Employee表包含Company字段，其中包含Sample中对应公司名称的RowID。
  公司表。
  箭头语法从表中检索公司名称:

SELECT Name,Company->Name AS CompanyName
      FROM Sample.Employee

在这种情况下，必须拥有被引用表的SELECT特权:对于被引用表的字段和RowID列，可以是表级的SELECT特权，也可以是列级的SELECT特权。

• 星号语法(*)，按列号顺序选择表中的所有列:

SELECT TOP 5 * FROM Sample.Person

星号语法选择嵌入的串行对象属性(字段)，包括嵌套在串行对象中的串行对象的属性。
没有选择引用串行对象的字段。
例如，选择来自嵌入式串行对象的Home_City属性，但是使用Home引用字段访问Sample。
没有选择地址嵌入串行类(包含City属性)。

星号语法不选择隐藏字段。
默认情况下，RowID是隐藏的(不通过SELECT *显示)。
但是，如果表定义为%PUBLICROWID，则SELECT *返回RowID字段和所有非隐藏字段。
缺省情况下，该字段的名称为ID，但如果存在用户自定义的ID字段，IRIS可能会对其进行重命名。

在下面的示例中，select-item由一个非限定星号语法组成，用于从表中选择所有列。
注意，你也可以指定重复的列名(在本例中是Name)和非列的select-item元素(在本例中是{fn NOW}):

SELECT TOP 5 {fn NOW} AS QueryDate,
             Name AS Client,
             *
FROM Sample.Person

在下面的示例中，select-item由限定星号语法组成，用于从一个表中选择所有列，以及从另一个表中选择列名列表。

SELECT TOP 5 E.Name AS EmpName, 
             C.*, 
             E.Home_State AS EmpState
FROM Sample.Employee AS E, Sample.Company AS C

注意:SELECT *是 SQL完全支持的一部分，在应用程序开发和调试期间，它非常方便。
但是，在生产应用程序中，首选的编程实践是显式列出所选字段，而不是使用星号语法形式。
显式列出字段可以使应用程序更清晰、更容易理解、更容易维护，并更容易按名称搜索字段。

• 包含一个或多个SQL聚合函数的选择项。
  聚合函数总是返回单个值。
  聚合函数的参数可以是下列任何一种:

  • 单个列名计算查询所选行的所有非空值的聚合:

  SELECT AVG(Age) FROM Sample.Person
  

  • 也允许使用标量表达式来计算聚合:

  SELECT SUM(Age) / COUNT(*) FROM Sample.Person
  

  • 星号语法(*)-与COUNT函数一起使用，用于计算表中的行数:

  SELECT COUNT(*) FROM Sample.Person
  

  • 一个选择不同的函数-通过消除冗余值计算聚合:

  SELECT COUNT(DISTINCT Home_State) FROM Sample.Person
  

  • 虽然ANSI SQL不允许在单个SELECT语句中组合列名和聚合函数，但SQL扩展了这一标准，允许这样做:

  SELECT Name, COUNT(DISTINCT Home_State) FROM Sample.Person
  ```sql
  - 使用`%FOREACH`的聚合函数。
  这将导致对一个或多个列的每个不同值计算聚合:
  ```sql
  SELECT DISTINCT Home_State, AVG(Age %FOREACH(Home_State)) 
   FROM Sample.Person
  

  • 使用%AFTERHAVING的聚合函数。
    这导致在HAVING子句指定的子population上计算聚合:

  SELECT Name,AVG(Age %AFTERHAVING) 
    FROM Sample.Person
    HAVING (Age > AVG(Age))
  

  将返回年龄大于平均年龄的那些记录，给出年龄高于数据库中所有人平均年龄的那些人的平均年龄。

• Window function语法，支持基于特定于该行的“窗口框架”为每一行计算聚合、排名和其他函数。
  支持以下语法

  window-function() OVER (PARTITION BY partfield ORDER BY orderfield)
  

  • window-function:支持如下窗口函数:ROW_NUMBER()， RANK()， PERCENT_RANK()， FIRST_VALUE(field)， SUM(field)。
  • OVER:必须在OVER关键字后面加上括号。
    括号中的子句是可选的。
  • PARTITION BY partfield:可选子句，根据指定的partfield对行进行分区。
    部分字段可以是单个字段，也可以是用逗号分隔的字段列表。
    如果指定了，PARTITION BY必须在ORDER BY之前指定。
  • ORDER BY orderfield:可选子句，根据指定的orderfield对行进行排序。
    Orderfield可以是单个字段，也可以是逗号分隔的字段列表。

在Window function中指定的字段可以接受表别名前缀。

Window function可以指定列别名。
默认情况下，列被标记为Window_n。

• 作为过程存储的用户定义的类方法。
  可以是非限定方法名，也可以是限定方法名。
  以下是所有有效的类方法名:
  Sample.RandLetter()一个限定的类方法名;
  和Sample.Rand_Letter()调用类方法“Rand_Letter”()。
  在下面的例子中，RandCaseLetter()是一个返回随机字母的类方法，可以是大写字母(' U ')，也可以是小写字母('L'):

SELECT RandCaseLetter('U')

该方法的返回值将自动从Logical格式转换为Display/ODBC格式。
默认情况下，该方法的输入值不会从Display/ODBC格式转换为Logical格式。
但是，可以使用$SYSTEM.SQL.Util.SetOption("SQLFunctionArgConversion")方法在系统范围内配置输入显示到逻辑的转换。
可以使用$SYSTEM.SQL.Util.GetOption("SQLFunctionArgConversion")来确定该选项的当前配置。

如果指定的方法在当前名称空间中不存在，系统将生成SQLCODE -359错误。
如果指定的方法不明确(可能引用多个方法)，系统将生成SQLCODE -358错误。

• 对数据库列进行操作的用户提供的ObjectScript函数调用(外部函数):

SELECT $$REFORMAT(Name)FROM MyTable

如果在系统范围内配置了“允许SQL语句中的外部函数”选项，则只能在SQL语句中调用用户提供的(外部)函数。
默认为“No”;
默认情况下，尝试调用用户提供的函数会产生SQLCODE -372错误。

不能使用用户提供的函数来调用%routine(以%字符开头的例程)。
尝试这样做会产生SQLCODE -373错误。

• 对字段值应用额外处理的选择项:

算术运算:

SELECT Name, Age,Age-AVG(Age) FROM Sample.Person

如果选择项算术运算包括除法，并且数据库中该字段的任何值都可能产生值为零或NULL的除数，则不能依赖测试顺序来避免被零除法。
相反，使用case语句来抑制风险。

SQL函数:

SELECT Name,$LENGTH(Name) FROM Sample.Person

SQL case转换函数:

SQL case转换函数:

一个XMLELEMENT、XMLFOREST或XMLCONCAT函数，它在从指定列名检索的数据值周围放置XML(或HTML)标记。

• 为所有记录返回相同值的选择项。

  当所有select-items都不引用表数据时，FROM子句是可选的。
  如果包含FROM子句，则指定的表必须存在。

  • 算术运算:

  SELECT 7 * 7, 7 * 8 FROM Sample.Person
  

  SELECT Name, Age, 9 - 6 FROM Sample.Person
  

  • 字符串字面值或操作字符串字面值的函数:

  SELECT UCASE('fred') FROM Sample.Person
  

  字符串字面量可以用来产生更可读的输出，如下面的示例所示:

  SELECT TOP 10 Name,'was born on',%EXTERNAL(DOB)
  FROM Sample.Person
  

  数值字面值的指定方式决定了它的数据类型。
  因此，字符串'123'将被报告为数据类型VARCHAR，而数字123将被报告为数据类型INTEGER或numeric。

  • %TABLENAME或%CLASSNAME伪字段变量关键字。
    %TABLENAME返回当前表名。
    %CLASSNAME返回当前表对应的类名。
    如果查询引用多个表，可以在关键字前加上表别名。
    例如,t1.%TABLENAME。
  • 以下ObjectScript特殊变量之一(或其缩写):$HOROLOG， $JOB， $NAMESPACE， $TLEVEL， $USERNAME， $ZHOROLOG， $ZJOB， $ZNSPACE， $ZPI， $ZTIMESTAMP， $ZTIMEZONE， $ZVERSION。