现在我有两张表,如下,
表1: 表2:
我要如何才能
从表1中把padno字段的值的前缀跟 表2中padgroup字段的值匹配的数据一次性全部查询出来,
想用 join + like 实现,但是一直实现不了啊
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
再来和join+like的版本
SELECT * FROM `a2` INNER JOIN `a1` ON a1.padno like concat(a2.padgroup, '%');
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楼的。哈哈