有这样一个需求：我们按照 A4 纸设计了一个报表，可是用户的纸张比较宽，所以打印出来后浪费了一半的纸张，客户想让报表中的数据分栏显示。比如原来报表数据为：

序号 ID NAME

------- --------- -------------

1 422_01 yuechaotian1

2 422_02 yuechaotian2

3 422_03 yuechaotian3

4 422_04 yuechaotian4

5 422_05 yuechaotian5

6 422_06 yuechaotian6

7 422_07 yuechaotian7

8 422_08 yuechaotian8

9 422_09 yuechaotian9

10 422_10 yuechaotian10

11 422_11 yuechaotian11

12 422_12 yuechaotian12

13 422_13 yuechaotian13

14 422_14 yuechaotian14

15 422_15 yuechaotian15

16 422_16 yuechaotian16

17 422_17 yuechaotian17

18 422_18 yuechaotian18

浪费了右侧的纸张，所以客户想要这样的效果：

序号   ID         NAME             序号   ID         NAME
---   ---------- --------------    ----   --------- -------------
1   422_01     yuechaotian01      10   422_10     yuechaotian10
2   422_02     yuechaotian02      11   422_11     yuechaotian11
3   422_03     yuechaotian03      12   422_12     yuechaotian12
4   422_04     yuechaotian04      13   422_13     yuechaotian13
5   422_05     yuechaotian05      14   422_14     yuechaotian14
6   422_06     yuechaotian06      15   422_15     yuechaotian15
7   422_07     yuechaotian07      16   422_16     yuechaotian16
8   422_08     yuechaotian08      17   422_17     yuechaotian17
9   422_09     yuechaotian09      18   422_18     yuechaotian18

当然，数据行数是不固定的。试验了一下，用一个 SQL 就可以实现该功能。下面我们一步一步地介绍一下：

1. 创建初始环境

SQL> create table test_yct(id, name) as

2 select '422_0'||rownum, 'yuechaotian'||rownum from dual connect by rownum <10;

表已创建。

SQL> insert into test_yct

2 select '422_1'||(rownum-1), 'yuechaotian1'||(rownum-1) from dual connect by rownum <10;

已创建9行。

SQL> commit;

提交完成。

SQL> select * from test_yct;

ID NAME

----------- ----------------

422_01 yuechaotian1

422_02 yuechaotian2

422_03 yuechaotian3

422_04 yuechaotian4

422_05 yuechaotian5

422_06 yuechaotian6

422_07 yuechaotian7

422_08 yuechaotian8

422_09 yuechaotian9

422_10 yuechaotian10

422_11 yuechaotian11

422_12 yuechaotian12

422_13 yuechaotian13

422_14 yuechaotian14

422_15 yuechaotian15

422_16 yuechaotian16

422_17 yuechaotian17

422_18 yuechaotian18

已选择18行。

2. 实现单行交互显示

首先想到的是通过对 ROWNUM 的计算来实现单行的交互：

SQL> SELECT A.N, A.ID, A.NAME, B.N, B.ID, B.NAME

2 FROM (SELECT N, ID, NAME

3 FROM (SELECT ROWNUM N, ID, NAME FROM test_yct)

4 WHERE MOD(N, 2) = 1) A,

5 (SELECT N, ID, NAME

6 FROM (SELECT ROWNUM N, ID, NAME FROM test_yct)

7 WHERE MOD(N, 2) = 0) B

8 WHERE A.N + 1 = B.N;

N ID NAME N ID NAME

--- -------- ---------------- --- ------- --------------

1 422_01 yuechaotian1 2 422_02 yuechaotian2

3 422_03 yuechaotian3 4 422_04 yuechaotian4

5 422_05 yuechaotian5 6 422_06 yuechaotian6

7 422_07 yuechaotian7 8 422_08 yuechaotian8

9 422_09 yuechaotian9 10 422_10 yuechaotian10

11 422_11 yuechaotian11 12 422_12 yuechaotian12

13 422_13 yuechaotian13 14 422_14 yuechaotian14

15 422_15 yuechaotian15 16 422_16 yuechaotian16

17 422_17 yuechaotian17 18 422_18 yuechaotian18

已选择9行。

3. 实现多行交互显示

用同样的思路，可以实现多行的分栏显示：

SQL> SELECT a.n, A.id, A.name, b.n, B.id, B.name

2 FROM (SELECT N, id, name

3 FROM (SELECT ROWNUM N, id, name FROM test_yct)

4 WHERE MOD(FLOOR((N - 1) / 9), 2) = 0) A,

5 (SELECT N, id, name

6 FROM (SELECT ROWNUM N, id, name FROM test_yct)

7 WHERE MOD(FLOOR((N - 1) / 9), 2) = 1) B

8 WHERE A.N + 9 = B.N;

