如何按照用户的期望排序和显示字母和数字的混合列表？

发布于 2024-07-13 15:33:29 字数 637 浏览 10 评论 0原文

我们的应用程序有一个 CustomerNumber 字段。我们有数百名不同的人使用该系统（每个人都有自己的登录名和自己的 CustomerNumber 列表）。单个用户最多可能拥有 100,000 个客户。许多人的数量少于 100。

有些人只在客户编号字段中输入实际数字，而另一些人则混合使用不同的数字。系统允许 20 个字符，可以是 AZ、0-9 或破折号，并将它们存储在 VARCHAR2(20) 中。任何小写字母在存储之前都会变成大写字母。

现在，假设我们有一个简单的报告，其中列出了特定用户的所有客户，并按客户编号排序。例如，

SELECT CustomerNumber,CustomerName
FROM Customer
WHERE User = ?
ORDER BY CustomerNumber;

这是一个幼稚的解决方案，因为只使用数字的人不希望看到简单的字母排序（其中“10”在“9”之前）。

我不想向用户询问有关其数据的任何不必要的问题。

我正在使用 Oracle，但我认为看到其他数据库的一些解决方案会很有趣。请注明您的答案适用于哪个数据库。

您认为实现这一目标的最佳方法是什么？

原文

Our application has a CustomerNumber field. We have hundreds of different people using the system (each has their own login and their own list of CustomerNumbers). An individual user might have at most 100,000 customers. Many have less than 100.

Some people only put actual numbers into their customer number fields, while others use a mixture of things. The system allows 20 characters which can be A-Z, 0-9 or a dash, and stores these in a VARCHAR2(20). Anything lowercase is made uppercase before being stored.

Now, let's say we have a simple report that lists all the customers for a particular user, sorted by Customer Number. e.g.

SELECT CustomerNumber,CustomerName
FROM Customer
WHERE User = ?
ORDER BY CustomerNumber;

This is a naive solution as the people that only ever use numbers do not want to see a plain alphabetic sort (where "10" comes before "9").

I do not wish to ask the user any unnecessary questions about their data.

I'm using Oracle, but I think it would be interesting to see some solutions for other databases. Please include which database your answer works on.

What do you think the best way to implement this is?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

差↓一点笑了 2024-07-20 15:33:29

也许您最好的选择是预先计算一个单独的列并将其用于订购并使用客户编号进行显示。这可能涉及将任何内部整数用 0 填充到固定长度。

另一种可能性是对返回的结果进行后选择排序。

Jeff Atwood 整理了一篇博客，发布了一些内容人们计算人类友好的排序顺序。

回复收藏 0 原文

情深如许 2024-07-20 15:33:29

在 Oracle 10g 中：

SELECT  cust_name
FROM    t_customer c 
ORDER BY
    REGEXP_REPLACE(cust_name, '[0-9]', ''), TO_NUMBER(REGEXP_SUBSTR(cust_name, '[0-9]+'))

这将按数字的第一次出现进行排序，而不考虑其位置，即：

customer1 customer1 customer1 customer1 customer1 customer1 customer1 客户2 < 客户10客户1omer？客户1 cust8omer1 ？ cust8omer2

，其中 ? 表示顺序未定义。

对于大多数情况来说这已经足够了。

要强制对情况 2 进行排序，您可以将 REGEXP_INSTR(cust_name, '[0-9]', n) 添加到 ORDER BY列出n次，强制在第n（2nd、3rd等）组第一次出现时排序的数字。

要对案例 3 强制排序，您可以将 TO_NUMBER(REGEXP_SUBSTR(cust_name, '[0-9]+', n)) 添加到 ORDER BY 列出 n 次，强制执行 n 的顺序。一组数字。

实际上，我写的查询就足够了。

您可以在这些表达式上创建基于函数的索引，但您需要使用提示强制执行它，并且无论如何都会执行一次 SORT ORDER BY ，因为 CBO< /code> 不信任基于函数的索引，不足以允许对其进行 ORDER BY 。

In Oracle 10g:

SELECT  cust_name
FROM    t_customer c 
ORDER BY
    REGEXP_REPLACE(cust_name, '[0-9]', ''), TO_NUMBER(REGEXP_SUBSTR(cust_name, '[0-9]+'))

This will sort by the first occurence of number, not regarding it's position, i. e.:

customer1 < customer2 < customer10
- cust1omer ? customer1
- cust8omer1 ? cust8omer2

, where a ? means that the order is undefined.

That suffices for most cases.

To force sort order on case 2, you may add a REGEXP_INSTR(cust_name, '[0-9]', n) to ORDER BY list n times, forcing order on the first appearance of n-th (2nd, 3rd etc.) group of digits.

