SQL回炉系列(二) 多表联合查询和视图

时间:2021-03-13 19:11:58

以下SQL脚本用于查询每个监测电表的即时功率和同时刻工作日的历史最大最小功率,涉及四张表:

  • 即时数据表 cf_originaldata
  • 电表信息表 StationInfo
  • 历史记录表 HistoryMaxPower
  • 日期信息表 WorkCalendar

即时数据表存放即时数据,每个电表每30秒采集一次数据存入;
电表信息表有表的ID,名字等信息;
历史记录表统计了每个电表一天中每个30秒单位的历史最大和最小功率,分工作日和非工作日,以isWorkDay字段区分;
日期信息表存放了日期信息,年月日星期几,是否是工作日等;
查询结果以显示为视图。

SQL语句如下:

USE [ZSJ]
GO

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[RealPowerWithMaxMinView] AS SELECT ROW_NUMBER() over(order by c1.back) as rows, c2.StationID back, py, StationName, switchResult, max_py, min_py, count(distinct c2.StationID) notused from ( SELECT back, py FROM dbo.cf_originaldata WHERE getTime>DATEADD(SS, -20, GETDATE()) and gettime = (SELECT TOP 1 gettime FROM dbo.cf_originaldata ORDER BY AutoId DESC) ) c1 right join StationInfo c2 on c1.back=c2.StationID left join ( select max_py,min_py,StationID from dbo.HistoryMaxPower where time=(select top 1 time from HistoryMaxPower where time<convert(varchar(100),getdate(),8) order by time desc) and isWorkDay= (select isWorkDay from dbo.WorkCalendar where convert(varchar(10),the_date,120)=convert(varchar(10),GetDate(),120)) ) c3 on c2.StationID=c3.StationID group by c1.back,c2.StationID, py, StationName, switchResult,max_py, min_py GO