DISTINCT结果为ORA-01791:不是SELECTED表达式 [英] DISTINCT results in ORA-01791: not a SELECTed expression

问题描述

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.

gives ORA-01791: not a SELECTed expression but works without DISTINCT.

如何使其工作?

推荐答案

只需将LNAME作为自己的列添加到select子句中即可:

Just add LNAME as a column on its own in the select clause:

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

如果只希望输出中的第一列，则可以将整个内容放在子查询中.

If you only want the first column in the output, you can put the whole thing in a subquery.

这篇关于DISTINCT结果为ORA-01791:不是SELECTED表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！