使用位置选出子集

将学生按成绩蛇行分作两班，即1、4、5、8、…为一班，2、3、6、7、…为二班

SQL解法

一班

select 姓名,成绩
from (select 姓名,成绩,
            row_number() over(order by绩 desc) 排名
      from 成绩表)
where mod(排名,4)=0 or mod(排名,4)=1 order by 排名

二班

select 姓名,成绩
from (select 姓名,成绩,
            row_number() over(order by 成绩 desc) 排名
      from 成绩表)
where mod(排名,4)=2 or mod(排名,4)=3 order by 排名

SQL的无序集合机制必须借助子查询制造出一个序号才能完成相关的位置访问。

SPL解法

A
1 =demo.query("select * from 成绩表").sort(成绩:-1)
2 =A1.step(4,1,4) 一班
3 =A1.step(4,2,3) 二班

SPL中根据位置访问成员很轻松。

Class 1:

    SELECT name,score
    FROM (SELECT name,score,
                 row_number() OVER(ORDER BY score desc) score_rank
          FROM score_table)
    WHERE mod(score_rank,4)=0 or mod(score_rank,4)=1
    ORDER BY score_rank

Class 2:

    SELECT name,score
    FROM (SELECT name,score,
                 row_number() OVER(ORDER BY score desc) score_rank
          FROM score_table)
    WHERE mod(score_rank,4)=2 or mod(score_rank,4)=3
    ORDER BY score_rank

The SQL record set is in random order, so a subquery is required to create the sequence number for rows before accessing the desired position.

SPL

A
1 =demo.query("select * from score_table").sort(score:-1)
2 =A1.step(4,1,4) Class 1
3 =A1.step(4,2,3) Class 2

SPL handles this task with ease as it can access the record directly according to the positions.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.