匹配两个表中的记录

本文介绍了匹配两个表中的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个表:ads_info和ads.

I have two Tables: ads_info and ads.

我想匹配两个表中的记录.

I want to match records from two tables.

广告的SQL模式:

| id | title | |----|-----------------------| | 1 | This Dog is very nice |

ads_info的SQL架构:

SQL Schema for ads_info:

| id | infotext | tag | |----|------------------------------|-----------| | 1 | Dogs can eat a lot of things | dog, pets |

我想检查ID为1的广告的标题中ads_info中是否有标签.我已经尝试过了:

I want to check if the title of the Ads with id 1 has tags in ads_info. I have tried this:

SELECT * FROM `ads` where id = '1' UNION SELECT * FROM `ads_info` where tag like '%ads.title%'

此处是SQL字段: LINK

推荐答案

您可以这样操作:演示我确信有更好的方法，甚至可以更好地执行此示例:)但这可能会有所帮助...

Well you can do it this way : DEMO I am sure there are better ways and even this example can be better executed :) But it will maybe help...

首先您要创建拆分功能和在表中插入这些值的过程(我在这里使用此处的答案 LINK 并更正了一些小错误):

First you create function for split and procedure for inserting those values in table(I have used here a answer from here LINK and corrected some small errors):

功能

CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');

过程

CREATE PROCEDURE ABC(in fullstr VARCHAR(255)) BEGIN DECLARE a int default 0; DECLARE str VARCHAR(255); simple_loop: LOOP SET a=a+1; SET str=SPLIT_STR(fullstr,",",a); IF str='' THEN LEAVE simple_loop; END IF; insert into my_temp_table values (str); END LOOP simple_loop; END;

我已经为此值创建了一个表:

I have created a table for this values:

create table my_temp_table (temp_columns varchar(100));

调用了该过程:

call ABC((select tag from ads_info));

然后您可以使用它:

Select * from ads B where exists (select * from my_temp_table where find_in_set(UPPER(trim(temp_columns)), replace(UPPER(B.TITLE), ' ', ',')) > 0 );

或者这个:

SELECT * FROM ads, my_temp_table WHERE find_in_set(UPPER(trim(temp_columns)), replace(UPPER(ads.TITLE), ' ', ',')) > 0 ;