select ElUserName,a.fd_tm,a.fd_1,MeterId as meterjbh,a.Fd_2,a.fd_3 from V_0c33_Max as a right join
(select MeterId,ElUserName from MonitorUserList where addr1 in
(select addr1 from MonitorInfo left join UserGroup on MonitorInfo.bumenid =UserGroup.id join systemIniList on IniIndex = 9
and systemIniList.fd_b = MonitorInfo.txgy where UserGroup.id='226' or UserGroup.id =226)
and addr2 in (select addr2 from MonitorInfo left join UserGroup on MonitorInfo.bumenid =UserGroup.id join
systemIniList on IniIndex = 9 and systemIniList.fd_b = MonitorInfo.txgy where UserGroup.id='226' or UserGroup.id =226))
as TV on MeterId=a.MeterJbh where 1=1 order by a.fd_tm Desc
go
select addr1,addr2 from MonitorInfo left join UserGroup on MonitorInfo.bumenid =UserGroup.id join
systemIniList on IniIndex = 9 and systemIniList.fd_b = MonitorInfo.txgy where UserGroup.id='226' or UserGroup.id =226
红三角粮油食品有限公司 2012-05-28 13:00:00.000 2012-05-28 13:03:00.000 902796 4329.84 1939.01
焦作市双龙防腐瓷业有限公司 2012-05-28 07:30:00.000 2012-05-28 07:33:00.000 933454 2515.48 696.35
修武县宏诚机械有限公司 NULL NULL 000000934791 NULL NULL
391 1
1200 58
(select MeterId,ElUserName from MonitorUserList where addr1 in
(select addr1 from MonitorInfo left join UserGroup on MonitorInfo.bumenid =UserGroup.id join systemIniList on IniIndex = 9
and systemIniList.fd_b = MonitorInfo.txgy where UserGroup.id='226' or UserGroup.id =226)
and addr2 in (select addr2 from MonitorInfo left join UserGroup on MonitorInfo.bumenid =UserGroup.id join
systemIniList on IniIndex = 9 and systemIniList.fd_b = MonitorInfo.txgy where UserGroup.id='226' or UserGroup.id =226))
as TV on MeterId=a.MeterJbh where 1=1 order by a.fd_tm Desc
go
select addr1,addr2 from MonitorInfo left join UserGroup on MonitorInfo.bumenid =UserGroup.id join
systemIniList on IniIndex = 9 and systemIniList.fd_b = MonitorInfo.txgy where UserGroup.id='226' or UserGroup.id =226
红三角粮油食品有限公司 2012-05-28 13:00:00.000 2012-05-28 13:03:00.000 902796 4329.84 1939.01
焦作市双龙防腐瓷业有限公司 2012-05-28 07:30:00.000 2012-05-28 07:33:00.000 933454 2515.48 696.35
修武县宏诚机械有限公司 NULL NULL 000000934791 NULL NULL
391 1
1200 58
--自己得学会怎么把问题描述清楚而不是一味的让别人猜你的需求。。
---这样?
select ElUserName,a.fd_tm,a.fd_1,MeterId as meterjbh,a.Fd_2,a.fd_3 from V_0c33_Maxas a
right join
(select MeterId,ElUserName from MonitorUserList a,
(select addr1 from MonitorInfo left join UserGroup on MonitorInfo.bumenid =UserGroup.id join systemIniList on IniIndex = 9
and systemIniList.fd_b = MonitorInfo.txgy where UserGroup.id='226' or UserGroup.id =226)b
where a.addr1=b.addr1 and a.addr2=b.addr2)
as TV on MeterId=a.MeterJbh where 1=1 order by a.fd_tm Desc
--第二个查询把Inner Join 改成 Left Join 不知道是不是你要的效果...
select
addr1,
addr2
from MonitorInfo
left join UserGroup on MonitorInfo.bumenid =UserGroup.id
--把Inner join 改成 left join 的效果呢?
left join systemIniList on systemIniList.fd_b = MonitorInfo.txgy
where
--为啥总要写两遍
--UserGroup.id='226' or UserGroup.id =226
UserGroup.id='226'
and IniIndex = 9