用 SQL 实现数据的分栏显示

有这样一个需求：我们按照 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，即实现需要的功能。