排序时将几个指定值按顺序排在最前
例题描述和简单分析
数据库中,有EMPLOYEE 表,数据如下所示:
EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
1 |
Rebecca |
Moore |
F |
California |
1974-11-20 |
2005-03-11 |
R&D |
2 |
Ashley |
Wilson |
F |
New York |
1980-07-19 |
2008-03-16 |
Finance |
3 |
Rachel |
Johnson |
F |
New Mexico |
1970-12-17 |
2010-12-01 |
Sales |
4 |
Emily |
Smith |
F |
Texas |
1985-03-07 |
2006-08-15 |
HR |
5 |
Ashley |
Smith |
F |
Texas |
1975-05-13 |
2004-07-30 |
R&D |
6 |
Matthew |
Johnson |
M |
California |
1984-07-07 |
2005-07-07 |
Sales |
7 |
Alexis |
Smith |
F |
Illinois |
1972-08-16 |
2002-08-16 |
Sales |
8 |
Megan |
Wilson |
F |
California |
1979-04-19 |
1984-04-19 |
Marketing |
9 |
Victoria |
Davis |
F |
Texas |
1983-12-07 |
2009-12-07 |
HR |
10 |
Ryan |
Johnson |
M |
Pennsylvania |
1976-03-12 |
2006-03-12 |
R&D |
需要将数据按EID 排序,顺序为:[9,7,5,3, 其余升序排序…](最前的 9,7,5,3 部分作为参数传入),结果如下所示:
EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
9 |
Victoria |
Davis |
F |
Texas |
1983-12-07 |
2009-12-07 |
HR |
7 |
Alexis |
Smith |
F |
Illinois |
1972-08-16 |
2002-08-16 |
Sales |
5 |
Ashley |
Smith |
F |
Texas |
1975-05-13 |
2004-07-30 |
R&D |
3 |
Rachel |
Johnson |
F |
New Mexico |
1970-12-17 |
2010-12-01 |
Sales |
1 |
Rebecca |
Moore |
F |
California |
1974-11-20 |
2005-03-11 |
R&D |
2 |
Ashley |
Wilson |
F |
New York |
1980-07-19 |
2008-03-16 |
Finance |
4 |
Emily |
Smith |
F |
Texas |
1985-03-07 |
2006-08-15 |
HR |
6 |
Matthew |
Johnson |
M |
California |
1984-07-07 |
2005-07-07 |
Sales |
8 |
Megan |
Wilson |
F |
California |
1979-04-19 |
1984-04-19 |
Marketing |
10 |
Ryan |
Johnson |
M |
Pennsylvania |
1976-03-12 |
2006-03-12 |
R&D |
解法及简要说明
在集算器中编写脚本p1.dfx,如下所示:
A |
|
1 |
=connect("demo") |
2 |
=A1.query@x("SELECT * FROM EMPLOYEE").sort(EID).align@as(eid.split@pc(),EID) |
简要说明:
设脚本参数eid,值为 9,7,5,3
A1 连接数据源名为 demo 的数据库
A2 返回查询结果所组成的序表,结束时自动关闭数据库连接。序表按 EID 排序,再按(参数 eid 用逗号分隔后的数字序列)进行排序,无对应的成员排在最后。
BIRT 集成这段代码的方法可参考:《BIRT 如何调用 SPL 脚本》。
https://www.eclipse.org/forums/index.php/t/1087439/
英文版