条件子句 [英] Conditional order by clause

问题描述

以下查询返回错误.帮助我根据错误以及实现条件排序的方式.我正在尝试按年级和名称排序(当年级> = 8时)，并按年级和分数排序(当年级< 8时).

Below query returning error. Help me under the error and a way to achieve the conditional order by. I am trying to order by grade and name when grade >=8 and grade and marks when grade <8.

SELECT
    name, grade, marks
FROM
    students, grades
WHERE
    min_mark <= marks
    AND   marks <= max_mark
    AND   marks >= 70
UNION
SELECT
    TO_CHAR('NULL') AS name, grade, marks
FROM
    students, grades
WHERE
    min_mark <= marks
    AND   marks <= max_mark
    AND   marks <= 69
order by grade desc,(case when grade >= 8 
                     then  name 
                     when grade < 8 
                     then marks  
                     end );

错误:
按等级desc排序(等级> = 8
时的情况*
第18行出现错误:
ORA-01785:ORDER BY项目必须是SELECT列表表达式的编号

ERROR:
order by grade desc,(case when grade >= 8
*
ERROR at line 18:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

推荐答案

这似乎是错误5695629，它似乎是针对10g提出的，而且似乎尚未修复(自12cR2起；我不知道)还没有18人可以玩)，这很不寻常.

This appears to be bug 5695629, which seems to have been raised against 10g and doesn't seem to have been fixed yet (as of 12cR2; I don't have 18 to play with yet), which is unusual.

您可以通过在订购前将查询包装在外部选择中来避免这种情况:

You can avoid it by wrapping the query in an outer select before ordering:

select name, grade, marks
from
(
    SELECT
        name, grade, marks
    FROM
        students, grades
    WHERE
        min_mark <= marks
        AND   marks <= max_mark
        AND   marks >= 70
    UNION
    SELECT
        TO_CHAR('NULL') AS name, grade, marks
    FROM
        students, grades
    WHERE
        min_mark <= marks
        AND   marks <= max_mark
        AND   marks <= 69
)
order by grade desc,case when grade >= 1 
                     then  name 
                     when grade < 1 
                     then  marks
                     end ;

但是，由于 name 和 marks 是(大概)不同的数据类型-字符串和数字-而是会得到

But as name and marks are (presumably) different data types - string and number - that will instead get

ORA-00932:数据类型不一致:预期的CHAR得到了NUMBER

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

您可以将 marks 转换为字符串，但是如果这样做，则需要对其进行填充，以便按字母顺序对结果字符串进行排序仍然符合数字顺序-混乱但合理，因为标记可以(再次，大概-如果是百分比?)最多只能输入三位数:

You could convert marks to a string, but if you do then you need to pad it so sorting the resulting string alphabetically still matches the numeric order - messy but plausible since the marks can (again, presumably - if it's a percentage?) only be up to three digits:

select name, grade, marks
from
(
    ...
    <the main part of your query here as a subquery, as above>
    ...
)
order by grade desc,case when grade >= 8 
                     then  name 
                     when grade < 8 
                     then  to_char(marks, 'FM000')
                     end ;

db小提琴演示，它使用通过CTE提供的一些虚拟数据

db<>fiddle demo using some dummy data supplied via CTEs.

如果标记可以超过三位数，则更改格式掩码以匹配最大可能长度.

If the marks can be more than three digits then change the format mask to match the maximum possible length.

TO_CHAR('NULL')部分也很奇怪，因为这将在这些行的名称列中为您提供文字字符串"NULL".由于您以字符串文字开头，因此 TO_CHAR()部分毫无意义，只需直接使用'NULL'AS名称.如果您实际上希望将其保留为空白，则可以使用 null AS name ，它将与并集的第一个分支中的匹配列表达式的数据类型相匹配(并且还将获取其别名)).您可以显式转换为字符串类型，例如 cast(null为varchar2(20))AS名称，但似乎没有什么意义.

The TO_CHAR('NULL') part is also odd as that will give you the literal string "NULL" in the name column for those rows. Since you start with a string literal the TO_CHAR() part is pointless, just use 'NULL' AS name directly. If you actually want it to be blank then you can just use null AS name and it will match the data type of the matching column expression from the first branch of the union (and will pick up its alias too). You could explicitly cast to a string type, e.g. cast(null as varchar2(20)) AS name but there doesn't seem to be much point.

这篇关于条件子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！