Oracle SQL 工作表

发布于 2024-11-28 23:47:58 字数 815 浏览 0 评论 0原文

我正在尝试比较两列，目标是返回不匹配的行。例如，我在第 1 列和第 2 列中，下面是

1                     2
id name age job       id  name  age  job
1   aaa  11 bbb        1   aaa   11   bbb
2   ccc  22 ddd        2   ccc   22   eee

我正在寻找的返回值，

2  ccc 22  ddd
2  ccc 22  eee

我正在尝试使用以下内容

select id, name, age from 1 where id in
(
select id, name, age from 1
minus
select id, name, age from 2
)
union all
select id, name, age from 2 where id in
(
select id, name, age from 1
minus
select id, name, age from 2
)
order by id

，我收到以下错误

ORA-00913: demasiados valores
00913. 00000 -  "too many values"
*Cause:    
*Action:
Error at Line: 6 Column: 1

Thatrefers the line with the 1st (

Any help将不胜感激。

原文

I'm trying to compare 2 columns and the goal is the return of the lines that don't match. For example I have in column 1 and 2 the following

1                     2
id name age job       id  name  age  job
1   aaa  11 bbb        1   aaa   11   bbb
2   ccc  22 ddd        2   ccc   22   eee

the return I'm looking for is

2  ccc 22  ddd
2  ccc 22  eee

I'm trying to use the following

select id, name, age from 1 where id in
(
select id, name, age from 1
minus
select id, name, age from 2
)
union all
select id, name, age from 2 where id in
(
select id, name, age from 1
minus
select id, name, age from 2
)
order by id

I get the following error

ORA-00913: demasiados valores
00913. 00000 -  "too many values"
*Cause:    
*Action:
Error at Line: 6 Column: 1

That refers to the line with the 1st (

Any help would be very appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦里兽 2024-12-05 23:47:58

您收到的具体错误是由于以下原因造成的：

where id in ( select id, name, age from

您不能使用 in 子句将 ID 的单个值与其他 3 个值进行比较。

-- get values in 1 not in 2
SELECT 1.id, 1.name, 1.age 
FROM 1
LEFT JOIN 2 on 1.id = 2.id and 1.name = 2.name and 1.age = 2.age
WHERE 2.id is null
UNION
-- get values in 2 not in 1
SELECT 2.id, 2.name, 2.age 
FROM 2
LEFT JOIN 1 on 2.id = 1.id and 2.name = 1.name and 2.age = 1.age
WHERE 1.id is null

The specific error you are getting is because of the following:

where id in ( select id, name, age from

You can not use an in clause to compare a single value of ID to 3 other values.

-- get values in 1 not in 2
SELECT 1.id, 1.name, 1.age 
FROM 1
LEFT JOIN 2 on 1.id = 2.id and 1.name = 2.name and 1.age = 2.age
WHERE 2.id is null
UNION
-- get values in 2 not in 1
SELECT 2.id, 2.name, 2.age 
FROM 2
LEFT JOIN 1 on 2.id = 1.id and 2.name = 1.name and 2.age = 1.age
WHERE 1.id is null

回复收藏 0 原文

~没有更多了~