select convert(varchar(7),time,120) as time,SUM(kwh212) as kwh212 from tb_kwh_dcs where datepart(hh,TIME)between 10 and 11 or datepart(hh,TIME)between 18 and 21 group by convert(varchar(7),'2016-01',120)这个样子吗? 提示:每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。
IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL DROP TABLE #TmpCREATE TABLE #Tmp( CreateDate DATETIME, Nr INT )INSERT INTO #Tmp ( CreateDate, Nr ) VALUES ( '2016-01-01 11:30:00',1) ,( '2016-01-01 14:30:00',2) ,( '2016-01-01 18:30:00',2) ,( '2016-01-02 10:30:00',2) ,( '2016-01-02 15:30:00',2) ,( '2016-01-02 19:30:00',2) ,( '2016-01-02 23:30:00',2)DECLARE @StartDate DATETIME = '2016-01-01', @EndDate DATETIME = '2016-02-01' ;WITH CTE AS ( SELECT CONVERT(VARCHAR(10),@StartDate,120) AS DisplayName, DATEADD(hh,10,@StartDate) AS StartDate, DATEADD(hh,12,@StartDate) AS EndDate UNION ALL SELECT CONVERT(VARCHAR(10),@StartDate,120) AS DisplayName, DATEADD(hh,18,@StartDate) AS StartDate, DATEADD(hh,22,@StartDate) AS EndDate UNION ALL SELECT CONVERT(VARCHAR(10),DATEADD(dd,1,StartDate),120), DATEADD(dd,1,StartDate) AS StartDate, DATEADD(dd,1,EndDate) AS EndDate FROM CTE WHERE StartDate < DATEADD(dd,-1,@EndDate) ) SELECT b.DisplayName,SUM(a.Nr) FROM #Tmp a INNER JOIN CTE b ON a.CreateDate BETWEEN b.StartDate AND b.EndDate GROUP BY b.DisplayNameDisplayName ----------- ----------- 2016-01-01 3 2016-01-02 4
时间在where里面用between限制
datepart(hh,TIME)between 10 and 11 or
datepart(hh,TIME)between 18 and 21
group by convert(varchar(7),'2016-01',120)这个样子吗?
提示:每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。
DROP TABLE #TmpCREATE TABLE #Tmp(
CreateDate DATETIME,
Nr INT
)INSERT INTO #Tmp
( CreateDate, Nr )
VALUES ( '2016-01-01 11:30:00',1)
,( '2016-01-01 14:30:00',2)
,( '2016-01-01 18:30:00',2)
,( '2016-01-02 10:30:00',2)
,( '2016-01-02 15:30:00',2)
,( '2016-01-02 19:30:00',2)
,( '2016-01-02 23:30:00',2)DECLARE @StartDate DATETIME = '2016-01-01',
@EndDate DATETIME = '2016-02-01'
;WITH CTE AS
(
SELECT CONVERT(VARCHAR(10),@StartDate,120) AS DisplayName,
DATEADD(hh,10,@StartDate) AS StartDate,
DATEADD(hh,12,@StartDate) AS EndDate
UNION ALL
SELECT CONVERT(VARCHAR(10),@StartDate,120) AS DisplayName,
DATEADD(hh,18,@StartDate) AS StartDate,
DATEADD(hh,22,@StartDate) AS EndDate
UNION ALL
SELECT CONVERT(VARCHAR(10),DATEADD(dd,1,StartDate),120),
DATEADD(dd,1,StartDate) AS StartDate,
DATEADD(dd,1,EndDate) AS EndDate
FROM CTE
WHERE StartDate < DATEADD(dd,-1,@EndDate)
)
SELECT b.DisplayName,SUM(a.Nr)
FROM #Tmp a
INNER JOIN CTE b ON a.CreateDate BETWEEN b.StartDate AND b.EndDate
GROUP BY b.DisplayNameDisplayName
----------- -----------
2016-01-01 3
2016-01-02 4