分组并比较字符串列中的数字 [英] Group and compare the number from string column

问题描述

我有一个包含字母数字内容的mysql表.该数字的格式正确为"/prop/xx/xxx "，因此我使用"substring"提取它们.但是我需要按描述对它们进行分组和排序.

I have a mysql table which contains alphanumeric content. The number is in the exact format '/prop/xx/xxx' so I use 'substring'to extract them. But I need to group and order them from its population by desc.

所以我理想的代码是这样的

So my ideal code like this

SELECT SUBSTRING(`page_url`,10,3) as pid from `prop_log` group by pid order by pid

我知道这段代码是不可能的，但是有什么建议吗?

I know it's impossible with this code but any advice?

PS. page_url 中的数字可以为4位或更多

PS. number in page_url can be 4 digits or more

推荐答案

您没有聚合功能，因此您应该使用不重复"而不是分组依据"

You have not aggregation function so you should use distinct and not group by

 SELECT distinct SUBSTRING(`page_url`,-3) as pid 
  from `prop_log` 
  order by pid

，如果您只需要数字行

 SELECT distinct SUBSTRING(`page_url`,-3) as pid 
  from `prop_log` 
  WHERE `page_url` REGEXP '[0-9]'
  order by pid

以及行数

 SELECT SUBSTRING(`page_url`,-3) as pid 
  from `prop_log` 
  WHERE `page_url` REGEXP '[0-9]'
  group by SUBSTRING(`page_url`,-3)
  order by count(*)

这篇关于分组并比较字符串列中的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！