mysql - 是否可以选择 MySQL 中相似行的计数？

Question

我有一个非常简单的表，用于记录页面访问者输入的邮政编码。邮政编码总是看起来像 DDDDCC，其中 D = 数字，C = 字符。我想为每一行选择一个计数，以显示有多少邮政编码与该行相似。

• 我知道我需要使用 SUBSTRING(postcode, 1, 4) 来获取前 4 位数字，但我不知道如何从那里继续

表结构(带有样本数据)

postcode  ip               datum                
--------  ---------------  ---------------------
1651ZX    8X.26.1X4.9X     2017-03-02 22:18:02  
1028HN    84.26.104.9X     2017-03-02 22:19:32  
1871EN    84.26.104.9X     2017-03-02 22:41:47  
1651QQ    84.2X.104.9X     2017-03-02 22:46:35  
1651ZX    188.206.X1.10X   2017-03-02 23:27:07  
1111AB    217.110.X01.13X  2017-03-03 09:12:19  
1871AA    188.206.X0.22X   2017-03-04 21:48:34  
1028HN    188.206.X0.22X   2017-03-04 23:42:19  
1823KA    84.105.X01.1X    2017-03-09 12:28:06  
1871EN    84.105.X01.1X    2017-03-09 12:29:13  
1823JM    84.105.X01.1X    2017-03-09 12:29:40  
1818AJ    84.105.201.14    2017-03-09 12:30:50  
1651ZX    84.105.201.14    2017-03-10 09:17:38 

我的预期输出是(如果我犯了错误请原谅我)

postcode    count
--------    -------
1651ZX         4
3028HN         2
1871EN         3
1651QQ         4
1651ZX         4
1111AB         1
1871AA         3
3028HN         2
1823KA         2
1871EN         3
1823JM         2
1818AJ         1
1651ZX         4

但是我该怎么做呢？

Answer 1

SELECT t1.postcode, t2.post_count
FROM yourTable t1
INNER JOIN
(
    SELECT SUBSTRING(postcode, 1, 4) AS code,
           COUNT(*) AS post_count
    FROM yourTable
    GROUP BY SUBSTRING(postcode, 1, 4)
) t2
    ON SUBSTRING(t1.postcode, 1, 4) = t2.code

此处演示:

Rextester