mysql 查询问题求助

Question

现在我有两张表，如下，
表1： 表2：

我要如何才能
从表1中把padno字段的值的前缀跟 表2中padgroup字段的值匹配的数据一次性全部查询出来，
想用 join + like 实现，但是一直实现不了啊

Answer 1

SELECT * FROM 
(
    SELECT 
        base2.id,
        base2.padno,
        base1.padgroup 
    FROM base2 
    LEFT JOIN base1 
    ON base2.padno LIKE CONCAT(base1.padgroup,"%") 
    ORDER BY padgroup DESC
） as temp 
GROUP BY temp.padno ;

用子查询将padno与padgroup对应起来，然后倒序排就可以得出group较为长的在上面，此时进行group by得出结果，如果还有需要就在group by 后面加入新的order by

Answer 2

DEMO:DEMO在线预览

SELECT * 
FROM   (SELECT t1.id, 
               padno, 
               padgroup 
        FROM   t1, 
               t2 
        WHERE  t1.padno REGEXP t2.padgroup 
        ORDER  BY padgroup DESC) temp 
GROUP  BY padno; 

借鉴了3楼的。哈哈

Answer 3

再来和join+like的版本

SELECT * FROM `a2` INNER JOIN `a1` ON a1.padno like concat(a2.padgroup, '%');