如何统计phenotipos表ID在atributos表中的出现频次及最高频次元素？

我明白你的困扰——两张表的字段没法直接匹配，而且id长度还不一样，固定截取长度确实不靠谱。下面针对你的两个需求给出具体的SQL解决方案：

需求1：统计phenotipos表中每个id在atributos表中的出现次数 #

我们可以通过前缀匹配的方式关联两张表，不用纠结固定截取长度。因为你的SAMPID都是以phenotipos.id开头，后面紧跟着连字符，所以用LIKE结合字符串拼接来匹配更准确：

SELECT 
    p.id,
    COUNT(a.SAMPID) AS occurrence_count
FROM phenotipos p
LEFT JOIN atributos a ON a.SAMPID LIKE CONCAT(p.id, '-%')
GROUP BY p.id
ORDER BY occurrence_count DESC;

说明： #

• CONCAT(p.id, '-%')确保只匹配以目标id开头且紧跟连字符的SAMPID，避免出现类似短id匹配长前缀的误判
• LEFT JOIN保证即使某个id在atributos中没有匹配项，也会显示为0次
• GROUP BY p.id按phenotipos的id分组统计次数

需求2：找出atributos表中出现频次最高的样本前缀（对应phenotipos的id） #

这里我们先从SAMPID中提取出和phenotipos.id对应的前缀，再统计频次取最高值：

SELECT 
    LEFT(a.SAMPID, LOCATE('-', a.SAMPID) - 1) AS sample_prefix,
    COUNT(*) AS occurrence_count
FROM atributos a
GROUP BY sample_prefix
ORDER BY occurrence_count DESC
LIMIT 1;

说明： #

• LOCATE('-', a.SAMPID)找到SAMPID中第一个连字符的位置，LEFT(..., 位置-1)就能截取到前面的样本前缀
• GROUP BY sample_prefix按前缀分组统计次数，ORDER BY ... DESC LIMIT 1直接取频次最高的那一项

如果你的需求是找出整个SAMPID字段中出现次数最多的元素，那可以用更简单的写法：

SELECT 
    SAMPID,
    COUNT(*) AS occurrence_count
FROM atributos
GROUP BY SAMPID
ORDER BY occurrence_count DESC
LIMIT 1;

内容的提问来源于stack exchange，提问作者Jose Gracia Rodriguez