GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-09-15) 南大通用数据技术股份有限公司 1201 Query OK, 0 rows affected (Elapsed: 00:00:00.06) 存在的列名加filler gbase> load data infile 'sftp://gbase:gbase@192.168.105.54//home/gbase/t1.txt' into table t1 fields terminated by '|' table_fields 'c1,c2 filler,c3'; Query OK, 1 row affected (Elapsed: 00:00:01.13) Task 1310 finished, Loaded 1 records, Skipped 0 records gbase> select * from t1; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 1 | NULL | b | +------+------+------+ 3 rows in set (Elapsed: 00:00:00.03) 数据文件字段比表定义的列多,多出来的列需要忽略 gbase@suse100-4:~> cat t1.txt 1|m|a|b gbase> create table t1(c1 int,c2 varchar(10),c3 varchar(20)); Query OK, 0 rows affected (Elapsed: 00:00:00.06) 存在的列名加filler gbase> load data infile 'sftp://gbase:gbase@192.168.105.54//home/gbase/t1.txt' into table t1 fields terminated by '|' table_fields 'c1,m,c2,c3'; Query OK, 1 row affected (Elapsed: 00:00:01.13) Task 1310 finished, Loaded 1 records, Skipped 0 records gbase> select * from t1; +------+------+------+ | c1 | c2 | c3 | +------+------+------+ | 1 | a | b | +------+------+------+ 3 rows in set (Elapsed: 00:00:00.03)
语法格式 ORDER BY {col_name | expr | position} [ASC | DESC] , ... 参数说明 ORDER BY 用于对结果集进行排序,数据列列名称或者表达式。 col_name: 指定排序的数据列, 多列之间用 “,” 分隔。 col_name 可以是SELECT 中使用AS 定义的别名。 expr:指定排序的表达式,多列之间用“,”分隔。 position :在“ SELECT col_name_1,..., col_name_n FROM ” 之间的 “col_name_1,..., col_name_n”的序号,position 是整数型数值,从“1”开始。
例如: “SELECT stu_no,stu_name FROM stundent ORDER BY 1;” 语句中, “1” 就是指代数据列stu_no。 ASC | DESC:如果希望对记录进行排序,可以使用ASC 或DESC 关键字来指 定排序规则,ASC 代表升序规则,DESC 代表降序规则。默认按照升序对记录 进行排序。 示例 示例1:... ORDER BY... gbase> SELECT a.stu_name,math,english,sum(math+english) AS total FROM student a INNER JOIN result b ON a.stu_no = b.stu_no GROUP BY a.stu_no ORDER BY a.stu_no;
GBase UP 产品手册 5 数据库管理指南 文档版本04(2021-04-21) 南大通用数据技术股份有限公司 707 +----------+------+---------+-------+ | stu_name | math | english | total | +----------+------+---------+-------+ | Tom | 80.0 | 85.2 | 165.2 | | Jim | 78.0 | 95.5 | 173.5 | | John | 89.5 | 99.0 | 188.5 | | Rose | 65.0 | 75.5 | 140.5 | | Jane | 92.0 | 94.0 | 186.5 | | Mike | 72.5 | 86.0 | 158.5 | | Jack | 85.0 | 76.0 | 161.5 | | Jerry | 95.0 | 97.0 | 192.0 | | Allen | 56.0 | 78.0 | 134.0 | | Max | 86.0 | 93.0 | 179.0 | +----------+------+---------+-------+ 10 rows in set