在进行某些复杂程序开发时候,我们常常遇到这样的问题,在给客户前台显示数据时候,某一条记录后面对应的列数是动态的,而且这一动态的数据受前面维护的数据多少的影响;我们既要对后台分条存储的数据进行一定的处理分析,又要在前台给客户用动态数据列来显示,这真是一个非常挠头的问题。
我曾经研究过某ERP系统,对于平衡核算生成计划时候,就涉及到多列数据的存储问题,他们在后台数据表中设计成了多列形式,比如 deman1,deman2,deman3……,然后利用procedure里面的一个Cursor,然后在loop里面,分别利用变量来处理的;
但是这种方法对于每一条明细的分析处理上非常的复杂,因此我们采用了下面图示的方式存储数据,处理的过程为:
1. 动态维护表C,作为后面的vehi数据来源;
2. 在前台ASP环境中,采用图B的形式来显示和操作数据;
3. 当对图B中的紫色部分进行显示前,利用Sql Server 2000里面的自定义函数(如何处理见后面说明),来动态的在表C取出来,取过来的数据形如’x|y|z’,然后利用Asp中的split函数分组给予动态显示型1~型2……数据
4. 在增加、修改、删除时候,需要按照split分成的数组逐条操作。
序号 | item | name | vehi | retQty | balQty | balDate |
1 | a1 | a1_name | 型1 | 1 | 15 |
|
2 | a1 | a1_name | 型2 | 3 | 6 |
|
3 | b1 | b1_name | 型1 | 1 | 15 |
|
4 | b1 | b1_name | 型2 | 1 | 2 |
|
5 | b2 | b2_name | 型1 | 2 | 30 |
|
6 | b2 | b2_name | 型2 | 2 | 4 |
|
后台数据存储 表A
序号 | vehi | Qty | addDate |
1 | 型1 | 15 |
|
2 | 型2 | 2 |
|
后台数据存储 表C
序号 | item | name | 型1 | 型2 | |||
retQty | balQty | retQty | balQty |
| |||
1 | a1 | a1_name | 1 | 15 | 3 | 6 |
|
2 | b1 | b1_name | 1 | 15 | 1 | 2 |
|
3 | b2 | b2_name | 2 | 30 | 2 | 4 |
|
前台动态显示 图B
因为以前用户是利用Excel来操作整理数据,就是采用了图B中的形式来进行数据操作,如果采用表A中的形式在前台页面操作数据,当对应的表C中的vehi数据列较多时候,就数倍的增加了工作量,“用户就是上帝”哈哈,没办法,我们只好采用了上面的方法,最后附上部分的后台处理和前台页面显示部分的代码仅供参考,前台asp代码非常感谢S仔帮忙。
后记:在实际应用中,当图B中存在500条记录,利用SqlServer2000里面的自定义函数返回的表中有二十几个字段,存在几个“型1~型2……”的时候,前台速度为1/3秒左右,实际应用效果十分理想,哈哈。欢迎交流 MSN: w_lin_s@hotmail.com
1. Sql Server自定义函数,实现将分条的数据,按照某几个字段分组,将后面动态的数据合并成‘x|y|z’的形式,
/*
** add by wLs For: convert many lines of Balance datas to one char group by item
*/
CREATE function xzc.fn_getContent_Bala
(@s_p_no int = NULL)
returns @fn_getContent_Balance TABLE
(ID varchar(8000),
item varchar(20),
name varchar(50),
retQty varchar(10),
balQty varchar(10),
…… --省去变量定义部分
/*Outer CURSOR*/
DECLARE getItemBalance CURSOR SCROLL FOR
select item,name from (
select min(ID) as ID, item,name
from Balance where SupplyPlanNo=@s_p_no
group by item,name
) a order by a.ID desc
/* --注意,cursor里面如果需要某种特殊的排序,比如上面的ID的话,只能采用上面这种的方法,因为cursor要求order by后面的字段必须出现在前面的select中,这会影响我们的distinct取值
*/
if @s_p_no IS NULL or @s_p_no = ''
begin
RETURN
end
OPEN getItemBalance
FETCH first from getItemBalance into @sItem,@sName
while(@@fetch_status = 0)
BEGIN
/*Inner CURSOR*/
DECLARE getQtyBalance CURSOR SCROLL FOR
select ID, RatQty, BalQty from Balance
where SupplyPlanNo = @s_p_no and Item = @sItem and Name = @sName order by ID
OPEN getQtyBalance
FETCH first from getQtyBalance into @iID,@iRatQty,@iBalQty
while(@@fetch_status = 0)
BEGIN
select @allID = @allID + '|' + convert(varchar(15), @iID)
select @allRationQty = @allRationQty + '|' + convert(varchar(25), @iRationQty)
select @allBalanceQty = @allBalanceQty + '|' + convert(varchar(25), @iBalanceQty)
FETCH next from getQtyBalance into @iID,@iRatQty,@iBalQty
END
CLOSE getQtyBalance
DEALLOCATE getQtyBalance
-- 利用自写function去除形成字段前后的’|’字符
select @allID = xzc.trim_char(@allID, '|')
select @allRationQty = xzc.trim_char(@allRationQty, '|')
select @allBalanceQty = xzc.trim_char(@allBalanceQty, '|')
INSERT INTO @fn_getContent_Balance VALUES(@allID, @sItem,@sName, @allRatQty,@allBalQty)
select @allRationQty = ''
select @allID = ''
select @allBalanceQty = ''
FETCH next from getItemBalance into @sItem,@sName
END
CLOSE getItemBalance
DEALLOCATE getItemBalance
RETURN
END
2.前台asp代码部分,根据返回的‘x|y|z’形式分组,然后实现前台动态列的数据显示;主要步骤分为两步:(1)要首先动态的利用自定义函数从表C中取出来vehi数据名的集合数组在标题栏显示,并将其保存在变量中,当后面的数据新增修改删除时候,需要传过去该变量作为动态数据列数的来源依据;(2)对应数据显示部分,利用自定义函数返回表A中的与vehi对应的retQty和balQty等数据的集合数组,然后动态显示,从而形成动态数据列的对应,具体可参看图B中的的数据显示。
需要注意的是:当新增修改删除时候,因为我们这里将前台页面的多条动态数据列分条存储到了后台数据库,需要循环操作,同时需要注意疏组下标越界问题,而且因为网络或其他不可预知问题,偶尔会出现,个别的动态数据列的数值丢失问题。
同时因为涉及到了这种得动态数据列形成‘x|y|z’形式的分组问题,需要注意前台页面的“刷新”导致的数据重复问题,目前我们采用的是根据关键字段建立唯一约束的方法来解决。
代码以新增部分为例:
//新增函数
if (flag="addnew") then
b_vehicle=split(vehicle,"|")
b_ratqty=split(ratqty,"|")
b_balqty=split(balqty,"|")
bcount=ubound(b_vehicle)
for i=0 to bcount
sql="insert into balance(Item, Name,ItemSpec, RatQty,BalQty)"
……
'response.write sql&"<br>"
g_conn.execute(sql)
next
end if