近日在工作中,遇到一个项目,需要将SQL Server中的纪录拆分显示,也就是将一条纪录根据某种方式拆分成多条纪录。比如说在某个帐单系统中,记帐时,按照常规方式按条记;出帐时,要以0.5每纪录的方式进行拆分,即3元的帐单,要拆分成6条纪录,每条0.5元,除金额外,其它字段保持不变。
这是个很有趣的问题,最简陋的方法莫过于使用游标,一条纪录一条纪录的分析并插入。可是,如果纪录数很多(比如上十万条帐单),那肯定是一场恶梦。如何拆分才能使效率最佳化呢?我在网上搜索了一下“SQL 纪录拆分”,竟然还真让我找到了,他用的是JOIN的方式达到拆分效果,看起来效率应该比游标高许多,不过对方是将VarChar字段用","分割进行拆分,我采取拿来主义的方法,对他的方法进行了修改,下面以上面说的帐单系统为例,细说一下:
设定一个帐单基本值,此值即每条出帐纪录的金额。在SELECT中,将帐单的金额减去此基本值乘以一个有限序列数i,其结果可用来表示第i条出帐单的剩余金额,如果得数大于等于0,表示还有剩余的金额,出帐纪录有效;若是小于0,则意味着剩余金额不足,出帐纪录无效。有效的出帐纪录中的金额自然等于帐单基本值,不过这里面还有几个问题:
1、这里只考虑了帐单金额可以被帐单基本值整除的情况,这种情况下,假如帐单基本值是0.5元,自然是每条出帐单0.5元;而假若帐单是3.2元,帐单基本值是0.5元,那么最后一条帐单纪录肯定不是0.5元而是0.2元,所以还需要判断一下剩余金额是否大于0小于帐单基本值,如果是,则剩余金额就是该条出帐单的金额。
2、有限的序列数i,如何产生有限序列数?方法很多,创建一个临时表(或者表变量),1、2、3、4、5……一条一条插入即可产生序列数啦。另一个方法比较有趣,使用某个系统表中的纪录做引子,产生序列数。
下面是SQL示例:
DECLARE
@BaseValue
AS
INT
--
帐单基本值
SET
@BaseValue
=
50
![](/Images/OutliningIndicators/None.gif)
DECLARE
@Fee
TABLE
(ID
INT
IDENTITY
(
1
,
1
), Name
VARCHAR
(
32
), Fee
INT
, FeeTime
DATETIME
)
INSERT
INTO
@Fee
VALUES
(
'
Andy
'
,
300
,
'
2007-01-01 10:22:42
'
)
INSERT
INTO
@Fee
VALUES
(
'
John
'
,
300
,
'
2007-01-01 10:22:32
'
)
INSERT
INTO
@Fee
VALUES
(
'
Lara
'
,
310
,
'
2007-01-01 10:22:22
'
)
INSERT
INTO
@Fee
VALUES
(
'
Philip
'
,
240
,
'
2007-01-01 10:22:52
'
)
![](/Images/OutliningIndicators/None.gif)
DECLARE
@Tmp
TABLE
(ID
INT
IDENTITY
(
0
,
1
), A
INT
)
--
序列数表(必须从0开始)
INSERT
INTO
@Tmp
(A)
SELECT
NULL
FROM
sys.columns
--
用系统表做引子,产生序列数
SELECT
F.ID, F.Name,
'
Fee
'
=
CASE
WHEN
Fee
-
@BaseValue
*
T.ID
>=
@BaseValue
THEN
@BaseValue
ELSE
Fee
-
@BaseValue
*
T.ID
END
,
FeeTime
FROM
@Fee
AS
F
INNER
JOIN
@Tmp
AS
T
ON
Fee
-
@BaseValue
*
T.ID
>
0
ORDER
BY
F.ID, T.ID