数据库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 VISUALPROGRESS1 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的数据集配置向导呢???
字段
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 VISUALPROGRESS1 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的数据集配置向导呢???
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)
(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(+))
这样呢
(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
--试试这样
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