有这样两张表表A
CREATE TABLE A (
id number(10) NOT NULL,
product_id number(10) not null ,
company_id number(10) ,
name varchar2(50) not null,
keyword varchar2(50) ,--关键词
content_up varchar2(50) ,
sort_number number(4) ,
ad_type varchar2(50) ,
is_vitual number(1) not null,
description varchar2(255) ,
biz_state varchar2(20) ,
sys_deleted number(1) default 0 not null,
sys_ts_first number(38) ,
sys_ts_last number(38) ,
PRIMARY KEY (id)
) ;
表B
CREATE TABLE B(
id number(10) not null,
keyword varchar2(50) not null ,--关键字
synonyms varchar2(100) not null--对应同义词
) ;要求是当输入查询条件keyword时就到表A去找这个keyword,如果查询所输入的在表A中找
不到,就到表B去匹配它的同义词(如:输入‘咖啡’但在表B的synonyms中有‘咖啡厅’、‘咖啡连锁店’)
如果匹配到了,就把匹配到的表B的synonyms字段对应的keyword给查询出来再去匹配表A中的keyword字段判断是否有这个keyword,如果有查询出来!
CREATE TABLE A (
id number(10) NOT NULL,
product_id number(10) not null ,
company_id number(10) ,
name varchar2(50) not null,
keyword varchar2(50) ,--关键词
content_up varchar2(50) ,
sort_number number(4) ,
ad_type varchar2(50) ,
is_vitual number(1) not null,
description varchar2(255) ,
biz_state varchar2(20) ,
sys_deleted number(1) default 0 not null,
sys_ts_first number(38) ,
sys_ts_last number(38) ,
PRIMARY KEY (id)
) ;
表B
CREATE TABLE B(
id number(10) not null,
keyword varchar2(50) not null ,--关键字
synonyms varchar2(100) not null--对应同义词
) ;要求是当输入查询条件keyword时就到表A去找这个keyword,如果查询所输入的在表A中找
不到,就到表B去匹配它的同义词(如:输入‘咖啡’但在表B的synonyms中有‘咖啡厅’、‘咖啡连锁店’)
如果匹配到了,就把匹配到的表B的synonyms字段对应的keyword给查询出来再去匹配表A中的keyword字段判断是否有这个keyword,如果有查询出来!
SELECT *
FROM a
WHERE a.keyword IN
(CASE
WHEN v_keyword = a.keyword
THEN v_keyword
ELSE (SELECT keyword
FROM b
WHERE synonyms LIKE '%' || v_keyword || '%')
END
)
select * from a
where keyword in (case when exists(select 1 from a where keyword = '传入参数') then '传入参数'
else (select keyword from b where synoyms like '传入参数' || '%') end);
insert into B values(1,'帽子连锁','帽子')
insert into B values(1,'移动新活动','充话费送话费')
insert into B values(1,'裤子专卖店','裤子专卖')
insert into B values(1,'移动','移动运营')insert into A values(1,1,1,coffee,'咖啡厅','coffee',0,1,1,'coffee','coffee',1,null,null)
insert into A values(2,2,1,mobile,'移动','mobile',1,1,1,'mobile','mobile',1,null,null)
insert into A values(3,3,1,photo,'照相馆','photo',2,1,1,'photo','photo',1,null,null)
insert into A values(4,4,1,bag,'包','bag',3,1,1,'bag','bag',1,null,null)
insert into A values(5,5,1,kuzi,'裤子专卖店','kuzi',4,1,1,'kuzi','kuzi',1,null,null)
insert into A values(6,6,1,kuziliansuo,'裤子连锁店','kuziliansuo',5,1,1,'kuziliansuo','kuziliansuo',1,null,null)
假如我输入查询keyword的为裤子!
where keyword in (case when exists(select 1 from a where keyword = '裤子') then '裤子'
else (select keyword from b where synonyms like '裤子' || '%') end); ID PRODUCT_ID COMPANY_ID NAME KEYWORD CONTENT_MARKUP SORT_NUMBER AD_TYPE IS_VITUAL DESCRIPTION BIZ_STATE SYS_DELETED SYS_TS_FIRST SYS_TS_LAST
----------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- -------------------------------------------------- --------- -------------------------------------------------------------------------------- -------------------- ----------- --------------------------------------- ---------------------------------------
5 5 1 kuzi 裤子专卖店 kuzi 4 1 1 kuzi kuzi 1