有这样一个需求:我们按照 A4
纸设计了一个报表,可是用户的纸张比较宽,所以打印出来后浪费了一半的纸张,客户想让报表中的数据分栏显示。比如原来报表数据为:
序号IDNAME
------- ---------
-------------
1 422_01yuechaotian1
2 422_02yuechaotian2
3 422_03yuechaotian3
4 422_04yuechaotian4
5 422_05yuechaotian5
6 422_06yuechaotian6
7 422_07yuechaotian7
8 422_08yuechaotian8
9 422_09yuechaotian9
10 422_10yuechaotian10
11 422_11yuechaotian11
12 422_12yuechaotian12
13 422_13yuechaotian13
14 422_14yuechaotian14
15 422_15yuechaotian15
16 422_16yuechaotian16
17 422_17yuechaotian17
18 422_18yuechaotian18
浪费了右侧的纸张,所以客户想要这样的效果:序号 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
2select '422_0'||rownum, 'yuechaotian'||rownum from dual connect by rownum
<10;
表已创建。
SQL> insert into
test_yct
2select '422_1'||(rownum-1), 'yuechaotian1'||(rownum-1) from dual connect
by rownum <10;
已创建9行。
SQL>
commit;
提交完成。
SQL> select * from
test_yct;
IDNAME
-----------
----------------
422_01yuechaotian1
422_02yuechaotian2
422_03yuechaotian3
422_04yuechaotian4
422_05yuechaotian5
422_06yuechaotian6
422_07yuechaotian7
422_08yuechaotian8
422_09yuechaotian9
422_10yuechaotian10
422_11yuechaotian11
422_12yuechaotian12
422_13yuechaotian13
422_14yuechaotian14
422_15yuechaotian15
422_16yuechaotian16
422_17yuechaotian17
422_18yuechaotian18
已选择18行。
2. 实现单行交互显示
首先想到的是通过对 ROWNUM 的计算来实现单行的交互:
SQL> SELECT A.N, A.ID,
A.NAME, B.N, B.ID, B.NAME
2FROM (SELECT N, ID, NAME
3FROM (SELECT ROWNUM N, ID, NAME FROM
test_yct)
4WHERE MOD(N, 2) = 1) A,
5(SELECT N, ID, NAME
6FROM (SELECT ROWNUM N, ID, NAME FROM
test_yct)
7WHERE MOD(N, 2) = 0) B
8WHERE A.N + 1 = B.N;
N IDNAMENIDNAME
--- -------- ----------------
----------
--------------
1 422_01yuechaotian12422_02yuechaotian2
3 422_03yuechaotian34422_04yuechaotian4
5 422_05yuechaotian56422_06yuechaotian6
7 422_07yuechaotian78422_08yuechaotian8
9 422_09yuechaotian910422_10yuechaotian10
11 422_11yuechaotian1112422_12yuechaotian12
13 422_13yuechaotian1314422_14yuechaotian14
15 422_15yuechaotian1516422_16yuechaotian16
17 422_17yuechaotian1718422_18yuechaotian18
已选择9行。
3. 实现多行交互显示
用同样的思路,可以实现多行的分栏显示:
SQL> SELECT a.n, A.id,
A.name, b.n, B.id, B.name
2FROM (SELECT N, id, name
3FROM (SELECT ROWNUM N, id, name FROM
test_yct)
4WHERE MOD(FLOOR((N - 1) / 9), 2) = 0) A,
5(SELECT N, id, name
6FROM (SELECT ROWNUM N, id, name FROM
test_yct)
7WHERE MOD(FLOOR((N - 1) / 9), 2) = 1) B
8WHERE 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
2FROM (SELECT N, id, name
3FROM (SELECT ROWNUM N, id, name FROM
test_yct)
4WHERE MOD(FLOOR((N - 1) / 9), 2) = 0) A,
5(SELECT N, id, name
6FROM (SELECT ROWNUM N, id, name FROM
test_yct)
7WHERE MOD(FLOOR((N - 1) / 9), 2) = 1) B
8WHERE A.N + 9 = B.N(+);
NIDNAMENIDNAME
--------------------------------------------
1422_01yuechaotian110422_10yuechaotian10
2422_02yuechaotian211422_11yuechaotian11
3422_03yuechaotian312422_12yuechaotian12
4422_04yuechaotian413422_13yuechaotian13
5422_05yuechaotian514422_14yuechaotian14
6422_06yuechaotian615422_15yuechaotian15
7422_07yuechaotian716422_16yuechaotian16
8422_08yuechaotian817422_17yuechaotian17
9422_09yuechaotian918422_18yuechaotian18
19422_19yuechaotian19
4.
实现首页带标题的多行交互显示
迁移到 PB
实现的应用程序中时,发现有个问题:用户要求打印的报表中首页是带标题的,而后继页中不带标题。也就是说,报表的首页行数比后继页要少一点,比如首页打印6行,而后继页中打印9行。所以对这个
SQL 又做了一点改进。
我们先多插入几行,这样显示效果比较明显:SQL> insert into
test_yct
2select '422_2'||(rownum-1),
'yuechaotian2'||(rownum-1)
3from dual connect by rownum <10;
已创建9行。
SQL>
commit;
提交完成。
看看效果:SQL> SELECT a.n, A.ID,
A.NAME, b.n, B.ID, B.NAME
2FROM (SELECT N, ID, NAME
3FROM (SELECT ROWNUM N, ID, NAME FROM
test_yct)
4WHERE MOD(FLOOR((N - 1) / 6), 2) = 0) A,
5(SELECT N, ID, NAME
6FROM (SELECT ROWNUM N, ID, NAME FROM
test_yct)
7WHERE MOD(FLOOR((N - 1) / 6), 2)
= 1) B
8WHERE A.N + 6 = B.N(+)
9AND a.n <= 6
10UNION ALL
11SELECT a.n, A.ID, A.NAME, b.n, B.ID, B.NAME
12FROM (SELECT N, ID, NAME
13FROM (SELECT ROWNUM N, ID, NAME FROM
test_yct)
14WHERE n > 6 *
2
15AND MOD(FLOOR((N - 6 * 2 -1) / 9), 2) = 0)
A,
16(SELECT N, ID, NAME
17FROM (SELECT ROWNUM N, ID, NAME FROM
test_yct)
18WHERE n > 6 * 2
19AND MOD(FLOOR((N - 6 * 2 - 1) /
9), 2) = 1) B
20WHERE A.N + 9 = B.N(+);
NIDNAMENIDNAME
-------------------------------- --- --------
--------
1422_01yuechaotian17422_07yuechaotian7
2422_02yuechaotian28422_08yuechaotian8
3422_03yuechaotian39422_09yuechaotian9
4422_04yuechaotian410422_10yuechaotian10
5422_05yuechaotian511422_11yuechaotian11
6422_06yuechaotian612422_12yuechaotian12
13422_13yuechaotian1322422_22yuechaotian22
14422_14yuechaotian1423422_23yuechaotian23
15422_15yuechaotian1524422_24yuechaotian24
16422_16yuechaotian1625422_25yuechaotian25
17422_17yuechaotian1726422_26yuechaotian26
18422_18yuechaotian1827422_27yuechaotian27
19422_19yuechaotian1928422_28yuechaotian28
20422_20yuechaotian20
21422_21yuechaotian21
已选择15行。
首页的行数(6)和后继页的行数(9)通过参数传递给该
SQL,即实现需要的功能。