MySQL对varchar类字段的排序_mysql varchar 排序-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_48830183/article/details/126846379

问题描述：
今天在做一个BI需求时，因数据是从excel表格导入的。Excel表中有一个月份的字段，枚举值是按照一月、二月、三月、、、、、这样来的，所以在设计表的字段类型时选择了varchar型，可没想到后面要对这个字段进行分组求和并排序，采用group by+order by后发现月份无法正常排序。如下所示：

3306[bi]>select
    ->     Month as "投运月份",
    ->     sum(Revenue) as "2022年收入"
    -> from
    ->     revenue_flow_statement
    -> where
    ->     Associated = "自主"
    -> group by
    ->     Month 
    -> order by
    ->     Month  asc
    -> ;
+--------------+--------------------+
| 投运月份     | 2022年收入         |
+--------------+--------------------+
| 一月         |  39****11.86294877 |
| 七月         |  89****12.02000003 |
| 三月         | 13****359.80000003 |
| 二月         |  36****41.36000001 |
| 五月         | 10****164.03999999 |
| 八月         |  33****57.86000001 |
| 六月         |  44****280.9900001 |
| 四月         | 12****142.77999997 |
+--------------+--------------------+
8 rows in set (0.00 sec)

原因：是月份采用了varchar字段，MySQL无法正常排序。
解决方法：
将月份字段进行一下转换，可以选择+0或者*1等。
如下：

3306[bi]>select
    ->     Month as "投运月份",
    ->     sum(Revenue) as "2022年收入"
    -> from
    ->     revenue_flow_statement
    -> where
    ->     Associated = "自主"
    -> group by
    ->     Month 
    -> order by
    ->     Month*1  asc 
    -> ;
+--------------+--------------------+
| 投运月份     | 2022年收入         |
+--------------+--------------------+
| 一月         |  39****11.86294877 |
| 二月         |  36****41.36000001 |
| 三月         | 13****359.80000003 |
| 四月         | 12****142.77999997 |
| 五月         | 10****164.03999999 |
| 六月         |  44****280.9900001 |
| 七月         |  89****12.02000003 |
| 八月         |  33****57.86000001 |
+--------------+--------------------+
8 rows in set, 8 warnings (0.00 sec)

另一种方法：
可以考虑使用mysql的自定义排序方法-----采用filed字段自定义排序
如下图所示：

3306[bi]>select 
    -> a.month as "月份",
    -> a.in_revenue as "系统内收入",
    -> a.out_revenue as "系统外收入",
    -> a.2022_revenue as "2022自主小计",
    -> a.ass_in_revenue as "关联系统内收入",
    -> a.2022_revenue+a.ass_in_revenue as "合计"
    -> from
    -> (select
    ->     Month as month,
    ->     sum(if(Attribute = "自主" and System_type ='系统内',Revenue ,0)) as in_revenue,
    ->     sum(if(Attribute = "自主" and System_type ='系统外',Revenue ,0)) as out_revenue,
    ->     sum(if(Attribute = "自主" ,Revenue ,0)) as 2022_revenue,
    ->     sum(if(Attribute = "关联" and System_type = "系统内", Revenue,0)) as ass_in_revenue
    -> from
    ->     revenue_flow_statement
    -> group by
    ->     Month
    -> order by 
    ->    Month )a
    -> group by 
    -> a.month,a.in_revenue,a.out_revenue,a.2022_revenue,a.ass_in_revenue
    -> order by field(a.month,"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月") asc
    -> ;
+-----------+--------------------+--------------------+--------------------+-----------------------+--------------------+
| 月份      | 系统内收入         | 系统外收入         | 2022自主小计       | 关联系统内收入        | 合计               |
+-----------+--------------------+--------------------+--------------------+-----------------------+--------------------+
| 一月      |  15****35.48605777 |    23****76.376891 |  39****11.86294877 |    13****10.614513278 | 52900022.477462046 |
| 二月      | 16****.0199999998 |        34****52.34 |  36****41.36000001 |           13****32.27 |  49****73.63000001 |
| 三月      |          61****8.2 |        12****651.6 | 13****359.80000003 |    41****57.870000005 | 17****317.67000002 |
| 四月      |  30****76.94000001 |  93****65.84000002 | 12****142.77999997 |           54****93.77 | 17****036.54999998 |
| 五月      | 15****87.019999998 |  92****77.02000001 | 10****164.03999999 |           34****15.72 |       14****379.76 |
| 六月      |  13****245.1699999 |  30****035.8199999 |  44****280.9900001 |           25****20.39 |  46****301.3800001 |
| 七月      | 57****24.919999994 | 31****87.100000005 |  89****12.02000003 |           24****60.61 | 11****72.63000003 |
| 八月      | 24****20.250000015 |  84****7.610000001 |  33****57.86000001 |    45****12.179999985 |  79****70.03999999 |
| 九月      |                  0 |                  0 |                  0 |                     0 |                  0 |
| 十月      |                  0 |                  0 |                  0 |                     0 |                  0 |
| 十一月    |                  0 |                  0 |                  0 |                     0 |                  0 |
| 十二月    |                  0 |                  0 |                  0 |                     0 |                  0 |
+-----------+--------------------+--------------------+--------------------+-----------------------+--------------------+
12 rows in set (0.00 sec)

