日期:2014-05-18  浏览次数:20685 次

这句Sql确实有问题,指导一下
SQL code
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

------解决方案--------------------
SQL code

--自己得学会怎么把问题描述清楚。。。而不是一味的让别人猜你的需求。。
---这样?
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

------解决方案--------------------
SQL code

--第二个查询把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