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

本文转自 august 51CTO博客，原文链接：http://blog.51cto.com/august/6934，如需转载请自行联系原作者