我们有一个这样的Oracle sql:
with temp1 as (
select col1,col2,col3,col4,col5 from a
), temp2 as (
select col1,col2,col3,col4,col5 from b
)
select *
from
(
( select * from temp1
minus
select * from temp2 )
union
( select * from temp2
minus
select * from temp1 )
)
union
(SELECT * FROM temp1 T1
LEFT JOIN temp2 T2 ON T1.col1 = T2.col1
WHERE T1.col2 <> T2.col2 )但它会抛出"ORA-01789:查询块的结果列数不正确“,
你能给我一些建议吗?
发布于 2020-07-03 10:32:20
现在还不清楚你想做什么。但是,此部分返回10列,而不是5列:
SELECT *
FROM temp1 T1 LEFT JOIN
temp2 T2
ON T1.col1 = T2.col1所以列数是不一样的。
显式列出所有子查询的列(带有别名)。
发布于 2020-07-03 13:01:24
您得到的错误是由于以下原因:
with
temp1 as (
select col1,col2,col3,col4,col5 from a --> TEMP1 has 5 columns
),
temp2 as (
select col1,col2,col3,col4,col5 from b --> TEMP2 has 5 columns
)
select *
from
(
( select * from temp1 --> TEMP1 MINUS TEMP2 has 5 columns --> OK
minus
select * from temp2 )
union
( select * from temp2 --> TEMP2 MINUS TEMP1 has 5 columns --> OK
minus
select * from temp1 )
)
union
(SELECT * FROM temp1 T1 --> TEMP1 JOIN TEMP2 has 10 columns --> WRONG
LEFT JOIN temp2 T2 ON T1.col1 = T2.col1
WHERE T1.col2 <> T2.col2 )如果最后一个查询(“错误的”查询)被重写为例如select t1.* from ...,它可以工作,但问题是这是否是你想要的。
发布于 2020-07-03 13:53:28
最后一个查询在选择列表中有10列,其他查询有5列。最后一个查询应该是t1.*或t2.*或任何自定义的但有5列的查询
https://stackoverflow.com/questions/62707615
复制相似问题