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版本是多少?
发贴时请看置顶帖。
你的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
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)
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
这样试试
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
你干脆把查询1和查询2的内容全帖出来吧,可能ASE不支持这种语法。
你的Sybase 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
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)
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
这样试试
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) 格式,
优化一下吧
优化一下吧