如何与其他表一样进行匹配和计数 [英] how to match with like in other table and count

问题描述

我有2张桌子 areas2013

i have 2 tables areas2013

row_id | area              |teamleader
1      |  1234-Asia        |   Joe 
2      |  12345-Europe     |   Juan  
3      | 123456-UK         |   Ple 

和 f12

row_id| eacode
1     | 1234
2     | 12345
3     | 1234

如您所见，我想使用％来匹配eacode和区域并计算多少eacode.

as you can see i want to match the eacode and area using like% and count how many eacode.

我想要在php中这样的东西

i want something like this in php

eacode| area | count | teamleader 
1234  | Asia  |   2    |  Joe
12345 | Europe|   1    |  Juan

对不起，我的英语不好

推荐答案

Barmar的回答很好，我只是对其进行了一些微调，以获取您要求的确切输出:

Barmar's answer is good I just tweaked it a bit to get the exact output you requested:

SELECT eacode, SUBSTRING_INDEX(SUBSTRING_INDEX(area, '-', 2), '-', -1) as area, count(*) `count`, teamleader
FROM f12
JOIN areas2013 ON area like CONCAT(eacode, '-%')
GROUP BY eacode

这篇关于如何与其他表一样进行匹配和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！