mysql：使用返回重复行计数

i have an issue on the below

我在下面有一个问题

table1

first case :

第一种情况:

postcode
LS1 1LS

second case:

postcode
LS11LS

table2

    postcode| region
    LS1 | Leeds
    LS11    | Leeds

and i am using the below query to count the region versus related postcode

我正在使用以下查询来计算区域与相关的邮政编码

SELECT table2.region as 'Region', COUNT( table2.postcode ) as  'count'
FROM table1
INNER JOIN table2 ON table1.postcode LIKE CONCAT( table2.postcode,  '%' ) 
WHERE table1.postcode >  ''
GROUP BY Region 

on applying that on table 1 first case i get

我在第一个案例中应用它

Leeds | 1

on applying it on table 1 second case i get

将它应用于表1第二种情况我得到了

Leeds | 2

for some reason on this postcode / similar postcodes where the other half is exactly like the first part and there is no spaces in between the like inner join returns the count as 2.

出于某种原因,在这个邮政编码/类似的邮政编码中,另一半与第一部分完全相同,并且在类似的内部联接之间没有空格,将计数返回为2。

any solution for this ?

任何解决方案?

see this sqlfiddle

看到这个sqlfiddle

1 个解决方案

#1

If you have two entries in table one with values LS1 1LS and LS11LS, your join will match both of these, giving you a count of 2.

如果表1中有两个条目,其值为LS1 1LS和LS11LS,则您的连接将匹配这两个条目,计数为2。