数据库查询多表后重新编号产生的小问题求解~~

[shaluo520]

数据库Oracle,假设表名为GG_ZONE
字段
NAME
DISTRICT
测试数据为
insert into GG_ZONE(name,DISTRICT) values('A','350300')
insert into GG_ZONE(name,DISTRICT) values('B','350301')
insert into GG_ZONE(name,DISTRICT) values('C','350302')
insert into GG_ZONE(name,DISTRICT) values('D','350303')
insert into GG_ZONE(name,DISTRICT) values('E','350304')
表名为HS_CONSTRUCTPROJECT
字段
Guid
PROJECTNAME
DISTRICT
测试数据为
insert into HS_CONSTRUCTPROJECT(Guid,PROJECTNAME,DISTRICT) values('a123','测试','350300')
insert into HS_CONSTRUCTPROJECT(Guid,PROJECTNAME,DISTRICT) values('b123','测试看看','350301')
insert into HS_CONSTRUCTPROJECT(Guid,PROJECTNAME,DISTRICT) values('c123','building','350302')
insert into HS_CONSTRUCTPROJECT(Guid,PROJECTNAME,DISTRICT) values('d123','建设项目','350303')
insert into HS_CONSTRUCTPROJECT(Guid,PROJECTNAME,DISTRICT) values('e123','天上人间','350304')
表名为HS_PROJECTPROGRESS
字段
CONSTRUCTID
VISUALPROGRESS
insert into HS_PROJECTPROGRESS(CONSTRUCTID,VISUALPROGRESS) values('a123','天气')
insert into HS_PROJECTPROGRESS(CONSTRUCTID,VISUALPROGRESS) values('b123','看看')
insert into HS_PROJECTPROGRESS(CONSTRUCTID,VISUALPROGRESS) values('c123','数据')
查询的结果为
id name PROJECTNAME   DISTRICT  VISUALPROGRESS

1   A   测试          350300    天气
2   B   测试看看      350301    看看
3   C   building      350302    数据
4   D   建设项目      350303
5   E   天上人间      350304

请问这样的查询如何写。不希望用临时表的查询。。。
之前有个这样的写法是
select rownum id,name,PROJECTNAME,DISTRICT，VISUALPROGRESS
from
(select z.name,c.PROJECTNAME,z.DISTRICT
 from GG_ZONE z,HS_CONSTRUCTPROJECT c，HS_PROJECTPROGRESS y
 where z.DISTRICT=c.DISTRICT and c.Guid=y.CONSTRUCTID(+)
 order by z.name)

oracle数据库里执行的时候是没报错可以通过并得到我要的结果，但是在c#里用dataset数据集配置向导的时候报错,
提示错误为已生成SELECT语句。"ORDER"附近的WHERE字句错误。无法分析查询文本。
请问这样的错误是因为sql语句的错误吗？要怎么修改才能得到我要的结果，但是又可以通过C#里的dataSet的数据集配置向导呢？？？

[hao1hao2hao3]

把(+)换成标准写法 left join 试试

select rownum id,name,PROJECTNAME,DISTRICT，VISUALPROGRESS
from
(select z.name,c.PROJECTNAME,z.DISTRICT
 from GG_ZONE z join  HS_CONSTRUCTPROJECT c z.DISTRICT=c.DISTRICT  left join HS_PROJECTPROGRESS y
 on c.Guid=y.CONSTRUCTID
 order by z.name)

[minitoy]

select  id,name,PROJECTNAME,DISTRICT，VISUALPROGRESS from
(select z.name,c.PROJECTNAME,z.DISTRICT,row_number()over(order by z.name) id
 from GG_ZONE z,HS_CONSTRUCTPROJECT c，HS_PROJECTPROGRESS y
 where z.DISTRICT=c.DISTRICT and c.Guid=y.CONSTRUCTID(+))

这样呢

[minitoy]

select id,name,PROJECTNAME,DISTRICT，VISUALPROGRESS from
(select z.name,c.PROJECTNAME,z.DISTRICT,row_number()over(order by z.name) id
from GG_ZONE z,HS_CONSTRUCTPROJECT c，HS_PROJECTPROGRESS y
where z.DISTRICT=c.DISTRICT and c.Guid=y.CONSTRUCTID(+)) order by id asc

[心中的彩虹]

[Quote=引用楼主 shaluo520 的回复:]
SQL code
数据库Oracle,假设表名为GG_ZONE
字段
NAME
DISTRICT
测试数据为
insert into GG_ZONE(name,DISTRICT) values('A','350300')
insert into GG_ZONE(name,DISTRICT) values('B','350301')
insert into GG_ZONE(name,……
[/Quote]

--试试这样
select name,PROJECTNAME,DISTRICT，VISUALPROGRESS
from 
(select z.name,c.PROJECTNAME,z.DISTRICT,y.VISUALPROGRESS,
row_number() over(order by z.name) id
from GG_ZONE z join HS_CONSTRUCTPROJECT c on z.DISTRICT=c.DISTRICT
left join HS_PROJECTPROGRESS y on c.Guid=y.CONSTRUCTID) a
where id between m and n