SQL发行顺序(续) [英] SQL order by Issue-Continued

问题描述

此问题是我的早期查询的延续.它仍然无法正常工作. 它是关于ORDER BY子句的.我正在尝试使用名为"sortby"的变量进行排序.

This issue is a continuation of my earlier query. It's still not working. It's about an ORDER BY clause. I am trying to sort using a variable called "sortby".

在这里，现在使用DECODE()函数将ORDER BY子句选择为单独的列(如该问题的原始版本中@devio的答案中所建议的那样).

Here, now the ORDER BY clause is selected as a separate column using the DECODE() function (as suggested in the answer by @devio in the original version of this question).

在这种情况下，假设sortby ="memberCount"，我将其作为第一个参数传递给了decode()； memberCount 是grptest表中的一个列.

Let’s say sortby = ‘memberCount’ in this case, I passed it as first argument in decode(); memberCount is a COLUMN in the grptest table.

select distinct gl.group_id,
       decode('memberCount', 'name',        gl.group_name_key,
                             'description', gl.group_description_key,
                             'memberCount', gl.member_count)
             as p_sortby,
       gl.group_name,
       gl.group_description,
       gl.status_code,
       gl.member_count,
       (select grpp.group_name
           from grptest_relationship grel join grptest grpp
                   on grel.parent_group_id = grpp.group_id
           where grel.child_group_id = gl.group_id) as parent_group_name,
       gl.group_name_key,
       gl.group_description_key
   from grptest gl
   where gl.group_org_id = '3909'
     and (gl.group_name_key like '%' || 'GROUP' || '%')
order by 2;

它不起作用.

但是，如果我在上面的解码中将名称"作为第一个参数传递，它将起作用. 那是我最初的问题，为什么它不适用于memberCount.

But if I pass ‘name’ as first argument in decode above, it works. That’s my original issue about why it doesn’t apply on memberCount.

推荐答案

我评论了:

您得到什么错误?或您得到的错误行为是什么?在使您的问题适应数据库时，我必须确保在DECODE()可以接受之前，将数字列转换为字符类型-其他两列为字符列.完成此操作后，再加上一个较小的问题，即按字母顺序对数字进行排序，将"8"放在"79"之后，然后在"80"之前，我得到了一个合适的结果.

What is the error you get? Or what is the erroneous behaviour you get? In my adaptation of your question to my database, I had to ensure that the numeric column was converted to a character type before the DECODE() was acceptable - the other two columns were character columns. With that done, and with the minor issue that sorting numbers alphabetically places '8' after '79' and before '80', I got an appropriate result.

罗希特问:

感谢您的投入.我想我对您提到的一个小问题感到困惑，即按字母顺序将数字8放在'79'之后和'80'之前".我不明白这里是什么东西?另外，在我的查询中如何确保在DECODE()可以接受之前将数字列转换为字符类型"方面，您能提供帮助吗?您可以在这方面修改上面的查询吗?

Thanks for the inputs. I guess I am confused about the minor issue you mentioned that "that sorting numbers alphabetically places '8' after '79' and before '80'". I couldn't get what is the thing here? Also, could you please help in my query of how "to ensure that the numeric column was converted to a character type before the DECODE() was acceptable". Can you please modify my query above in this respect?

---

我使用的表用于元素表":

---

The table I used is for the 'table of elements':

-- Tables for storing information about chemical elements and chemical compounds
-- See: http://www.webelements.com/ for elements.
-- See: http://ie.lbl.gov/education/isotopes.htm for isotopes.

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE,
    name            CHAR(20) NOT NULL UNIQUE,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

这是一个有趣的表，因为它具有三个真正的候选键(原子序号，名称和符号都是唯一的)，并且根据上下文(同位素与化学物质)，最好使用原子序号或符号作为连接键.

It's an interesting table because it has three genuine candidate keys (atomic number, name and symbol are each unique), and depending on context (isotopes vs chemicals), you are better off using atomic number or symbol as the joining key.

我使用的查询是:

