在MySQL中对字符串字符进行排序 [英] Sorting string characters in MySQL

问题描述

我在mysql表中有一列(varchar(255))，我们称它为"word".如何编写一个选择查询以返回此列中按字符串中的字符排序的值?

I have a column (varchar(255)) in a mysql table, lets call it "word". How to write a select query that returns me the values in this column sorted by characters in the string?

例如，如果其中一个记录的单词为"earth"，那么对于所有行，它应该返回"aehrt"，依此类推.有没有办法在单个查询中做到这一点?

For example, if one of the records had the word "earth" it should return me "aehrt" and so on, for all the rows. Is there any way to do it in a single query?

推荐答案

效率可能很低，但是不需要用户定义的函数:

Probably highly inefficient, but without any need for user-defined functions:

SELECT GROUP_CONCAT(LETTER SEPARATOR '') AS ReOrderedLetters
  FROM ( SELECT 'A' as LETTER FROM <table> WHERE UPPER(`name`) like '%A%'
         UNION ALL
         SELECT 'B' as LETTER FROM <table> WHERE UPPER(`name`) like '%B%'
         UNION ALL
         SELECT 'C' as LETTER FROM <table> WHERE UPPER(`name`) like '%C%'
         UNION ALL
         SELECT 'D' as LETTER FROM <table> WHERE UPPER(`name`) like '%D%'
         ...
         UNION ALL
         SELECT 'Y' as LETTER FROM <table> WHERE UPPER(`name`) like '%Y%'
         UNION ALL
         SELECT 'Z' as LETTER FROM <table> WHERE UPPER(`name`) like '%Z%'
       ) alpha

编辑

上床睡觉之前，我不得不想出一个更好的选择，否则我就再也睡不着了.所以这是一种更清洁，更有效的选择.

I had to come up with a better alternative before going to bed, otherwise I'd never have got to sleep; so here's a much cleaner and more efficient alternative.

我创建了一个名为letter的表，其中有一个名为char的VARCHAR(1)列. 然后用字母A到Z填充该表

I created a table called letters with a single column of VARCHAR(1) called letter; then populated that table with the letters A to Z

CREATE TABLE IF NOT EXISTS `letters` (
   `letter` varchar(1) NOT NULL,
   PRIMARY KEY (`letter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `letters` (`letter`) VALUES
('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),
('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z');

然后:

select U.`name`,
       GROUP_CONCAT(L.`letter` 
           ORDER BY L.`letter` ASC 
          SEPARATOR '') AS ReOrderedLetters 
  FROM `users` U 
  LEFT JOIN `letters` L ON POSITION(L.`letter` IN UPPER(U.`name`)) > 0
 GROUP BY U.`name`

这篇关于在MySQL中对字符串字符进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！