ORA-01791 中的 DISTINCT 结果：不是 SELECTed 表达式

Question

select DISTINCT a.FNAME||' '||a.LNAME
   from AUTHOR a, books B, BOOKAUTHOR ba, customers C, orders
   where C.firstname='BECCA'
      and C.lastname='NELSON'
      and a.AUTHORID=ba.AUTHORID
      and b.ISBN=bA.ISBN
   order by a.LNAME

给出 ORA-01791: 不是 SELECTed 表达式 但没有 DISTINCT 就可以工作。

如何让它发挥作用？

Answer 1

只需在 select 子句中将 LNAME 作为单独的列添加即可：

SELECT full_name
FROM (
 select DISTINCT a.FNAME||' '||a.LNAME AS full_name, a.LNAME
 from AUTHOR a, books B, BOOKAUTHOR ba, customers C, orders
 where C.firstname='BECCA'
   and C.lastname='NELSON'
   and a.AUTHORID=ba.AUTHORID
   and b.ISBN=bA.ISBN
 )
order by a.LNAME

如果您只需要输出中的第一列，则可以将整个内容放入子查询中。