查询 - 按返回重复行一列此查询

Question

我有查询：

SELECT DISTINCT 
"id", 
        "fn" || ' ' || "ln" || '/' || "bi" || '('|| TRUNC((months_between(sysdate, "bi") /12)) || ')/' || "pol" || '/ ' || "pol2" AS "INFO", 
    "cn" , 
    "ci", 
    TO_CHAR("di" , 'DD-MON-YYYY') AS "DIST", 
    "cif" AS "Cand. Indp." AS "CIF FLAG", 
    TO_CHAR("ecd", 'DD-MON-YYYY') AS "ECD DATE", 
    trim(both '/' from ("DSD")) AS "DSD DATE", 
    trim(both '/' from ("TSD" )) AS "TSD DATE", 
    "com" AS "COMMENTS", 
    "SMF" AS "SM Flag" 
FROM "MY_TABLE" 
ORDER BY "ln"; 

我得到ORA-01791：不是一个选择的表达。

我GOOGLE了错误和问题似乎是独特的和顺序通过不一起工作。我试图通过分组而不是明确的并使用max（“ln”），但是对于连接的列，我无法弄清楚如何对它们进行分组。我正在寻找如何对ln上的结果进行排序？

Answer 1

您不受ORDER BY “LN” 选择

只能通过选择列，然后选择或添加选择

SELECT DISTINCT 
    "id", 
    "fn" || ' ' || "ln" || '/' || "bi" || '('|| TRUNC((months_between(sysdate, "bi") /12)) || ')/' || "pol" || '/ ' || "pol2" AS "INFO", 
    "ln", 
    "cn" , 
    "ci", 
    TO_CHAR("di" , 'DD-MON-YYYY') AS "DIST", 
    "cif" AS "Cand. Indp." AS "CIF FLAG", 
    TO_CHAR("ecd", 'DD-MON-YYYY') AS "ECD DATE", 
    trim(both '/' from ("DSD")) AS "DSD DATE", 
    trim(both '/' from ("TSD" )) AS "TSD DATE", 
    "com" AS "COMMENTS", 
    "SMF" AS "SM Flag" 
FROM "MY_TABLE" 
ORDER BY "ln"; 

或

SELECT DISTINCT 
    "id", 
    "fn" || ' ' || "ln" || '/' || "bi" || '('|| TRUNC((months_between(sysdate, "bi") /12)) || ')/' || "pol" || '/ ' || "pol2" AS "INFO", 
    "cn" , 
    "ci", 
    TO_CHAR("di" , 'DD-MON-YYYY') AS "DIST", 
    "cif" AS "Cand. Indp." AS "CIF FLAG", 
    TO_CHAR("ecd", 'DD-MON-YYYY') AS "ECD DATE", 
    trim(both '/' from ("DSD")) AS "DSD DATE", 
    trim(both '/' from ("TSD" )) AS "TSD DATE", 
    "com" AS "COMMENTS", 
    "SMF" AS "SM Flag" 
FROM "MY_TABLE" 
ORDER BY 2;