MySQL GROUP by Regex?

Question

我有以下查询

SELECT Count(*) as Total_Count, Col1 
FROM Table1 
GROUP BY Col1 
ORDER BY Total_Count DESC;

我想放大第1列。Col1中的数据采用以下格式：

text-abc1
txt4-abcde22
tex6-abc2
text4-imp4
text-efg1
txt-efg43

我希望能够通过对其进行分组

After the first `-`, any first three/four/five characters match

在本例中，如果我们匹配前3个字符。输出为：

Total_Count   Col1
3             abc
1             imp
2             efg

还有其他方法可以做到这一点吗？

Answer 1

您可能不需要正则表达式，只需要字符串操作。对于三个字符：

SELECT count(*) AS Total_Count,
SUBSTRING(Col1 FROM POSITION('-' in Col1)+1 FOR 3) AS Col1_zoomed
FROM Table1
GROUP BY Col1_zoomed
ORDER BY Total_Count DESC

Answer 2

select
substring(substring_index(col1,'-',-1),1,3) as grp,
count(*) as total
from table
group by grp

Answer 3

这应该是您想要的。

SELECT Count(*) as Total_Count, SUBSTRING(Col1, 1, 3)
FROM Table1 
GROUP BY SUBSTRING(Col1, 1, 3) 
ORDER BY Total_Count DESC;

Answer 4

我想回答这个问题"MySQL GROUP by Regex"因为这里的答案解决了所提供的问题。

您可以使用REGEXP_SUBSTR((函数通过REGEXP进行分组。

REGEXP_SUBSTR(expr，pat[，pos[，occurrence[，match_type]]](

返回字符串expr中与正则表达式匹配的子字符串由模式pat指定的表达式，如果不匹配则为NULL。如果expr或pat为NULL，则返回值为NULL。

例如：

SELECT 
    *
FROM
    YOUR_TABLE
GROUP BY REGEXP_SUBSTR(YOUR_COLUMN, 'YOUR REGEXP');

参考：https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-子

Answer 5

SELECT Count(*) as Total_Count, Col1, REGEXP_SUBSTR(Col1, '[a-z0-9]*', 0, 2) as Col1_combined 
FROM Table1 
GROUP BY REGEXP_SUBSTR(Col1, '[a-z0-9]*', 0, 2) 
ORDER BY Total_Count DESC;

REGEXP_SUBSTR从文本中的位置0开始返回与给定正则表达式[a-z0-9]匹配的字符串，并返回2的出现。REGEXP_SUBSTR 文档

REGEXP_SUBSTR(<column_name>, <regular_expression>, <starting_position>, <match_occurrence>)