一个奇怪的sql问题

liujun999999 2009-12-09 09:44:54

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


hz_zdfzl_v200911和hz_zdfzl_v200912是2个视图
查询的时候如果只查2个视图中的一个,也就是说,不要union的话是正常的,但是只要查询是跨月份的,那么就要进行union,但是union之后他报了一个这样的错误

09:12:38.937 DBMS 192.168.100.110 -- Error: Number (11040) Severity (16) State (1) Server (SYBASE) Adaptive Server cannot perform the requested action because column 'measurePointRowId' is not within the scope of the joined table expression. Check your command for missing or incorrect database objects, variable names, and/or input data.

百思不解,请高手帮忙
...全文
124 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
罗耗子 2010-01-21
  • 打赏
  • 举报
回复
union 两端的字段数不一致啊
xtting_8984313 2010-01-13
  • 打赏
  • 举报
回复
union两端select的类型必需一一对应。
wwwwb 2009-12-09
  • 打赏
  • 举报
回复
代码太长,建议分步调试,
根据提示measurePointRowId列没有在连接表中,检查一下

2,596

社区成员

发帖
与我相关
我的任务
社区描述
Sybase相关技术讨论区
社区管理员
  • Sybase社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