SQL语句

1、--按天查询(本月)
select Id, ActiveTotal,(DataDate) from DimEnergyFrozen WHERE MeterId=5 And DATEDIFF(MONTH,DataDate,GETDATE())=0 order by DataDate;

 

2、--取去年一年的每月数据
select YEAR(UPDATETime) '年',Month(UPDATETime) '月',Sum(ActiveTotal) '总计' from DimEnergyFrozen where UpdateTime<DATEADD(YYYY,-1,GETDATE()) and UpdateTime >DATEADD(YYYY,-2,GETDATE()) group by YEAR(UPDATETime),Month(UPDATETime) Order by YEAR(UPDATETime),MONTH(UPDATETime)

3、--取现在1年的每月数据
select YEAR(DataDate) '年',Month(DataDate) '月',Sum(ActiveTotal) '总计' from DimEnergyFrozen where DataDate<GETDATE() and DataDate >DATEADD(YYYY,-1,GETDATE()) group by YEAR(DataDate),Month(DataDate) Order by YEAR(DataDate),MONTH(DataDate)

4、--按月总计(用户表5)
select YEAR(UPDATETime) '年',Month(DataDate) '月',Sum(ActiveTotal) '总计' from DimEnergyFrozen where MeterId=5 group by YEAR(UPDATETime),Month(DataDate) Order by MONTH(DataDate) DESC

==========================