A. merge()
Description:
Merge multiple table sequences/record sequences.
Syntax:
A.merge (xi,…)
Note:
The function merges members of sequence A according to xi field in order.
By default, A(i) is treated as ordered by [xi,…]; when xi is absent and when no primary key is set for A(i) or A(i) is unordered, use @o option to simply concatenate members.
Parameter:
|
A |
A sequence made up of multiple table sequences/record sequences of the same structure. |
|
xi |
A field of A(i); if performing merge by multiple fields, use the comma to separate them, for example, x1,x2...; xi is by default the primary key of为A(i) . |
Option:
|
@u |
Remove the duplicates from the table sequence/record sequence generated from unioning members of A(i)s in certain order; records with same xi have same corresponding members of A(i). |
|
@i |
Return a table sequence/record sequence composed of the common members of A(i)s. |
|
@d |
Generate a new table sequence/record sequence by removing members of A(2)&…A(n) from A(1). |
|
@o |
Do not assume that A(i) is ordered by [xi,…], and thus simply concatenate members instead of performing the merge operation. |
|
@0 |
Put records with null values at the end. |
|
@x |
Remove common members of A(i) and union the other members to generate a new table sequence/sequence. |
Return value:
Table sequence/record sequence
Example:
Order-based MERGE:
|
|
A |
|
|
1 |
=demo.query("select top 6 * from DEPT order by FATHER ").sort(DEPTID,DEPTNAME) |
Return a table sequence ordered by DEPTID and DEPTNAME:
|
|
2 |
=demo.query("select * from DEPT where DEPTID <8 order by FATHER").sort(DEPTID,DEPTNAME) |
Return a table sequence ordered by DEPTID and DEPTNAME:
|
|
3 |
=[A1,A2].merge(DEPTID,DEPTNAME) |
Merge A1 and A2 according to DEPTID and DEPTNAME in order:
|
MERGE by the primary key when xi is by default the primary key of A(i):
|
|
A |
|
|
1 |
=connect("demo").query("select top 6 * from DEPT order by FATHER ").keys(DEPTID).sort(DEPTID) |
Return a table sequence using DEPTID as the primary key:
|
|
2 |
=connect("demo").query("select * from DEPT where DEPTID <8 order by FATHER").keys(DEPTID).sort(DEPTID) |
Return a table sequence using DEPTID as the primary key:
|
|
3 |
=[A1,A2].merge() |
Merge A1 and A2 according to primary key DEPTID as parameter xi is absent:
|
Other methods of MERGE:
|
|
A |
|
|
1 |
=connect("demo").query("select top 6 * from DEPT order by FATHER ").keys(DEPTID).sort(DEPTID) |
Return a table sequence using DEPTID as the primary key:
|
|
2 |
=connect("demo").query("select * from DEPT where DEPTID <8 order by FATHER").keys(DEPTID).sort(DEPTID) |
Return a table sequence using DEPTID as the primary key:
|
|
3 |
=[A1,A2].merge@u() |
Merge A1 and A2 according to primary key DEPTID as parameter xi is absent, and use @u option to remove duplicate records having same DEPTID:
|
|
4 |
=[A1,A2].merge@i() |
Merge A1 and A2 according to primary key DEPTID as parameter xi is absent, and use @i option to retain only common members of A1 and A2:
|
|
5 |
=[A1,A2].merge@d() |
Merge A1 and A2 according to primary key DEPTID as parameter xi is absent, and use @d option to return a sequence of members of A2 from which members of A1 is removed:
|
|
6 |
=[A1,A2].merge@x() |
Merge A1 and A2 according to primary key DEPTID as parameter xi is absent, and use @x option to return a sequence of members of both A1 and A2 from which their common members are removed:
|
Use @o option to simply concatenate records instead of performing the order-based MERGE:
|
|
A |
|
|
1 |
=connect("demo").query("select top 6 * from DEPT order by FATHER ") |
Return a table sequence:
|
|
2 |
=connect("demo").query("select * from DEPT where DEPTID <8 order by FATHER") |
Return a table sequence:
|
|
3 |
=[A1,A2].merge@o() |
Records of A1 and A2 are unordered; use @o option to simly concatenate them without sorting:
|
Use @0 option to place records whose primary keys are null at the end:
|
|
A |
|
|
1 |
=connect("demo").query("select top 6 * from DEPT order by FATHER ").keys(DEPTID).sort(DEPTID) |
Return a table sequence:
|
|
2 |
=connect("demo").query("select * from DEPT where DEPTID <8 order by FATHER").keys(DEPTID).sort(DEPTID) |
|
|
3 |
=A2(7).modify(null:DEPTID) |
Now this is data of A2’s table sequence:
|
|
4 |
=[A1,A2].merge() |
|
|
5 |
=[A1,A2].merge@0() |
Merge A1 and A2 according to primary key DEPTID as parameter xi is absent, and use @0 option to place records where DEPTID is null at the end:
|
