一个奇怪的sql有关问题

一个奇怪的sql问题

SQL code

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.937DBMS192.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.

百思不解，请高手帮忙

[解决办法]
代码太长，建议分步调试，
根据提示measurePointRowId列没有在连接表中，检查一下
------解决方案--------------------

union两端select的类型必需一一对应。
[解决办法]
union 两端的字段数不一致啊

查看更多 下一篇