作为第一种方法(如果时间允许),您应该考虑
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
返回表格列显示。