MySQL使用UNION在两列上排序

在原始联合体上进行排序,添加额外的 subbatch_no 列到 batch 子查询。然后在主查询中按两列将它们排序,在主查询中还可以将它们与 - 分离器。

SELECT CONCAT_WS('-', batch_no, subbatch_no) AS combined_batch_no
FROM (
    SELECT batch_no, NULL AS subbatch_no
    FROM batch
    UNION
    SELECT batch_no, subbatch_no
    FROM subbatch
) AS sub1 
ORDER by batch_no, subbatch_no

select CONCAT_WS('-',batch_no,subbatch_no) from (
  SELECT batch_no,null subbatch_no FROM batch 
  UNION ALL
  SELECT batch_no,subbatch_no FROM subbatch 
) A
ORDER BY batch_no,subbatch_no

事实上,很容易将apha numrical QI与您的数据进行排序

SELECT CAST(batch_no as char) batchno FROM batch 
UNION 
SELECT CONCAT(batch_no,'-',subbatch_no) FROM subbatch 
ORDER BY batchno+0 ASC

| batchno |
| :------ |
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
| 7-1     |
| 7-2     |
| 7-3     |
| 8-1     |
| 8-2     |
| 9       |
| 10      |
| 11-1    |
| 11-2    |
| 12      |

数据库(&L)&燃气轮机;不停摆弄 here

尝试以下操作:

WITH cte AS (
    SELECT batch_no, NULL as subbatch_no FROM tab1
    UNION ALL
    SELECT * FROM tab2
)
SELECT CONCAT_WS('-' , batch_no, subbatch_no) AS batch_no
FROM cte
ORDER BY cte.batch_no, cte.subbatch_no

查看演示 here .