mysql - Which element is the most frecuent according to a second table

Question

I have two tables,

phenotipos

id
GTEX-1117F
GTEX-ZE9C
K-562

and

atributos

SAMPID
K-562-SM-26GMQ
K-562-SM-2AXTU
GTEX-1117F-0003-SM-58Q7G
GTEX-ZE9C-0006-SM-4WKG2
GTEX-ZE9C-0008-SM-4E3K6
GTEX-ZE9C-0011-R11a-SM-4WKGG

I need to know how many times each element from fenotipos It's in atributos I tried to left join, but It doesn't work because the id aren't identicall to de SAMPID, and because id doesn't have the same number of characters each time I can either trim SAMPID with (distinct left (SAMPID,x)

标签： mysqlsqlcountsubquerysql-like

Answer 1

您可以使用相关子查询和字符串函数来计算每个表型开始的属性数量：

select p.*,
    (select count(*) from atributos a where a.sampid like concat(p.id, '%')) as cnt
from phenotipos p