番外篇：MySQL设置变量rank排名排序

select 
@rowNum:=@rowNum+1 as ID,
t.*
from (
select
    a.num,
    a.scrapcauses 
    from 
    (
        select
            count(*) as num,
            scrapcauses
from
    mes_sn_scrap
group by
    scrapcauses) a
order by
    a.num desc) t ,
(select @rowNum:=0) b


+------+------+--------------------+
| ID   | num  | scrapcauses        |
+------+------+--------------------+
|    1 | 2580 | 切坏               |
|    2 |  564 | 起铜皮             |
|    3 |  388 | 漏铜               |
|    4 |  288 | 掉焊盘             |
|    5 |  244 | 掉点               |
|    6 |  236 | 内部短路           |
|    7 |  160 | 起銅皮             |
|    8 |  160 | 掉件               |
|    9 |  140 | 掉焊盤             |
|   10 |  124 | 烧板               |
|   11 |  116 | 掉锡               |
|   12 |  116 | 炸机               |
|   13 |  116 | U1掉焊盘           |
|   14 |  112 | 四线切坏           |
|   15 |   92 | 烧坏               |
|   16 |   68 | 测试压断           |
|   17 |   68 | 產綫切坏           |
|   18 |   64 | T1掉焊盘           |
|   19 |   52 | 溢锡               |
|   20 |   40 | 乱件               |
|   21 |   32 | 变形               |
|   22 |   28 | 无焊盘             |
|   23 |   24 | 掉铜皮             |
|   24 |   16 | 掉锡点             |
|   25 |   16 | 露铜               |
|   26 |   16 | u1起铜皮           |
|   27 |   16 | 压坏               |
|   28 |   16 | 少锡               |
|   29 |   16 | 掉漆               |
|   30 |   12 | 线路不通           |
|   31 |   12 | 线路断裂           |
|   32 |   12 | R27对地            |
|   33 |   12 | U3对地             |
|   34 |    8 | 撞伤               |
|   35 |    8 | 烧录坏             |
|   36 |    8 | L4接地             |
|   37 |    8 | R155掉焊盘         |
|   38 |    8 | R27接地            |
|   39 |    8 | 压断               |
|   40 |    8 | 大面积少料         |
|   41 |    8 | 少件               |
|   42 |    4 | 掉皮               |
|   43 |    4 | 掉锡炉             |
|   44 |    4 | 撞件               |
|   45 |    4 | 断线               |
|   46 |    4 | 无法切割           |
|   47 |    4 | 测试炸机           |
|   48 |    4 | 短路               |
|   49 |    4 | 短路烧板           |
|   50 |    4 | 线路压断           |
|   51 |    4 | 线路开路           |
|   52 |    4 | 线路烧坏           |
|   53 |    4 | 脏污               |
|   54 |    4 | C14掉焊盘          |
|   55 |    4 | L59接地            |
|   56 |    4 | L66线路不通        |
|   57 |    4 | U1 R31开路         |
|   58 |    4 | U1起銅皮           |
|   59 |    4 | U2掉焊盘           |
|   60 |    4 | U9掉焊盘           |
|   61 |    4 | 产线作业不良       |
|   62 |    4 | 内部线路开路       |
|   63 |    4 | 切不断             |
|   64 |    4 | 切换               |
|   65 |    4 | 划坏               |
|   66 |    4 | 压伤               |
|   67 |    4 | 大面积缺锡         |
|   68 |    4 | 少锡点             |
+------+------+--------------------+
68 rows in set (0.00 sec)