本人工作单位是一家橡塑产品制造企业，05年2月单位领导要求我开发一个生产流程数据核对表，用来稽核流程数据的准确性，其报表基本原理如下：
后续工序今日正品数 + 后续工序今日废品数 = 本工序今日正品数 + 本工序今日留存数 - 本工序昨日留存数
了解了原理后马上开工，第一次考虑用临时表：
select distinct LEFT(productid, 4) + '00' as productid into #productid from yuancaiFlow_view
where productiondate = '2004-3-1'
and productid like '01____'
go
select a.productid,
b.productname,
isnull(hj.GoodQty, 0) as hj_good,
isnull(hjtoday.qty, 0) as hj_today,
isnull(hjyesterday.qty, 0) hj_yesterday,
isnull(hj.GoodQty, 0) - isnull(hjtoday.qty, 0) + isnull(hjyesterday.qty, 0) - isnull(sx.GoodQty, 0) - isnull(sx.BadQty, 0) as hj_difference,
isnull(sx.GoodQty, 0) as sx_good,
isnull(sx.BadQty, 0) as sx_bad,
isnull(sxtoday.qty, 0) as sx_today,
isnull(sxyesterday.qty, 0) sx_yesterday,
isnull(sx.GoodQty, 0) - isnull(sxtoday.qty, 0) + isnull(sxyesterday.qty, 0) - isnull(jt.GoodQty, 0) - isnull(jt.BadQty, 0) as sx_difference,
isnull(jt.GoodQty, 0) as jt_good,
isnull(jt.BadQty, 0) as jt_bad,
isnull(jttoday.qty, 0) as jt_today,
isnull(jtyesterday.qty, 0) jt_yesterday,
isnull(jt.GoodQty, 0) - isnull(jttoday.qty, 0) + isnull(jtyesterday.qty, 0) - isnull(ph.GoodQty, 0) - isnull(ph.BadQty, 0) as jt_difference,
isnull(ph.GoodQty, 0) as ph_good,
isnull(ph.BadQty, 0) as ph_bad,
isnull(phtoday.qty, 0) as ph_today,
isnull(phyesterday.qty, 0) ph_yesterday,
isnull(ph.GoodQty, 0) - isnull(phtoday.qty, 0) + isnull(phyesterday.qty, 0) - isnull(zj.GoodQty, 0) - isnull(zj.BadQty, 0) as ph_difference,
isnull(zj.GoodQty, 0) as zj_good,
isnull(zj.BadQty, 0) as zj_bad
from #productid as a,
finishedproduct as b,
yuancaiFlow_view as hj,
basflowsubsistence as hjtoday,
basflowsubsistence as hjyesterday,
yuancaiFlow_view as sx,
basflowsubsistence as sxtoday,
basflowsubsistence as sxyesterday,
yuancaiFlow_view as jt,
basflowsubsistence as jttoday,
basflowsubsistence as jtyesterday,
yuancaiFlow_view as ph,
basflowsubsistence as phtoday,
basflowsubsistence as phyesterday,
yuancaiFlow_view as zj
where a.productid = b.productid
--hj
and hj.productiondate = '2004-3-1'
and left(a.productid, 4) *= left(hj.productid, 4)
and hj.productid like '____06'
and left(a.productid, 4) + '06' *= hjtoday.productid
and hjtoday.checkdate = '2004-3-1'
and left(a.productid, 4) + '06' *= hjyesterday.productid
and hjyesterday.checkdate = '2004-2-29'
--sx
and sx.productiondate = '2004-3-1'
and left(a.productid, 4) *= left(sx.productid, 4)
and sx.productid like '____08'
and left(a.productid, 4) + '08' *= sxtoday.productid
and sxtoday.checkdate = '2004-3-1'
and left(a.productid, 4) + '08' *= sxyesterday.productid
and sxyesterday.checkdate = '2004-2-29'
--jt
and jt.productiondate = '2004-3-1'
and left(a.productid, 4) *= left(jt.productid, 4)
and jt.productid like '____07'
and left(a.productid, 4) + '07' *= jttoday.productid
and jttoday.checkdate = '2004-3-1'
and left(a.productid, 4) + '07' *= jtyesterday.productid
and jtyesterday.checkdate = '2004-2-29'
--ph
and ph.productiondate = '2004-3-1'
and left(a.productid, 4) *= left(ph.productid, 4)
and ph.productid like '____09'
and left(a.productid, 4) + '09' *= phtoday.productid
and phtoday.checkdate = '2004-3-1'
and left(a.productid, 4) + '09' *= phyesterday.productid
and phyesterday.checkdate = '2004-2-29'
--zj
and zj.productiondate = '2004-3-1'
and left(a.productid, 4) *= left(zj.productid, 4)
and zj.productid like '01__11'
go
drop table #productid
go
准备用ASP呈现报表，结果ASP端的Recordset老提示出错。只好改用函数：
-- =============================================
-- Create table function (TF)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fn_GetGLSubsistenceList')
DROP FUNCTION fn_GetGLSubsistenceList
GO
CREATE FUNCTION fn_GetGLSubsistenceList
(@CheckDate smalldatetime)
RETURNS @FlowSubsistenceList TABLE
(ProductID nvarchar(6),
ProductName nvarchar(32),
HJGoodQty real,
HJTodayQty real,
HJYesterdayQty real,
HJDefference real,
SXGoodQty real,
SXBadQty real,
SXTodayQty real,
SXYesterdayQty real,
SXDefference real,
JTGoodQty real,
JTBadQty real,
JTTodayQty real,
JTYesterdayQty real,
JTDefference real,
PHGoodQty real,
PHBadQty real,
PHTodayQty real,
PHYesterdayQty real,
PHDefference real,
ZJGoodQty real,
ZJBadQty real)
AS
BEGIN
DECLARE @ProductIDList TABLE
(ProductID nvarchar(6))
DECLARE @YesterdayOfCheckDate smalldatetime
SET @YesterdayOfCheckDate = DATEADD(day, -1, @CheckDate)
--保存该日生产的产品ID号
INSERT @ProductIDList
select distinct LEFT(productid, 4) + '00' as productid from yuancaiFlow_view
where productiondate = @CheckDate
and productid like '01____'
--正常工序为hj->sx->jt->ph->zj
INSERT @FlowSubsistenceList
select a.productid,
b.productname,
isnull(hj.GoodQty, 0) as hj_good,
isnull(hjtoday.qty, 0) as hj_today,
isnull(hjyesterday.qty, 0) hj_yesterday,
0 - (isnull(hj.GoodQty, 0) - isnull(hjtoday.qty, 0) + isnull(hjyesterday.qty, 0) - isnull(sx.GoodQty, 0) - isnull(sx.BadQty, 0)) as hj_difference,
isnull(sx.GoodQty, 0) as sx_good,
isnull(sx.BadQty, 0) as sx_bad,
isnull(sxtoday.qty, 0) as sx_today,
isnull(sxyesterday.qty, 0) sx_yesterday,
0 - (isnull(sx.GoodQty, 0) - isnull(sxtoday.qty, 0) + isnull(sxyesterday.qty, 0) - isnull(jt.GoodQty, 0) - isnull(jt.BadQty, 0)) as sx_difference,
isnull(jt.GoodQty, 0) as jt_good,
isnull(jt.BadQty, 0) as jt_bad,
isnull(jttoday.qty, 0) as jt_today,
isnull(jtyesterday.qty, 0) jt_yesterday,
0 - (isnull(jt.GoodQty, 0) - isnull(jttoday.qty, 0) + isnull(jtyesterday.qty, 0) - isnull(ph.GoodQty, 0) - isnull(ph.BadQty, 0)) as jt_difference,
isnull(ph.GoodQty, 0) as ph_good,
isnull(ph.BadQty, 0) as ph_bad,
isnull(phtoday.qty, 0) as ph_today,
isnull(phyesterday.qty, 0) ph_yesterday,
0 - (isnull(ph.GoodQty, 0) - isnull(phtoday.qty, 0) + isnull(phyesterday.qty, 0) - isnull(zj.GoodQty, 0) - isnull(zj.BadQty, 0)) as ph_difference,
isnull(zj.GoodQty, 0) as zj_good,
isnull(zj.BadQty, 0) as zj_bad
from @ProductIDList as a,
finishedproduct as b,
yuancaiFlow_view as hj,
basflowsubsistence as hjtoday,
basflowsubsistence as hjyesterday,
yuancaiFlow_view as sx,
basflowsubsistence as sxtoday,
basflowsubsistence as sxyesterday,
yuancaiFlow_view as jt,
basflowsubsistence as jttoday,
basflowsubsistence as jtyesterday,
yuancaiFlow_view as ph,
basflowsubsistence as phtoday,
basflowsubsistence as phyesterday,
yuancaiFlow_view as zj
where a.productid = b.productid
--hj
and hj.productiondate = @CheckDate
and left(a.productid, 4) *= left(hj.productid, 4)
and hj.productid like '____06'
and left(a.productid, 4) + '06' *= hjtoday.productid
and hjtoday.checkdate = @CheckDate
and left(a.productid, 4) + '06' *= hjyesterday.productid
and hjyesterday.checkdate = @YesterdayOfCheckDate
--sx
and sx.productiondate = @CheckDate
and left(a.productid, 4) *= left(sx.productid, 4)
and sx.productid like '____08'
and left(a.productid, 4) + '08' *= sxtoday.productid
and sxtoday.checkdate = @CheckDate
and left(a.productid, 4) + '08' *= sxyesterday.productid
and sxyesterday.checkdate = @YesterdayOfCheckDate
--jt
and jt.productiondate = @CheckDate
and left(a.productid, 4) *= left(jt.productid, 4)
and jt.productid like '____07'
and left(a.productid, 4) + '07' *= jttoday.productid
and jttoday.checkdate = @CheckDate
and left(a.productid, 4) + '07' *= jtyesterday.productid
and jtyesterday.checkdate = @YesterdayOfCheckDate
--ph
and ph.productiondate = @CheckDate
and left(a.productid, 4) *= left(ph.productid, 4)
and ph.productid like '____09'
and left(a.productid, 4) + '09' *= phtoday.productid
and phtoday.checkdate = @CheckDate
and left(a.productid, 4) + '09' *= phyesterday.productid
and phyesterday.checkdate = @YesterdayOfCheckDate
--zj
and zj.productiondate = @CheckDate
and left(a.productid, 4) *= left(zj.productid, 4)
and zj.productid like '01__11'
RETURN
END
GO
ASP端与SQL端意外地解耦合了：
Dim rsCheckList
Set rsCheckList = Server.CreateObject("ADODB.Recordset")
rsCheckList.ActiveConnection = oConn
rsCheckList.Open"select * from dbo.fn_GetGLSubsistenceList('"&datCheckDate&"')"
这次编程实践让我得到一个ASP开发的新思路：
用视图+函数封装数据库端，ASP端只调用函数，这样做最大的好处是平台移植更加方便了，并且对ASP程序员的数据库知识要求也降低了。