简单 SQL

with T as (x) 

用集算器脚本定义数据文件中的表，x可返回序表或游标。

select x F,…

从表中选取数据，x为字段或表达式，F为字段别名。

_file

如果from文件，该字段表示文件名，其它from时无定义。

_ext

扩展名。

_size

文件大小。

_date

文件最后修改时间。

*

*只包含文件内的字段不含文件属性。

.from T T’

使用定义过的表T，T’为表别名，T’可省略。

from fn T

直接用文件fn作为表T，文件类型包括： txt、csv、xls、xlsx、btx（集文件），按游标处理；文件均认为有标题，fn文件可以用相对路径或绝对路径，相对路径相对于集算器主目录。

文件名中允许使用通配符，*匹配0个或多个字符，?匹配单个字符；可以对应多个文件，认为是多个同结构的数据文件做并集，暂不支持访问子目录。

from {x}

x为当前网格内可执行的表达式，且必须是返回结果为序表或游标的集算器表达式。

from v

v是当前网络内的变量名。

as

别名前可以用as。

where

条件过滤。

join 、left join、full join

关联，有join时认为全内存，使用xjoin实现。

group by

分组，用groups，认为结果集可内存化。

group by n 

按select中第n个表达式分组，n只能是常数。

having

分组过滤。

order by

排序，默认升序。

order by n

按select中第n个表达式排序，n只能是常数。

distinct

同SQL，只能单表。

and、 or 、not 、 in、 is null、

case when else end

使用SQL风格，in只能使用常数集合,暂不支持子查询。

between

介于两个值之间的数据范围，例如f1 between 1 and 3，相当于 f1 >= 1 && f1 <= 3，暂不支持子查询。

like

模糊查询，支持如下几种通配符：

%  多个字符；

_  单个字符；

暂不支持子查询。

into fn

将结果集写入到文件中，根据扩展名决定文件类型，目前支持的扩展名包括txt、csv、btx。

文件已存在则判断结构相同后追加，结构不同则报错。

fn文件可以用相对路径或绝对路径，相对路径相对于集算器主目录。 

into ${x}.ext

可在文件名中使用有宏的串，将计算出x拼入文件名。

?、?i

SQL中的参数，i代表第i个参数。 

聚合函数

sum、count、 max、 min、avg、distinct、count(distinct)  。

集合运算

union、union all、intersect、minus。

子查询

from之外涉及的子查询均认为全内存，可支持无参数的子查询。

exists  、not exists

判断子查询是否返回记录。

计算函数

在sqltranslate函数参照表中定义（无数据库名称，对应SPL表达式），暂不支持窗口函数。

top n

取前n条记录。

limit n offset m

跳过m条后取n条记录。

其他函数

符合语法规则的集算器函数。

字符串函数：

LOWER(str)

转成小写。

UPPER(str)

转成大写。

LTRIM(str)

删除前导空白。

RTRIM(str)

删除后缀空白。

TRIM(str)

删除两端空白。

SUBSTR(str,start,len)

SUBSTR(str,start)

子串。

LEN(str)

串长。

INDEXOF(sub,str[,start])

子串的位置。

LEFT(str,len)

str中最左边len个字符。

RIGHT(str,len)

str中最右边len个字符。

CONCAT(str1,str2)

连接2个字符串。

CONCAT(s1,s2,…)

连接多个字符串。

REPLACE(str,sub,rplc)

将str中的sub替换成rplc。

数值函数：

ABS(x)

求绝对值。

ACOS(x)

求反余弦。

ASIN(x)

求反正弦。

ATAN(x)

求反正切。

ATAN2(x,y)

求反正切。

CEIL(x)

求大于等于x的最小整数。

COS(x)

求余弦。

EXP(x)

求e的x次幂。

FLOOR(x)

求小于等于x的最大整数。

LN(x)

求自然对数。

LOG10(x)

求以10为底的对数。

MOD(x,m)

x模m。

POWER(x,y)

x的y次幂。

ROUND(x,n)

四舍五入。

SIGN(x)

求符号。

SIN(x)

求正弦。

SQRT(x)

平方根。

TAN(x)

正切。

TRUNC(x,n)

截断。

RAND(seed)

随机数。

时间日期函数：

YEAR(d)

取年。

MONTH(d)

取月。

DAY(d)

取天。

HOUR(d)

取小时。

MINUTE(d)

取分钟。

SECOND(d)

取秒。

QUARTER(d)

取季度。

TODAY()

今天。

NOW()

当前时间。

ADDYEARS(d,n)

增加年数。

ADDMONTHS(d, n)

增加月数。

ADDDAYS(d, n)

增加天数。

ADDHOURS(d, n)

增加小时数。

ADDMINUTES(d, n)

