如何在MYSQL中对列进行排序
如何在MYSQL中对列进行排序
提问于 2018-07-24 17:31:59
我每行有三个字段，我想将它们连接起来，按升序排序：
select * concat(field1, field2, field3) AS result
from tbl 我的数据是：
field1, field2, field3
7 2 9
4 7 8
6 3 2 预期的结果是：
field1, field2, field3 result
7 2 9 279
4 7 8 478
6 3 2 236我该怎么做呢？
回答 4
Stack Overflow用户
回答已采纳
发布于 2018-07-24 18:01:43
从技术上使用LEAST、GREATEST函数
SELECT field1, field2, field3,
concat(LEAST(field1,field2,field3),
(field1 + field2 + field3) - (GREATEST(field1,field2,field3)+
LEAST(field1,field2,field3)),
GREATEST(field1,field2,field3)
)
from tblStack Overflow用户
发布于 2018-07-24 18:20:21
您可以尝试GROUP_CONCAT方法。它可以扩展到任意数量的列：
CREATE TABLE testdata(id INT PRIMARY KEY, field1 INT, field2 INT, field3 INT);
INSERT INTO testdata (id, field1, field2, field3) VALUES
(1, 7, 2, 9),
(2, 4, 7, 8),
(3, 6, 3, 2);
SELECT testdata.id, field1, field2, field3, GROUP_CONCAT(rtc.fields ORDER BY fields SEPARATOR '') AS fields_sorted
FROM testdata
INNER JOIN (
SELECT id, field1 AS fields FROM testdata UNION ALL
SELECT id, field2 AS fields FROM testdata UNION ALL
SELECT id, field3 AS fields FROM testdata
) AS rtc ON testdata.id = rtc.id
GROUP BY testdata.id, field1, field2, field3;输出：
+----+--------+--------+--------+---------------+
| id | field1 | field2 | field3 | fields_sorted |
+----+--------+--------+--------+---------------+
| 1 | 7 | 2 | 9 | 279 |
| 2 | 4 | 7 | 8 | 478 |
| 3 | 6 | 3 | 2 | 236 |
+----+--------+--------+--------+---------------+Stack Overflow用户
发布于 2018-07-24 17:53:31
请尝试这个解决方案，尽管我还没有测试过它。你将能够通过稍微修修补补来达到你想要的效果。如有任何改进，请在评论中提及。
select id, concat(MIN_VAL, MIDDLE_VAL, MAX_VAL)
from
( SELECT ID,
(CASE
WHEN field1 >= field2 AND field1 >= field3 THEN field1
WHEN field2 >= field1 AND field2 >= field3 THEN field2
WHEN field3 >= field1 AND field3 >= field2 THEN field3
ELSE field1 END) MAX_VAL
,Case When field1 < field2 And field1 < field3 Then field1
When field2 < field1 And field2 < field3 Then field2
Else field3
End As MIN_VAL
,Case When (field1 >= field2 And field1 <= field3) OR (field1 <= field2 And field1 >= field3) Then field1
When (field2 >= field3 And field2 <= field1) OR (field2 <= field3 And field2 >= field1)Then field2
WHEN (field3 >= field1 AND field3 <= field2) OR (field3 <= field1 AND field3 >= field2) THEN field3
Else field1
End As MIDDLE_VAL
From table
) int_pass页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51495205
复制相关文章
相似问题