SELECT

功能描述

SELECT用于从表或视图中取出数据。

SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。

注意事项

• 本章节只包含shark新增的语法，原openGauss的语法未做删除和修改。
• 新增支持TOP子句。

语法格式

• 查询数据

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ top_clause ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ into_option ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW {window_name AS ( window_definition )} [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT { [offset,] count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] [PERCENT] { ROW | ROWS } { ONLY | WITH TIES } ]
[ into_option ]
[ {FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT N]} [...] ]
[ into_option ];

• 其中TOP子句top_clause为：

  TOP (expression) [ PERCENT ] [ WITH TIES ]
  

参数说明

• TOP (expression) [ PERCENT ] [ WITH TIES ]

  TOP子句限制查询结果集中返回指定的行数或行数的百分比。将 TOP 子句与 ORDER BY 子句一起使用时，结果集被限制为有序的指定行数，否则TOP子句按未定义的顺序返回指定行数。使用PERCENT关键字可以指定返回的行数为查询结果集的百分比。WITH TIES关键字表示返回指定的行数以及结果集有序情况下所有与最后一行相同的值。

  说明：

  • 兼容D库中，使用PERCENT关键字时，PERCENT值的范围为[0,100]，超范围报错。
  • 兼容D库中，指定的百分比数和结果集总数相乘后不为整数时，向上取整到最接近的整数值。指定的行数不为整数时，四舍五入到最接近的整数值。
  • 兼容D库中，WITH TIES必须和ORDER BY 子句一起使用，否则报错。这一规则适用于TOP子句和FETCH子句。
  • 兼容D库中，TOP子句和LIMIT子句以及FETCH子句不能同时使用。

TOP子句示例

opengauss=# CREATE TABLE Products(QtyAvailable smallint, UnitPrice money, InventoryValue AS (QtyAvailable * UnitPrice) PERSISTED);
CREATE TABLE
opengauss=# INSERT INTO Products(QtyAvailable, UnitPrice) VALUES (25, 2.00), (10, 1.5), (25, 2.00), (10, 1.5), (10, 1.5);
INSERT 0 5
opengauss=# select * from Products ;
 qtyavailable | unitprice | inventoryvalue 
--------------+-----------+----------------
           25 |     $2.00 |         $50.00
           10 |     $1.50 |         $15.00
           25 |     $2.00 |         $50.00
           10 |     $1.50 |         $15.00
           10 |     $1.50 |         $15.00
(5 rows)

opengauss=# select TOP 4 * from Products ORDER BY qtyavailable;
 qtyavailable | unitprice | inventoryvalue 
--------------+-----------+----------------
           10 |     $1.50 |         $15.00
           10 |     $1.50 |         $15.00
           10 |     $1.50 |         $15.00
           25 |     $2.00 |         $50.00
(4 rows)

opengauss=# select TOP 2 PERCENT * from Products ORDER BY qtyavailable;
 qtyavailable | unitprice | inventoryvalue 
--------------+-----------+----------------
           10 |     $1.50 |         $15.00
(1 row)

opengauss=# select TOP 2 PERCENT WITH TIES * from Products ORDER BY qtyavailable;
 qtyavailable | unitprice | inventoryvalue 
--------------+-----------+----------------
           10 |     $1.50 |         $15.00
           10 |     $1.50 |         $15.00
           10 |     $1.50 |         $15.00
(3 rows)

相关链接

SELECT