增加分钟数。

ADDSECONDS(d, n)

增加秒数。

DAYOFYEAR(d)

当年中第几天。

WEEKOFYEAR(d)

当年中第几星期。

转换函数：

ASCII(str)

串中最左边字符的ASCII码。

CHR(n)

整数n转换成字符。

INT(x)

字符串或数值转成整数。

DECIMAL(x,len,scale)

字符串或数值转成数值。

TIMESTAMP(str)

yyyy-mm-dd hh24:mi:ss格式串转换成日期。

NUMTOCHAR(d)

数值转成字符串。

DATETOCHAR(date)

将日期转化成yyyy-mm-dd hh24:mi:ss格式串。

其他：

NULLIF(x1,x2)

若x1等于x2返回null，否则返回x1。

COALESCE(x1,…)

返回第一个非null的参数。

A

1

=connect()

2

=A1.query("select * from Persons.txt")

3

=A1.query("select * from D:/Orders.txt")

4

=A1.query("select Id_P, sum(OrderNo) from Orders.csv group by 1 ")

按照Id_P分组：

5

=A1.query("select Id_P, OrderNo from Orders.csv order by 2 ")

按照OrderNo字段排序，默认升序：

6

=A1.query("select * into p1.txt from Persons.csv ")

将查询的结果集写入到p1.txt文件中。

7

=A1.query("select * from Persons.csv where Id_P=? or Id_P>? ",2,2)

查询Id_P大于2或等于2的数据：

8

=A1.query("select * from Persons.csv where Id_P=?1 or Id_P>?2 ",2,2)

查询Id_P大于2或等于2的数据，结果同上，?i表示第i个参数。

9

=A1.query("with persons as (file(\"D:/Persons.btx\").import@b()) select * from persons ")

从Persons.btx文件中查询的结果集 命名为persons，然后再从persons中查询数据，对于大批量的SQL数据，起到优化的作用。

10

=A1.query("select  *  from Persons.txt  P  join Orders.txt  O  on P.Id_P = O.Id_P  ")

多表联合查询：

11

=A1.query("select distinct _file,_ext,_size,_date  from cities.txt")

去重复值查询，获取数据文件的文件名、扩展名、文件大小、文件最后修改时间：

12

=A1.query("select CASE  id_P  when 1  then 'one' when 2 then 'tow' else 'other'  end from p1.txt")

使用case when语句。

13

=A1.query("select * from Persons.btx  where city  like  'N%' ")

使用like语法，查找文件Persons.btx中字段city首字母为N的记录：

14

=A1.query("select * from Persons.btx  where Name  like  '_a_t%' ")

使用like语法，查找文件Persons.btx中字段Name第二个字母为a第四个字母为t的记录：

A

1

=file("score1.txt")

2

=file("score2.txt")

3

=A1.cursor@t()

4

=A2.cursor@t()

5

=[A3, A4].mcursor@t()

6

=connect().query("select CLASS, max(SCORE)  avg_score  from {A5} where SUBJECT='math' group by CLASS")

from {x} 语法，此处x为游标。

A

1

$()select * from Persons.txt

查询数据文件Persons.txt中的数据，结果返回序表。

2

$select * from D:/Orders.txt

使用绝对路径方式查询，结果返回序表。

3

$select * from Persons.csv where Id_P=? or Id_P>?;2,2

查询Id_P大于2或等于2的数据。

4

$select  *  from Persons.txt  P  join Orders.txt  O  on P.Id_P = O.Id_P

多表联合查询。

5

=arg1=create(id,num).record([1,11,2,22])

设置网格变量。

6

$select * from arg1

from v语法，arg1是当前网络内的变量名。

A

1

=create(id,num).record([1,11,2,22])

2

$select * into Num.txt from {A1}

将A1序表中的数据写入到文件Num.txt中。

3

>fname="Num_"+string(date(now()))

4

$select * into {fname}.txt from {A1}

将A1序表中的数据写入到数据文件中，fname为宏，最终生成Num_yyyy-mm-dd.txt名称的文件，yyyy-mm-dd为当前日期。

A

1

$select  *  from  stu.txt

读取学生表数据：

2

$select *  from scores.txt

读取成绩表数据：

3

$select  stu.ID,stu.NAME  from  stu.txt stu  where  exists(select * from scores.txt sco where stu.ID=sco.STUDENTID and score>90)

使用exists语法，筛选出学生表中，在成绩表至少有一门课程成绩超 90 分的学生。

4

$select  stu.ID,stu.NAME  from  stu.txt stu  where  not exists(select * from scores.txt sco where stu.ID=sco.STUDENTID and score>90)

使用not exists语法，从学生表中筛选出所有课程分数都不超过 90 分的学生。