等待下一个秋

  • 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日 2366点热度 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/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

搜一搜
微信
最新 热点 随机
最新 热点 随机
ChatGPT可以做什么 ClickHouse 自定义分区键 ClickHouse数据副本引擎 ClickHouse ReplacingMergeTree引擎 ClickHouse MergeTree引擎 clickhouse简介
Spark面试题(六)——Spark资源调优 flink-cdc同步mysql数据到hbase 基于wordpress的本博客的搭建方法,遇到的问题与解决办法 Windows10总是自动切换为繁体 黑客与画家 15.设计与研究 一分钟了解蒙特卡洛方法
标签聚合
大数据 Java 挣钱 Flink Python Redis R语言 mysql 算法 书籍 Hive 数据仓库
文章归档
  • 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号