27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT CONVERT(VARCHAR(10),DTIME,23) AS PER_DAY,
AVG(PLC1) AS PLC1_AVG,AVG(PLC2) AS PLC2_AVG
FROM TABLE
WHERE DTIME BETWEEN '2017-07-01' AND '2017-07-31'
GROUP BY CONVERT(VARCHAR(10),DTIME,23)
SELECT CONVERT(VARCHAR(10),DTIME,23) AS PER_DAY,
AVG(PLC1) AS PLC1_AVG,AVG(PLC2) AS PLC2_AVG
FROM TABLE
GROUP BY CONVERT(VARCHAR(10),DTIME,23)
如果要以日期为字段名来显示,那就得动态了。--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([dtime] DATETIME,[PLC1] decimal(18,9),[PLC2] decimal(18,9))
Insert #T
select N'2017-07-01 17:03:29.000',1.120,7.715 union all
select N'2017-07-01 17:04:29.000',1.120,7.715 union all
select N'2017-07-01 17:05:29.000',1.120,7.715 union all
select N'2017-07-02 18:03:29.000',1.120,7.715 union all
select N'2017-07-02 18:04:29.000',1.120,7.715 union all
select N'2017-07-02 18:05:29.000',1.120,7.715
Go
--测试数据结束
SELECT t.gtime,AVG(t.PLC1) plc1 FROM (
Select *,CONVERT(NVARCHAR(10),dtime,120) AS gtime from #T
)t
WHERE t.gtime BETWEEN '2017-07-01' AND '2017-07-01'
GROUP BY t.gtime