时间:2021-04-16
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
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
执行GROUPBY子句的最一般的方法:先扫描整个表,然后创建一个新的临时表,表中每个组的所有行应为连续的,最后使用该临时表来找到组并应用聚集函数(如果有聚集函数
(一)行号显示和排序1.SQLServer的行号A.SQL2000使用identity(int,1,1)和临时表,可以显示行号SELECTidentity(in
本文实例讲述了mysql临时表用法。分享给大家供大家参考,具体如下:一、创建临时表可以将查询结果寄存。报表制作的查询sql中可以用到。(1)关于寄存方式,mys
复制代码代码如下:/******备份数据库结构******//*函数名称:table2sql()函数功能:把表的结构转换成为SQL函数参数:$table:要进行
外部临时表通过CREATETEMPORARYTABLE创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