mysql按排序列值选择多个列组

作为第一种方法(如果时间允许),您应该考虑 normalizing 你的桌子,就像“草莓”里说的 answer

然而,第二种方法允许任意数量的列(尽管由于字符串操作和冒泡排序而效率低下),利用 User Defined Functions .

我们基本上需要创建一个函数,它可以对逗号分隔字符串中的值进行排序。我找到了一个工作函数,可以进行排序。从复制代码 here :

-- sort comma separated substrings with unoptimized bubble sort
DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
  DECLARE delim CHAR(1) DEFAULT ','; -- delimiter 
  DECLARE strings INT DEFAULT 0;     -- number of substrings
  DECLARE forward INT DEFAULT 1;     -- index for traverse forward thru substrings
  DECLARE backward INT;   -- index for traverse backward thru substrings, position in calc. substrings
  DECLARE remain TEXT;               -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
  DECLARE swap1 TEXT;                 -- left substring to swap
  DECLARE swap2 TEXT;                 -- right substring to swap
  SET remain = inString;
  SET backward = LOCATE(delim, remain);
  WHILE backward != 0 DO
    SET strings = strings + 1;
    SET backward = LOCATE(delim, remain);
    SET remain = SUBSTRING(remain, backward+1);
  END WHILE;
  IF strings < 2 THEN RETURN inString; END IF;
  REPEAT
    SET backward = strings;
    REPEAT
      SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
      SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
      IF  swap1 > swap2 THEN
        SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
        ,SUBSTRING_INDEX(inString,delim,backward-2)
        ,swap2,swap1
        ,SUBSTRING_INDEX(inString,delim,(backward-strings))));
      END IF;
      SET backward = backward - 1;
    UNTIL backward < 2 END REPEAT;
    SET forward = forward +1;
  UNTIL forward + 1 > strings
  END REPEAT;
RETURN inString;
END |
DELIMITER ;

您将需要在mysql服务器上运行此代码,以便此函数在查询中可用,就像本地内置的mysql函数一样。现在,查询部分变得简单了。你要做的就是 Concat_ws() 所有使用逗号的数字列。然后,申请 sortString() 连接字符串上的函数。最后,在 Group By 子句,以获得所需的结果。

尝试:

SELECT sortString(CONCAT_WS(',', n1, n2, n3)) AS n_sequence -- add more columns here
       COUNT(id) AS total 
FROM your_table 
GROUP BY n_sequence 
ORDER BY total DESC 

现在我建议您可以使用应用程序代码更改逗号分隔的 n_sequence 返回表格列显示。

我们只需要表达式对n1、n2和n3列中的值进行“排序”。如果我们有,我们可以做一个简单的 GROUP BY 和 COUNT .

SELECT COUNT(1) AS total
     , IF(t.n1<=t.n2,IF(t.n1<=t.n3,t.n1,t.n3),IF(t.n2<=t.n3,t.n2,t.n3)) AS n1s
     , IF(t.n1<=t.n2,IF(t.n2<=t.n3,t.n2,IF(t.n1<=t.n3,t.n3,t.n1)),IF(t.n1<=t.n3,t.n1,IF(t.n2<=t.n3,t.n3,t.n2 ))) AS n2s
     , IF(t.n1<=t.n2,IF(t.n2<=t.n3,t.n3,t.n2),IF(t.n1<=t.n3,t.n3,t.n1)) AS n3s
  FROM this_table_column_structure t
 GROUP BY n1s,n2s,n3s
 ORDER BY total DESC, n1s, n2s, n3s

将返回

total   n1s   n2s   n3s
-----  ----  ----  ----
    2     1     2     3
    2     5     6     7
    1     1     5     6
    1     3     5     6

考虑一下下面的一个标准化数据集…

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL
,n INT NOT NULL
,val INT NOT NULL
,PRIMARY KEY(id,n)
);

INSERT INTO my_table VALUES
(1, 1, 3),
(1, 2, 2),
(1, 3, 1),
(2, 1, 6),
(2, 2, 5),
(2, 3, 7),
(3, 1, 2),
(3, 2, 3),
(3, 3, 1),
(4, 1, 1),
(4, 2, 6),
(4, 3, 5),
(5, 1, 5),
(5, 2, 6),
(5, 3, 7),
(6, 1, 3),
(6, 2, 5),
(6, 3, 6);

这里有一个快速(写)和肮脏的解决方案。提供更快/更优雅的解决方案…

SELECT vals
     , COUNT(*) total
  FROM 
     ( SELECT id
            , GROUP_CONCAT(val ORDER BY val) vals 
         FROM my_table 
        GROUP 
           BY id
     ) x 
 GROUP 
    BY vals;
+-------+-------+
| vals  | total |
+-------+-------+
| 1,2,3 |     2 |
| 1,5,6 |     1 |
| 3,5,6 |     1 |
| 5,6,7 |     2 |
+-------+-------+