代码之家 › 专栏 › 技术社区 › Palendrone

MySQL使用UNION在两列上排序

union sorting mysql

Palendrone · 技术社区 · 3 年前

所以我有几个表,我想合并成一个列表。我对MySQL的功能知之甚少,并且已经意识到UNION需要相同数量的列。。。

一个表包含以下数据:

批次号
1.
2.
3.
4.
5.
6.
9
10
12

另一个有

批次号	子批次号
7.	1.
7.	2.
7.	3.
8.	1.
8.	2.
11	1.
11	2.

我基本上希望能够有这样的输出:

批次号
1.
2.
3.
4.
5.
6.
7-1
7-2
7-3
8-1
8-2
9
10

11-2
12

我有过各种尝试,但以下是我想出的最好的,但显然是排序错误。。。

SELECT batch_no FROM batch 
UNION
SELECT CONCAT(batch_no,'-',subbatch_no) FROM subbatch 
ORDER BY batch_no DESC

有了这个命令,看起来就像它是文本一样,因为我已经把连字符放进去了,不知道如何做到这一点。。。任何帮助或建议都将不胜感激。非常感谢。

4 回复 | 直到 3 年前

Barmar 3 年前

在原始联合体上进行排序,添加额外的 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

Kadet 3 年前

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

nbk 3 年前

事实上,很容易将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

lemon 3 年前

尝试以下操作:

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 .