本人工作单位是一家橡塑产品制造企业，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
             &n

[1] [2] [3]  下一页