N   ID       NAME              N   ID      NAME
--   ------- -------------   ---   ------ -------------
1   422_01   yuechaotian1     10   422_10 yuechaotian10
2   422_02   yuechaotian2     11   422_11 yuechaotian11
3   422_03   yuechaotian3     12   422_12 yuechaotian12
4   422_04   yuechaotian4     13   422_13 yuechaotian13
5   422_05   yuechaotian5     14   422_14 yuechaotian14
6   422_06   yuechaotian6     15   422_15 yuechaotian15
7   422_07   yuechaotian7     16   422_16 yuechaotian16
8   422_08   yuechaotian8     17   422_17 yuechaotian17
9   422_09   yuechaotian9     18   422_18 yuechaotian18

已选择9行。

当时没注意，其实上面这个 SQL 有问题，因为这里恰好是18行。如果是19行呢？第19行不会显示的，所以应该加上外连接，才是正确的：

SQL> insert into test_yct values('422_19', 'yuechaotian19');

已创建 1 行。

SQL> commit;

提交完成。

SQL> SELECT a.n, A.id, A.name, b.n, B.id, B.name

2 FROM (SELECT N, id, name

3 FROM (SELECT ROWNUM N, id, name FROM test_yct)

4 WHERE MOD(FLOOR((N - 1) / 9), 2) = 0) A,

5 (SELECT N, id, name

6 FROM (SELECT ROWNUM N, id, name FROM test_yct)

7 WHERE MOD(FLOOR((N - 1) / 9), 2) = 1) B

8 WHERE A.N + 9 = B.N(+);

N ID NAME N ID NAME

-- ------- ------------- --- ------ -------------

1 422_01 yuechaotian1 10 422_10 yuechaotian10

2 422_02 yuechaotian2 11 422_11 yuechaotian11

3 422_03 yuechaotian3 12 422_12 yuechaotian12

4 422_04 yuechaotian4 13 422_13 yuechaotian13

5 422_05 yuechaotian5 14 422_14 yuechaotian14

6 422_06 yuechaotian6 15 422_15 yuechaotian15

7 422_07 yuechaotian7 16 422_16 yuechaotian16

8 422_08 yuechaotian8 17 422_17 yuechaotian17

9 422_09 yuechaotian9 18 422_18 yuechaotian18

19 422_19 yuechaotian19

4. 实现首页带标题的多行交互显示

迁移到 PB 实现的应用程序中时，发现有个问题：用户要求打印的报表中首页是带标题的，而后继页中不带标题。也就是说，报表的首页行数比后继页要少一点，比如首页打印6行，而后继页中打印9行。所以对这个 SQL 又做了一点改进。

我们先多插入几行，这样显示效果比较明显：

SQL> insert into test_yct

2 select '422_2'||(rownum-1), 'yuechaotian2'||(rownum-1)

3 from dual connect by rownum <10;

已创建9行。

SQL> commit;

提交完成。

看看效果：

SQL> SELECT a.n, A.ID, A.NAME, b.n, B.ID, B.NAME

2 FROM (SELECT N, ID, NAME

3 FROM (SELECT ROWNUM N, ID, NAME FROM test_yct)

4 WHERE MOD(FLOOR((N - 1) / 6), 2) = 0) A,

5 (SELECT N, ID, NAME

6 FROM (SELECT ROWNUM N, ID, NAME FROM test_yct)

7 WHERE MOD(FLOOR((N - 1) / 6), 2) = 1) B

8 WHERE A.N + 6 = B.N(+)

9 AND a.n <= 6

10 UNION ALL

11 SELECT a.n, A.ID, A.NAME, b.n, B.ID, B.NAME

12 FROM (SELECT N, ID, NAME

13 FROM (SELECT ROWNUM N, ID, NAME FROM test_yct)

14 WHERE n > 6 * 2

15 AND MOD(FLOOR((N - 6 * 2 -1) / 9), 2) = 0) A,

16 (SELECT N, ID, NAME

17 FROM (SELECT ROWNUM N, ID, NAME FROM test_yct)

18 WHERE n > 6 * 2

19 AND MOD(FLOOR((N - 6 * 2 - 1) / 9), 2) = 1) B

20 WHERE A.N + 9 = B.N(+);

N ID NAME N ID NAME

-- ------- -------------------- --- --- -------- --------

1 422_01 yuechaotian1 7 422_07 yuechaotian7

2 422_02 yuechaotian2 8 422_08 yuechaotian8

3 422_03 yuechaotian3 9 422_09 yuechaotian9

4 422_04 yuechaotian4 10 422_10 yuechaotian10

5 422_05 yuechaotian5 11 422_11 yuechaotian11

6 422_06 yuechaotian6 12 422_12 yuechaotian12

13 422_13 yuechaotian13 22 422_22 yuechaotian22

14 422_14 yuechaotian14 23 422_23 yuechaotian23

15 422_15 yuechaotian15 24 422_24 yuechaotian24

16 422_16 yuechaotian16 25 422_25 yuechaotian25

17 422_17 yuechaotian17 26 422_26 yuechaotian26

18 422_18 yuechaotian18 27 422_27 yuechaotian27

19 422_19 yuechaotian19 28 422_28 yuechaotian28

20 422_20 yuechaotian20

21 422_21 yuechaotian21

已选择15行。

首页的行数（6）和后继页的行数（9）通过参数传递给该 SQL，即实现需要的功能。

用 SQL 实现数据的分栏显示