mysql - 按最后 4 个字符分组并显示该组

Question

我在 mytable 表中有一个 lastname varchar(50) 列。

我会按最后 4 个字母拆分组中的lastname 列，并对这些组进行计数。

有什么方法可以做到这一点？

刚刚添加:这是正确的吗？

select substr(lastname,-4) as alpha, count(lastname)
  from mytable
 group by substr(lastname,-4)

新更新:

select right(lastname,4) as alpha, count(lastname) as total
  from orig
 group by alpha
 order by total desc;

Answer 1

不知道MySQL是否支持在group by中这种非标准的列别名使用:

select right(lastname,4) as alpha,
       count(lastname) as total
from orig
group by alpha
order by total desc;

或者，也许:

select alpha, count(*) as total
from (select right(lastname,4) as alpha from orig)
group by alpha
order by total desc;