| 我的分类 |
 |
|
|
|
|
|
|
用 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，即实现需要的功能。
|
|
|
发表于： 2008-05-29，修改于： 2008-05-29 08:27 已浏览495次，有评论1条
推荐
投诉
|
|
|
|
网友评论 |
|
本站网友 | 时间：2008-07-01 15:49:26 IP地址：120.7.162.★ |
|
|
如果某张表中有100条记录，第一栏显示88条数据，第二栏显示12条记录，空缺的部分用0补齐，应该怎么写，请赐教，万分感谢！！
|
|
Blog作者的回复:
-- 1. 创建测试表
SQL> create table test_tyc(id, name) as
2 select '422_0'||rownum, 'yuechaotian'||rownum from dual connect by rownum <101;
表已创建。
SQL> set pagesize 1000
SQL> col id format a10
SQL> col name format a20
SQL> select * from test_tyc;
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_095 yuechaotian95
422_096 yuechaotian96
422_097 yuechaotian97
422_098 yuechaotian98
422_099 yuechaotian99
422_0100 yuechaotian100
已选择100行。
--2. 查询 SQL 如下（如果每页不是显示88行，只要把下面的4个'88'替换成你需要显示的行数即可）
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_tyc)
4 WHERE MOD(FLOOR((N - 1) / 88), 2) = 0) A,
5 (SELECT N, id, name
6 FROM (SELECT ROWNUM N, id, name FROM test_tyc)
7 WHERE MOD(FLOOR((N - 1) / 88), 2) = 1
8 UNION ALL
9 SELECT ROWNUM + (SELECT COUNT(*) FROM test_tyc) n, '0', '0'
10 FROM dual
11 CONNECT BY ROWNUM < 88 + 1) B
12 WHERE A.N + 88 = B.N(+)
13 ORDER BY a.n;
N ID NAME N ID NAME
---------- ---------- -------------------- ---------- ---------- ------------------
1 422_01 yuechaotian1 89 422_089 yuechaotian89
2 422_02 yuechaotian2 90 422_090 yuechaotian90
3 422_03 yuechaotian3 91 422_091 yuechaotian91
4 422_04 yuechaotian4 92 422_092 yuechaotian92
5 422_05 yuechaotian5 93 422_093 yuechaotian93
6 422_06 yuechaotian6 94 422_094 yuechaotian94
7 422_07 yuechaotian7 95 422_095 yuechaotian95
8 422_08 yuechaotian8 96 422_096 yuechaotian96
9 422_09 yuechaotian9 97 422_097 yuechaotian97
10 422_010 yuechaotian10 98 422_098 yuechaotian98
11 422_011 yuechaotian11 99 422_099 yuechaotian99
12 422_012 yuechaotian12 100 422_0100 yuechaotian100
13 422_013 yuechaotian13 101 0 0
14 422_014 yuechaotian14 102 0 0
15 422_015 yuechaotian15 103 0 0
16 422_016 yuechaotian16 104 0 0
17 422_017 yuechaotian17 105 0 0
18 422_018 yuechaotian18 106 0 0
19 422_019 yuechaotian19 107 0 0
20 422_020 yuechaotian20 108 0 0
21 422_021 yuechaotian21 109 0 0
22 422_022 yuechaotian22 110 0 0
23 422_023 yuechaotian23 111 0 0
24 422_024 yuechaotian24 112 0 0
25 422_025 yuechaotian25 113 0 0
26 422_026 yuechaotian26 114 0 0
27 422_027 yuechaotian27 115 0 0
28 422_028 yuechaotian28 116 0 0
29 422_029 yuechaotian29 117 0 0
30 422_030 yuechaotian30 118 0 0
31 422_031 yuechaotian31 119 0 0
32 422_032 yuechaotian32 120 0 0
33 422_033 yuechaotian33 121 0 0
34 422_034 yuechaotian34 122 0 0
35 422_035 yuechaotian35 123 0 0
36 422_036 yuechaotian36 124 0 0
37 422_037 yuechaotian37 125 0 0
38 422_038 yuechaotian38 126 0 0
39 422_039 yuechaotian39 127 0 0
40 422_040 yuechaotian40 128 0 0
41 422_041 yuechaotian41 129 0 0
42 422_042 yuechaotian42 130 0 0
43 422_043 yuechaotian43 131 0 0
44 422_044 yuechaotian44 132 0 0
45 422_045 yuechaotian45 133 0 0
46 422_046 yuechaotian46 134 0 0
47 422_047 yuechaotian47 135 0 0
48 422_048 yuechaotian48 136 0 0
49 422_049 yuechaotian49 137 0 0
50 422_050 yuechaotian50 138 0 0
51 422_051 yuechaotian51 139 0 0
52 422_052 yuechaotian52 140 0 0
53 422_053 yuechaotian53 141 0 0
54 422_054 yuechaotian54 142 0 0
55 422_055 yuechaotian55 143 0 0
56 422_056 yuechaotian56 144 0 0
57 422_057 yuechaotian57 145 0 0
58 422_058 yuechaotian58 146 0 0
59 422_059 yuechaotian59 147 0 0
60 422_060 yuechaotian60 148 0 0
61 422_061 yuechaotian61 149 0 0
62 422_062 yuechaotian62 150 0 0
63 422_063 yuechaotian63 151 0 0
64 422_064 yuechaotian64 152 0 0
65 422_065 yuechaotian65 153 0 0
66 422_066 yuechaotian66 154 0 0
67 422_067 yuechaotian67 155 0 0
68 422_068 yuechaotian68 156 0 0
69 422_069 yuechaotian69 157 0 0
70 422_070 yuechaotian70 158 0 0
71 422_071 yuechaotian71 159 0 0
72 422_072 yuechaotian72 160 0 0
73 422_073 yuechaotian73 161 0 0
74 422_074 yuechaotian74 162 0 0
75 422_075 yuechaotian75 163 0 0
76 422_076 yuechaotian76 164 0 0
77 422_077 yuechaotian77 165 0 0
78 422_078 yuechaotian78 166 0 0
79 422_079 yuechaotian79 167 0 0
80 422_080 yuechaotian80 168 0 0
81 422_081 yuechaotian81 169 0 0
82 422_082 yuechaotian82 170 0 0
83 422_083 yuechaotian83 171 0 0
84 422_084 yuechaotian84 172 0 0
85 422_085 yuechaotian85 173 0 0
86 422_086 yuechaotian86 174 0 0
87 422_087 yuechaotian87 175 0 0
88 422_088 yuechaotian88 176 0 0
已选择88行。
|
|
|
|
|
| |
如果你的工作，机器也可以做。那总有一天，你也要变成机器。