SQLServer 2012自定义函数，返回查询结果

出发点：与数据相关的查询、统计、分析操作交给数据库自己完成。前台访问的时候，直接调用具体的函数，确保传的参数准确即可。

/*
  查询每小时能耗数据
  P_BuildID：楼宇代码
  P_EnergyCode：能源类型，支持模糊查询
  P_StartDate：统计起始时间
  P_EndDate：统计结束时间
  用例：SELECT F_HourValue,F_CollectTime FROM EMS.QueryEneryItemHourResult('110108A001','01%','2015-03-23','2015-03-24');
*/
CREATE FUNCTION QueryEneryItemHourResult(@P_BuildID char(10),@P_EnergyCode varchar(5),@P_StartDate datetime,@P_EndDate datetime)
RETURNS @EneryItemHourResult TABLE (F_HourValue numeric(18,4),F_CollectTime datetime) AS 
BEGIN
  WITH C AS(
    SELECT (F_OrigValue-MAX(F_OrigValue) OVER(PARTITION BY OrigValue.F_MeterID ORDER BY F_CollectTime ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) AS HourValue,F_CollectTime,F_EnergyItemCode
    FROM EMS.T_OV_MeterOrigValue AS OrigValue
    LEFT JOIN EMS.T_ST_MeterEnergyItemInfo AS MeterEnergy 
    ON  OrigValue.F_MeterID = MeterEnergy.F_MeterID
    WHERE F_BuildID=@P_BuildID 
      AND OrigValue.F_CollectTime BETWEEN @P_StartDate AND @P_EndDate 
      AND DATEPART(MINUTE,F_CollectTime)=0 
      AND F_EnergyItemCode LIKE @P_EnergyCode
  )
  INSERT INTO @EneryItemHourResult
  SELECT SUM(C.HourValue) AS HourValue,C.F_CollectTime
  FROM C
  GROUP BY C.F_CollectTime
  RETURN
END

/*
  查询昨天每小时能耗数据
  P_BuildID：楼宇代码
  P_EnergyCode：能源类型，支持模糊查询
  用例：SELECT F_HourValue,F_CollectTime FROM EMS.QueryYesterdayEneryItemHourResult('110108A001','01%');
*/
CREATE FUNCTION QueryYesterdayEneryItemHourResult(@P_BuildID char(10),@P_EnergyCode varchar(5))
RETURNS @YesterdayEneryItemHourResult TABLE (F_HourValue numeric(18,F_CollectTime datetime) AS 

BEGIN
  DECLARE @P_EndDate datetime = CONVERT(varchar(10),GETDATE(),111)
  DECLARE @P_StartDate datetime = CONVERT(varchar(10),DATEADD(DAY,-1,@P_EndDate),111)

  INSERT INTO @YesterdayEneryItemHourResult
  SELECT F_HourValue,F_CollectTime
  FROM EMS.QueryEneryItemHourResult(@P_BuildID,@P_EnergyCode,@P_StartDate,@P_EndDate)
  RETURN
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!