等待下一个秋

  • Spark
  • Flink
  • Hive
  • 数据仓库
  • ClickHouse
  • 收徒弟
  • Java
    • Spring
    • Mybatis
    • SpringBoot
    • 面试题
  • Python
    • Python基础
    • 爬虫
    • Numpy
    • matplotlib
    • Flask
  • 技术杂谈
    • Linux知识
    • Docker
    • Git教程
    • Redis教程
    • mysql
    • 前端
    • R语言
    • 机器学习
  • 关于我
  • 其它
    • 副业挣钱
    • 资料下载
    • 资料文档
专注于Hadoop/Spark/Flink/Hive/数据仓库等
关注公众号:大数据技术派,获取更多学习资料。
  1. 首页
  2. 技术杂谈
  3. mysql
  4. 正文

mysql order by如何实现自然排序

2021年10月13日 2451点热度 0人点赞 0条评论

摘要:在本教程中,您将了解MySQL如何使用ORDER BY子句实现自然排序技术。

假设我们有一个名为items的表,包含两列:id和item_no。要创建items表,我们使用CREATE TABLE语句,sql语句如下:

CREATE TABLE IF NOT EXISTS items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);

我们使用INSERT语句将一些数据插入到items表中:

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');

当我们查询数据并按照item_no进行排序时,我们得到以下结果:

SELECT 
    item_no
FROM
    items
ORDER BY item_no;

这不是我们希望看到的结果。我们预期的结果如下:

这被称为自然排序。不幸的是,MySQL不提供任何内置的自然排序语法或函数。

为了实现以上排序规则,首先我们将item_no列分成2列:prefix和suffix。prefix列存储item_no的数字部分,suffix列存储item_no的字母部分。然后,我们可以根据prefix和suffix列对数据进行排序,如下所示:

SELECT 
    CONCAT(prefix, suffix)
FROM
    items
ORDER BY prefix , suffix;

sql语句首先按prefix排序,然后按suffix排序。这样我们得到预期的排序结果。

这个解决方案的缺点是我们必须在插入或更新item_no之前将它分成两部分。另外,当我们查询数据时,我们必须将两列合并成一列。

如果item_no数据是相当标准的格式,您可以使用以下sql查询实现自然排序而不更改表结构。

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

在这个sql语句中,首先我们使用类型转换将item_no转换为无符号整数。然后,我们使用ORDER BY子句首先按数字顺序排序,然后按字母顺序排序。

让我们来看看我们经常需要处理的另一组常见数据。

TRUNCATE TABLE items;
 
INSERT INTO items(item_no)
VALUES('A-1'),
      ('A-2'),
      ('A-3'),
      ('A-4'),
      ('A-5'),
      ('A-10'),
      ('A-11'),
      ('A-20'),
      ('A-30');

排序后的预期结果如下:

为了实现这个结果,我们可以使用LENGTH函数。请注意,LENGTH函数返回字符串的长度。其想法是按照item_no字段长度首先对结果集进行排序,然后按item_no字段值对结果集进行排序:

SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;

这样就实现了自然排序。

如果上面的方法不能解决自然排序, 那么,您需要在应用程序层中执行自然排序。一些语言支持自然排序功能,例如,PHP提供了对数组使用自然排序算法的函数natsort()。

在本教程中,我们向您介绍了在MySQL中自然排序的实现思路和方法。


标签: mysql
最后更新:2021年10月13日

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

打赏 点赞
< 上一篇
下一篇 >

文章评论

取消回复

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

搜一搜
微信
最新 热点 随机
最新 热点 随机
深入理解ClickHouse跳数索引 ClickHouse主键索引最佳实践 Java和Python操作Clickhouse ChatGPT 注册教程 ChatGPT可以做什么 ClickHouse 自定义分区键
cdh中yarn调度spark,container资源倾斜,container集中于一台或几台机器 使用Python作为粘合剂 Flink之Watermark详解 十大经典排序算法——基数排序 数仓建模—指标体系 新一代大数据计算引擎 Flink从入门到实战
标签聚合
Hive 数据仓库 R语言 Python 书籍 挣钱 Flink 算法 大数据 Java Redis mysql
文章归档
  • 2023年4月
  • 2023年3月
  • 2023年2月
  • 2022年12月
  • 2022年11月
  • 2022年9月
  • 2022年7月
  • 2022年6月
  • 2022年5月
  • 2022年4月
  • 2022年3月
  • 2022年2月
  • 2022年1月
  • 2021年12月
  • 2021年11月
  • 2021年10月
  • 2021年9月
  • 2021年8月
  • 2021年6月
  • 2021年5月
  • 2021年4月
  • 2021年3月
  • 2021年2月
  • 2021年1月
  • 2020年12月
  • 2020年11月
  • 2020年10月
  • 2020年9月
  • 2020年8月
  • 2020年7月
  • 2020年5月
  • 2020年4月
  • 2020年1月
  • 2019年9月
  • 2019年8月
  • 2019年7月
  • 2019年6月
  • 2019年5月
  • 2019年4月
  • 2019年3月
  • 2019年1月
  • 2018年12月
  • 2017年5月

©2022 ikeguang.com. 保留所有权利。

鄂ICP备2020019097号-1

鄂公网安备 42032202000160号