老师们执行报错Incorrect syntax near the keyword 'as'

我是想将查询1和查询2的结果合并，合并关系是provider 以查询1的provider为准。

select a.provider,a.code,b.provider
from (查询1)
as a left outer join
(查询2) as b on
a.provider = b.provider

上面的语句执行报错

报错：Line 6:
Incorrect syntax near the keyword 'as'.
Server Message: Number 156, Severity 15
Line 9:
Incorrect syntax near the keyword 'as'.
  求老师们解释下！

有哪位大师有更好的合并查询语句，执行环境是Sybase！

8 个解决方案

#1

你干脆把查询1和查询2的内容全帖出来吧，可能ASE不支持这种语法。
你的Sybase ASE版本是多少？
发贴时请看置顶帖。

#2

版本是SYBASE1192
select a.provider,a.code,b.provider
from (select provider,count(code) from provider_price where code in(select code from sell_data)
and provider in(select provider from contract where state in('正常','延期') and opstyle='经销')
and code in(select code from commodity where style=0 and state='正常')
group by provider) as a
left outer join
(select provider,count(distinct code) code from account where resume in('销售')
and provider in(select provider from contract where state in('正常','延期') and opstyle='经销')
and busdate>='2009-05-26' and busdate<='2009-06-25' group by provider) as b on a.provider = b.provider

#3

你先确保left join的两个查询都能正常执行：
select a.provider,a.code,b.provider
from (select provider,count(code) from provider_price where code in(select code from sell_data)
and provider in(select provider from contract where state in('正常','延期') and opstyle='经销')
and code in(select code from commodity where style=0 and state='正常')
group by provider) as a
以及
select provider,count(distinct code) code from account where resume in('销售')
and provider in(select provider from contract where state in('正常','延期') and opstyle='经销')
and busdate>='2009-05-26' and busdate<='2009-06-25' group by provider)

#4

2个查询语句单独是能执行的！

#5

select a.provider,a.code,b.provider
from (查询1)
as a left outer join
查询2 as b on
a.provider = b.provider

这样试试

#6

你的SYSBASE的版本是多少？是否支持子查询？

试一下

select a.provider

from (查询1)  as a

select a.provider

from (查询1)  a

是否正确。

#7

都试了还是不行是不是数据库不支持这样的语句格式哦！哎！谢谢你们的指点！

#8

sybase 不支持 select a from (select a from tb) 格式，
优化一下吧

#1