在大查询中在2列之间使用LIKE

Question

我有两张桌子，即tableA和tableB。我从colA从tableA和colB从tableB。现在我想加入tableA和tableB on colA，colA就像colB。

例如，如果colB有一个值"abcxyzijk“，而colA有一个值"xyz”，那么它也应该加入。联接应该不限于= colB。

我尝试使用以下代码，但它只给出了colA与colB完全匹配的记录。

SELECT tableA.colA,  tableB.colB
FROM TableB tableB
INNER JOIN TableA tableA
ON tableB.colB LIKE tableA.colA

大查询中的两列之间是否有使用LIKE的方法？

Answer 1

您可以使用交叉连接，然后使用有和包含过滤数据。

WITH Roster AS
 (SELECT 'Adams' as LastName, 'asdf' as SchoolID UNION ALL
  SELECT 'Buchanan', 'qwe' UNION ALL
  SELECT 'Coolidge', 'zxc' UNION ALL
  SELECT 'Davis', 'eryvbnqc' UNION ALL
  SELECT 'Eisenhower', 'pyimkmhjrty')

, TeamMascot AS
 (SELECT 'asdf' as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 'qwe', 'Knights' UNION ALL
  SELECT 'zxc', 'Lakers' UNION ALL
  SELECT 'vbn', 'Mustangs' UNION ALL
  SELECT 'rty', 'Cavs')

SELECT ros.LastName,ros.SchoolID, mas.SchoolID,mas.Mascot FROM Roster as ros
CROSS JOIN TeamMascot as mas
GROUP BY 1,2,3,4
HAVING REGEXP_CONTAINS(ros.SchoolID, mas.SchoolID)

输出：

​

​