2,596
社区成员
发帖
与我相关
我的任务
分享
select distinct a.measurePointRowId MEASUREPOINTROWID,
a.measurePointName MEASUREPOINTNAME,
b.linename LINENAME,
a.consumerRowId,
a.consumerName CONSUMERNAME,
a.totalratedpower TOTALRATEDPOWER,
b.jltype JLTYPE,
c.cnt CNT,
isnull(d.overcnt, 0) OVERCNT,
cc.fzlfz FZLFZ
from (select distinct measurePointRowId,
measurePointName,
consumerRowId,
consumerName,
totalratedpower
from hz_zdfzl_v200911
where datadate >= '2009-11-01'
and datadate < '2009-12-06'
union
select distinct measurePointRowId,
measurePointName,
consumerRowId,
consumerName,
totalratedpower
from hz_zdfzl_v200912
where datadate >= '2009-11-01'
and datadate < '2009-12-06') a
inner join (select distinct dbid, linename, 6003 jltype
from oj_group_dkh a
where a.gdjid = 5000532
union
select distinct dbid, linename, 8003 jltype
from oj_group_pw a
where a.gdjid = 5000532) b on b.dbid = a.measurePointRowId
left join sys_gdzlhreshold cc on cc.oi_idf = b.dbid
left join (select measurePointRowId, count(*) cnt
from (select *
from hz_zdfzl_v200911
where datadate >= '2009-11-01'
and datadate < '2009-12-06') a
group by a.measurePointRowId
union
select measurePointRowId, count(*) cnt
from (select *
from hz_zdfzl_v200912
where datadate >= '2009-11-01'
and datadate < '2009-12-06') b
group by b.measurePointRowId) c on c.measurePointRowId =
b.dbid
left join (select measurePointRowId, count(*) overcnt
from (select *
from hz_zdfzl_v200911 a
left join sys_gdzlhreshold b on a.measurePointRowId =
b.oi_idf
where a.fzl > b.fzlfz
and datadate >= '2009-11-01'
and datadate < '2009-12-06') a
group by a.measurePointRowId
union
select measurePointRowId, count(*) overcnt
from (select *
from hz_zdfzl_v200912 a
left join sys_gdzlhreshold b on a.measurePointRowId =
b.oi_idf
where a.fzl > b.fzlfz
and datadate >= '2009-11-01'
and datadate < '2009-12-06') b
group by b.measurePointRowId) d on d.measurePointRowId =
b.dbid