查询在Ingres中不起作用

Question

我继承了一些ingres数据库的东西。以前从未使用过ingres。我找到了以下查询来隔离不同的电子邮件地址记录。

select a.reg_uid as id, a.firstname, a.lastname, a.postzip_code, a.suburb, a.city, a.state, a.email, a.country 
from register a
inner join 
(
 select distinct email, min(reg_uid) as id from register 
  group by email 
) as b
on a.email = b.email 
and a.id = b.id

但是，当我将它插入到ingres中时，我得到了错误

"Table 'select' does not exist or is not owned by you."

有什么想法吗？

Answer 1

如果您使用的是Ingres 10S (10.1)，则可以使用通用表表达式(CTE)，如下所示：

with b(email,id) as
(
 select distinct email, min(reg_uid) as id from register 
  group by email 
)
select a.reg_uid as id, a.firstname, a.lastname, a.postzip_code, a.suburb, a.city, a.state, a.email, a.country 
from register a
inner join b
on a.email = b.email 
and a.id = b.id

对于早期版本，您可以为b创建一个视图( CTE实际上是一个内联视图)，或者将其重写为

select a.reg_uid as id, a.firstname, a.lastname, a.postzip_code, a.suburb, a.city, a.state, a.email, a.country 
from register a
where a.id = (
 select min(reg_uid) as id from register b
 where b.email=a.email
)

Answer 2

我在Ingres 9上成功地尝试了您的精确语句：

create table register ( id char(10), reg_uid char(10), firstname char(10), lastname char(10), postzip_code char(10), suburb char(10), city char(10), state char(10), email char(10), country char(10) )

select a.reg_uid as id, a.firstname, a.lastname, a.postzip_code, a.suburb, a.city, a.state, a.email, a.country 
from register a
inner join 
(
 select distinct email, min(reg_uid) as id 
 from register 
  group by email 
) as b
on a.email = b.email 
and a.id = b.id