select decode('atomic_number',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', atomic_number||''),
        name, symbol, atomic_number
    from elements
    order by 1;

select decode('name',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', atomic_number||''),
        name, symbol, atomic_number
    from elements
    order by 1;

select decode('symbol',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', atomic_number||''),
        name, symbol, atomic_number
    from elements
    order by 1;

这些演示了三种顺序-按符号，按名称和按原子序数.

These demonstrated the three orderings - by symbol, by name, and by atomic number.

原子序号的部分结果集是:

Part of the result set for the atomic number ordering was:

77      Iridium         Ir      77
78      Platinum        Pt      78
79      Gold            Au      79
8       Oxygen          O       8
80      Mercury         Hg      80
81      Thallium        Tl      81

因为原子序号被强制为字符串，所以排序是按字符串顺序进行的，并且当视为字符串时，如图所示，在"79"之后和"80"之前出现"8".避免该问题的一种方法是:

Because the atomic number was coerced into a string, the sort was in string order, and when regarded as a string, '8' appears after '79' and before '80', as shown. One way of avoiding that problem would be:

select decode('atomic_number',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', lpad(atomic_number, 3)),
        name, symbol, atomic_number
    from elements
    order by 1;

产生以下内容(尽管不明显，但在第一列的开头有一个空白):

Producing the following (which, though it isn't obvious, has an extra blank at the start of the first column):

 77     Iridium         Ir      77
 78     Platinum        Pt      78
 79     Gold            Au      79
 80     Mercury         Hg      80
 81     Thallium        Tl      81
 82     Lead            Pb      82

使用以下知识:空格在(ASCII，Latin-1，Unicode)排序序列中的任何数字之前，并且原子序数不超过3个数字.另外，我本可以使用'LPAD(atomic_number, 3, '0')'对数据进行零填充.我在Solaris 10上使用IBM Informix Dynamic Server(IDS)11.50.FC3W2进行了测试.IDS可以很好地容忍类型不匹配，并且可以将atomic_number参数自动转换为LPAD并转换为字符串.其他DBMS可能没有那么宽容.您必须显式地转换值.

This uses the knowledge that space precedes any digit in the (ASCII, Latin-1, Unicode) sort sequence, and that atomic numbers are not more than 3 digits. Alternatively, I could have used 'LPAD(atomic_number, 3, '0')' to zero-pad the data. I tested with IBM Informix Dynamic Server (IDS) 11.50.FC3W2 on Solaris 10. IDS is very tolerant of type mismatches and automatically converts the atomic_number argument to LPAD into a string. Other DBMS may not be so tolerant; you'd have to explicitly cast the value.

回到问题...

假设memberCount是一个数字列，并且值的长度不超过4位(如果较长则适当调整)，可以编写查询:

Assuming memberCount is a numeric column and the values are not more than 4 digits long (adjust appropriately if they are longer), the query can be written:

select distinct gl.group_id,
       decode('memberCount', 'name',        gl.group_name_key,
                             'description', gl.group_description_key,
                             'memberCount', LPAD(gl.member_count, 4))
             as p_sortby,
       gl.group_name,
       gl.group_description,
       gl.status_code,
       gl.member_count,
       (select grpp.group_name
           from grptest_relationship grel join grptest grpp
                   on grel.parent_group_id = grpp.group_id
           where grel.child_group_id = gl.group_id) as parent_group_name,
       gl.group_name_key,
       gl.group_description_key
   from grptest gl
   where gl.group_org_id = '3909'
     and (gl.group_name_key like '%' || 'GROUP' || '%')
order by 2;

或者您可能需要:

LPAD(CAST(memberCount AS CHAR(4)), 4)

或其他一些DBMS特有的咒语，可以达到相同的一般效果.

or some other slightly DBMS-specific incantation that achieves the same general effect.

由于您没有为查询提供模式(更少的示例数据)，所以数据库中没有您的表，因此我无法演示您的查询工作

Since you didn't provide a schema (much less sample data) for the query, I don't have your table in my database, so I can't demo your query working

这篇关于SQL发行顺序(续)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！