MySQL GROUP by Regex？
MySQL GROUP by Regex？
提问于 2011-11-26 03:20:14
我有以下查询
SELECT Count(*) as Total_Count, Col1
FROM Table1
GROUP BY Col1
ORDER BY Total_Count DESC;我想放大Col1。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有没有其他方法可以做到这一点？
回答 4
Stack Overflow用户
回答已采纳
发布于 2011-11-26 03:36:19
您可能不需要正则表达式，只需要字符串操作。对于三个字符：
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 DESCStack Overflow用户
发布于 2011-11-26 03:49:45
select
substring(substring_index(col1,'-',-1),1,3) as grp,
count(*) as total
from table
group by grpStack Overflow用户
发布于 2011-11-26 03:37:18
这应该可以做你想要的事情。
SELECT Count(*) as Total_Count, SUBSTRING(Col1, 1, 3)
FROM Table1
GROUP BY SUBSTRING(Col1, 1, 3)
ORDER BY Total_Count DESC;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8273405
复制相关文章
相似问题
腾讯云开发者
