一般数据检索
本章主要展示关于一般数据检索的代码参考，比如SELECT * FROM …、WHERE …、SELECT … FROM、AS、常数表、ORDER BY/ASC/DESC、DISTINCT、FISRT/LAST/TOP/BOTTOM、 UNION/UNION ALL/INTERSECT/MINUS、 SELECT … FROM (SELECT … )、SELECT (SELECT … FROM) FROM、 CURSOR/FETCH等。
SELECT * FROM …
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
WHERE …
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.select(SALARY>5000) |
|
SELECT … FROM
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.new(EID,NAME) |
|
AS
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.new(EID:EmployeeNo,NAME+" "+SURNAME: EmployeeName) |
|
常数表
|
|
A |
|
|
1 |
=[[1,"Beverage"]].new(~(1):TypeNumber,~(2):Name) |
创建1条记录的序表 |
|
2 |
=create(TypeNumber,TypeName).record([1,"Beverage",2,"Grain"]) |
先创建空序表后填数 |
ORDER BY/ASC/DESC
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.sort(BIRTHDAY,-SALARY) |
|
DISTINCT
|
|
A |
|
|
1 |
=demo.query("select * from SALES") |
|
|
2 |
=A1.id(CLIENT) |
取唯一值 |
|
3 |
=A1.(CLIENT) |
所有出现值 |
|
4 |
=A1.([CLIENT,SELLERID]) |
组合的所有出现值 |
FISRT/LAST/TOP/BOTTOM
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.m(1).NAME |
FIRST |
|
3 |
=A1.m(-1).NAME |
LAST |
|
4 |
=A1.m(to(3)) |
TOP 3 |
|
5 |
=A1.to(-3) |
BOTTOM 3 |
UNION/UNION ALL/INTERSECT/MINUS
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.select(DEPT=="Sales" || DEPT=="R&D") |
|
|
3 |
=A1.select(SALARY>5000) |
|
|
4 |
=A2|A3 |
UNION ALL |
|
5 |
=A2&A3 |
UNION |
|
6 |
=A2^A3 |
INTERSECTION |
|
7 |
=A2\A3 |
DIFFERENCE |
SELECT … FROM (SELECT … )
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=A1.select(DEPT=="Sales") |
执行查询 |
|
3 |
=A2.count() |
对结果集再统计 |
SELECT (SELECT … FROM) FROM
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=demo.query("select * from FAMILY") |
|
|
3 |
=A1.derive() |
|
|
4 |
=A1.run(EID=A2.select(EID:A1.EID)) |
先计算出子表引用 |
|
5 |
=A1.new(NAME,EID.count():NumberOfMembers) |
|
|
6 |
=A3.new(NAME,A2.select(EID:A3.EID).count():NumberOfMembers) |
直接写的方法 |
CURSOR/FETCH
|
|
A |
B |
C |
|
|
1 |
=demo.cursor("select * from SALES") |
|
|
|
|
2 |
for |
|
|
|
|
3 |
|
=A1.fetch(100) |
|
每次取100条 |
|
4 |
|
if B3==null |
break |
|
|
5 |
|
… |
|
|