34762.htm

一本很基础的SQL讲解

<link href="./dzs_cs.css" rel="stylesheet" type="text/css" />
<table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="24" align="center" valign="bottom" class="d_font3">SQL函数设计之临时表的使用</td>
      </tr>
      <tr>
        <td height="3" bgcolor="#E3E3E3"></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td class="d_font4">&nbsp;if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GETALL]') and xtype in (N'FN', N'IF', N'TF'))<BR>drop function [dbo].[GETALL]<BR>GO 
<P>--根据起始时间选择设定量、实出量、地面实出量以及出现0的情况，以查看故障信息<BR><BR>CREATE FUNCTION GETALL(@dateStart datetime,@dateEnd datetime)<BR>RETURNS @Result TABLE(ENo int,PlanTotal int,FactTotal int,FactOutTotal int,FactZero int,FactOutZero int)&nbsp; <BR>AS<BR>BEGIN<BR>DECLARE @Plan TABLE(ENo int ,PlanTotal int )<BR>DECLARE @Fact TABLE(ENo int,FactTotal int,FactOutTotal int)<BR>DECLARE @FactZero TABLE(ENo int,FactZero int)<BR>DECLARE @FactOutZero TABLE(ENo int,FactOutZero int)</P>
<P>INSERT @Plan<BR>SELECT Electrobath_No,sum(Al_P_Plan) AS planTotal<BR>FROM Al_Product2005 as a <BR>WHERE (C_date between @dateStart and @dateEnd) and (Al_Status&lt;&gt;'0') and (Al_P_Fact&lt;&gt;'0')<BR>and<BR>(<BR>NOT EXISTS<BR>(<BR>SELECT * <BR>FROM Al_Product2005 AS b <BR>WHERE <BR>(a.Al_F_Count &lt; b.Al_F_Count) AND (a.Electrobath_No = b.Electrobath_No) <BR>AND (a.Al_P_count = b.Al_P_count) AND (a.C_Date = b.C_Date) AND (Al_Status &lt;&gt; 0)<BR>)<BR>)<BR>GROUP BY Electrobath_No</P>
<P>INSERT @Fact<BR>SELECT DISTINCT Electrobath_No,sum(Al_P_Fact) as factTotal,sum(Al_P_FactOut) as factOutTotal <BR>FROM Al_Product2005<BR>WHERE (C_Date between @dateStart and @dateEnd)<BR>GROUP BY Electrobath_No </P>
<P>INSERT @FactZero</P>
<P>SELECT Electrobath_No,count(Al_P_Fact) AS FactZero<BR>FROM Al_Product2005&nbsp; <BR>WHERE (C_Date between @dateStart and @dateEnd) and (Al_P_Fact='0')<BR>GROUP BY Electrobath_No</P>
<P>INSERT @FactOutZero</P>
<P>SELECT Electrobath_No,count(Al_P_FactOut) as FactOutZero<BR>FROM Al_Product2005&nbsp; <BR>WHERE (C_Date between @dateStart and @dateEnd) and (Al_P_FactOut='0')<BR>GROUP BY Electrobath_No</P>
<P>INSERT @Result<BR>SELECT a.ENo,PlanTotal,FactTotal,b.FactOutTotal,c.FactZero,d.FactOutZero<BR>FROM @Plan a,@Fact b,@FactZero c,@FactOutZero d</P>
<P>RETURN</P>
<P>END</P>
<P>(责任编辑：铭铭 <A href="mailto: [e-mail]">[e-mail]</A> TEL：[phone])</P></td>
      </tr>
      <tr>
        <td class="d_font4">&nbsp;</td>
      </tr>
    </table>