本文简述SQL函数设计之临时表的使用方法。

　　if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GETALL]') and xtype in (N'FN', N'IF', N'TF'))

　　drop function [dbo].[GETALL]

　　GO

　　--根据起始时间选择设定量、实出量、地面实出量以及出现0的情况，以查看故障信息

　　CREATE FUNCTION GETALL(@dateStart datetime,@dateEnd datetime)

　　RETURNS @Result TABLE(ENo int,PlanTotal int,FactTotal int,FactOutTotal int,FactZero int,FactOutZero int)

　　AS

　　BEGIN

　　DECLARE @Plan TABLE(ENo int ,PlanTotal int )

　　DECLARE @Fact TABLE(ENo int,FactTotal int,FactOutTotal int)

　　DECLARE @FactZero TABLE(ENo int,FactZero int)

　　DECLARE @FactOutZero TABLE(ENo int,FactOutZero int)

　　INSERT @Plan

　　SELECT Electrobath_No,sum(Al_P_Plan) AS planTotal

　　FROM Al_Product2005 as a

　　WHERE (C_date between @dateStart and @dateEnd) and (Al_Status<>'0') and (Al_P_Fact<>'0')

　　and

　　(

　　NOT EXISTS

　　(

　　SELECT *

　　FROM Al_Product2005 AS b

　　WHERE

　　(a.Al_F_Count < b.Al_F_Count) AND (a.Electrobath_No = b.Electrobath_No)

　　AND (a.Al_P_count = b.Al_P_count) AND (a.C_Date = b.C_Date) AND (Al_Status <> 0)

　　)

　　)

　　GROUP BY Electrobath_No

　　INSERT @Fact

　　SELECT DISTINCT Electrobath_No,sum(Al_P_Fact) as factTotal,sum(Al_P_FactOut) as factOutTotal

　　FROM Al_Product2005

　　WHERE (C_Date between @dateStart and @dateEnd)

　　GROUP BY Electrobath_No

　　INSERT @FactZero

　　SELECT Electrobath_No,count(Al_P_Fact) AS FactZero

　　FROM Al_Product2005

　　WHERE (C_Date between @dateStart and @dateEnd) and (Al_P_Fact='0')

　　GROUP BY Electrobath_No

　　INSERT @FactOutZero

　　SELECT Electrobath_No,count(Al_P_FactOut) as FactOutZero

　　FROM Al_Product2005

　　WHERE (C_Date between @dateStart and @dateEnd) and (Al_P_FactOut='0')

　　GROUP BY Electrobath_No

　　INSERT @Result

　　SELECT a.ENo,PlanTotal,FactTotal,b.FactOutTotal,c.FactZero,d.FactOutZero

　　FROM @Plan a,@Fact b,@FactZero c,@FactOutZero d

　　RETURN

　　END