To force sort order on case 3, you may add a TO_NUMBER(REGEXP_SUBSTR(cust_name, '[0-9]+', n)) to ORDER BY list n times, forcing order of n-th. group of digits.

In practice, the query I wrote is enough.

You may create a function based index on these expressions, but you'll need to force it with a hint, and a one-pass SORT ORDER BY will be performed anyway, as the CBO doesn't trust function-base indexes enough to allow an ORDER BY on them.

回复收藏 0 原文

情绪失控 2024-07-20 15:33:29

您可以有一个数字列 [CustomerNumberInt]，仅当 CustomerNumber 为纯数字时才使用（否则为 NULL [1]），然后

ORDER BY CustomerNumberInt, CustomerNumber

[1] 取决于您的 SQL 版本如何处理 ORDER BY 中的 NULL，您可能希望将其默认为零（或无穷大！）

You could have a numeric column [CustomerNumberInt] that is only used when the CustomerNumber is purely numeric (NULL otherwise[1]), then

ORDER BY CustomerNumberInt, CustomerNumber

[1] depending on how your SQL version handles NULLs in ORDER BY you might want to default it to zero (or infinity!)

回复收藏 0 原文

云裳 2024-07-20 15:33:29

我有一个类似的可怕情况，并开发了一个适当的可怕函数来处理它（SQLServer）

在我的情况下，我有一个“单元”表（这是学生的工作跟踪系统，因此在这种情况下的单元代表一门课程他们正在做）。单位有一个代码，大部分是纯数字的，但由于各种原因，它被做成了 varchar，并且他们决定在一些代码上添加最多 5 个字符的前缀。所以他们期望 53,123,237,356 能够正常排序，但 T53, T123, T237, T356

UnitCode is a nvarchar(30)

这是函数的主体：

declare @sortkey nvarchar(30)

select @sortkey = 
    case
        when @unitcode like '[^0-9][0-9]%' then left(@unitcode,1) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-1)
        when @unitcode like '[^0-9][^0-9][0-9]%' then left(@unitcode,2) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-2)
        when @unitcode like '[^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,3) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-3)
        when @unitcode like '[^0-9][^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,4) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-4)
        when @unitcode like '[^0-9][^0-9][^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,5) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-5)
        when @unitcode like '%[^0-9]%' then @unitcode
        else left('000000000000000000000000000000',30-len(@unitcode)) + @unitcode
    end 

return @sortkey

写完后我想朝自己的脸开枪，但它有效但似乎不起作用当服务器运行时杀死它。

I have a similar horrible situation and have developed a suitably horrible function to deal with it (SQLServer)

In my situation I have a table of "units" (this is a work-tracking system for students, so unit in this context represents a course they're doing). Units have a code, which for the most part is purely numeric, but for various reasons it was made a varchar and they decided to prefix some by up to 5 characters. So they expect 53,123,237,356 to sort normally, but also T53, T123, T237, T356

UnitCode is a nvarchar(30)

Here's the body of the function:

declare @sortkey nvarchar(30)

select @sortkey = 
    case
        when @unitcode like '[^0-9][0-9]%' then left(@unitcode,1) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-1)
        when @unitcode like '[^0-9][^0-9][0-9]%' then left(@unitcode,2) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-2)
        when @unitcode like '[^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,3) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-3)
        when @unitcode like '[^0-9][^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,4) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-4)
        when @unitcode like '[^0-9][^0-9][^0-9][^0-9][^0-9][0-9]%' then left(@unitcode,5) + left('000000000000000000000000000000',30-(len(@unitcode))) + right(@unitcode,len(@unitcode)-5)
        when @unitcode like '%[^0-9]%' then @unitcode
        else left('000000000000000000000000000000',30-len(@unitcode)) + @unitcode
    end 

return @sortkey

I wanted to shoot myself in the face after writing that, however it works and seems not to kill the server when it runs.

回复收藏 0 原文

浅沫记忆 2024-07-20 15:33:29

我在 SQL SERVER 中使用了它并且工作得很好：这里的解决方案是在数字值前面填充一个字符，以便所有的字符串长度都相同。

下面是使用该方法的示例：

select MyCol
from MyTable
order by 
    case IsNumeric(MyCol) 
        when 1 then Replicate('0', 100 - Len(MyCol)) + MyCol
        else MyCol
    end

100 应替换为该列的实际长度。

I used this in SQL SERVER and working great: Here the solution is to pad the numeric values with a character in front so that all are of the same string length.

Here is an example using that approach:

select MyCol
from MyTable
order by 
    case IsNumeric(MyCol) 
        when 1 then Replicate('0', 100 - Len(MyCol)) + MyCol
        else MyCol
    end

The 100 should be replaced with the actual length of that column.

回复收藏 0 原文

~没有更多了~