匹配两个表中的记录 [英] Matching records from two tables
问题描述
我有两个表: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...
首先您要创建拆分功能和在表中插入这些值的过程(我在这里使用此处的答案
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 ;
这篇关于匹配两个表中的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!