按数值和字典顺序对字段进行排序 [英] Sorting field by numerical value and lexicographical value

问题描述

我有一张订单表.对于每个订单，我们允许用户输入其(非唯一)订单号.无论他们想要什么，我们都能做到.

我将这些订单显示在HTML表格中，并能够按各个字段(例如订单编号)对订单进行排序.

我们的一位客户注意到排序问题.由于订单号存储为VARCHAR，因此按字典顺序对其进行排序.问题是，并非所有订单号都是数字，有些不是单词，有些是字母数字.

例如，我可以有这样的订单号:

42
Order8
MyOrder
9
Order63

使用ORDER BY orderNumber进行排序时，我得到:

42
9
MyOrder
Order63
Order8

演示: http://sqlfiddle.com/#!2/7973e/1

这不是我想要的.我希望对它们进行如下排序:

9
42
MyOrder
Order8
Order63

我希望它们对于字符串是字典式的，但对于数字而言是数字的.我想到了一些可行的方法:

ORDER BY IFNULL(NULLIF(CAST(orderNumber AS SIGNED), 0), orderNumber)

演示: http://sqlfiddle.com/#!2/7973e/2

但是，我仍然得到相同的结果(因为随后将数字重新转换为字符串).如何以所需的方式对这些值进行排序?如果只有某种方法可以将字符串转换"为某种数值.

解决方案

您可以使用以下内容接近所需的内容:

ORDER BY CASE WHEN CONVERT(OrderNumber, SIGNED INTEGER)= 0 
         THEN 1e50
         ELSE CONVERT(OrderNumber, SIGNED INTEGER)
         END ASC, OrderNumber ASC

但是，如果您需要按其后一位数字(例如Order63，Order8)对混合的(文本/数字)订单号进行排序，则需要做更多的工作.

I have a table of orders. For each order, we allow the user to enter their (non-unique) order number. This can we whatever they want.

I have these orders displayed in an HTML table, with the ability to sort the orders by various fields, such as order number.

One of our clients noticed an issue with the sorting. Since the order numbers are stored as VARCHAR, they are sorted lexicographically. Problem is, not all order numbers are numerc, some are words, and others are alphanumeric.

So, for example, I can have order numbers like so:

42
Order8
MyOrder
9
Order63

When sorted using ORDER BY orderNumber, I get:

42
9
MyOrder
Order63
Order8

DEMO: http://sqlfiddle.com/#!2/7973e/1

This is not what I want. I want them to be sorted like so:

9
42
MyOrder
Order8
Order63

I want them to be lexicographical for the strings, but numeric for the numbers. I thought of something that might work:

ORDER BY IFNULL(NULLIF(CAST(orderNumber AS SIGNED), 0), orderNumber)

DEMO: http://sqlfiddle.com/#!2/7973e/2

But alas, I still get the same results (as the numbers are then re-cast back to strings). How can I sort these values in the way that I want? If only there was some way to "convert" the strings into a sort of numerical value.

解决方案

You can get close to what you want with the following:

ORDER BY CASE WHEN CONVERT(OrderNumber, SIGNED INTEGER)= 0 
         THEN 1e50
         ELSE CONVERT(OrderNumber, SIGNED INTEGER)
         END ASC, OrderNumber ASC

However, it would need more work if you need to sort a mixed (text/number) order number by its last digits (e.g. Order63, Order8).

这篇关于按数值和字典顺序对字段进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！