22,300
社区成员




with tabs as (select ROW_NUMBER() over(partition by a.模块ID order by a.时间 )
as rows, a.* from Data_AD a ) select 模块ID,avg(通道01),avg(通道02)
,avg(通道03),avg(通道04),avg(通道05),avg(通道06),avg(通道07),avg(通道08)
,avg(通道09),avg(通道10),avg(通道11),avg(通道12),avg(通道13),avg(通道14)
,avg(通道15),avg(通道16),avg(通道17),avg(通道18),avg(通道19),avg(通道20)
,avg(通道21),avg(通道22),avg(通道23),avg(通道24),avg(通道25),avg(通道26)
,avg(通道27),avg(通道28),avg(通道29),avg(通道30),avg(通道31),avg(通道32)
from tabs where 模块ID in ( select distinct(所属模块ID) from [MonitorPoint]
where 所属项目 = '地铁4号线站') and rows in (1,2,3,4) group by 模块ID order by 模块ID;
with tabs as
(select ROW_NUMBER() over(partition by AD.模块ID order by AD.时间 ) as row
, AD.* from Data_AD as AD where exists ( select * from [MonitorPoint]
where 所属项目 = '地铁4号线站' and 所属模块ID = AD.模块ID))
select 模块ID,avg(通道01),avg(通道02),avg(通道03),avg(通道04),avg(通道05)
,avg(通道06),avg(通道07),avg(通道08),avg(通道09),avg(通道10),avg(通道11)
,avg(通道12),avg(通道13),avg(通道14),avg(通道15),avg(通道16),avg(通道17)
,avg(通道18),avg(通道19),avg(通道20),avg(通道21),avg(通道22),avg(通道23)
,avg(通道24),avg(通道25),avg(通道26),avg(通道27),avg(通道28),avg(通道29)
,avg(通道30),avg(通道31),avg(通道32)
from tabs where tabs.row in (1,2,3,4) group by 模块ID order by 模块ID;
感谢你的建议,我在此基础上进行改进。
where 模块ID in ( select distinct(所属模块ID) from [MonitorPoint] where 所属项目 = '地铁4号线站')
-- 换成下面这句,看看
where exists(select * from [MonitorPoint] where 所属项目 = '地铁4号线站' and 所属模块ID = tabs.模块ID)