키워드별 매칭(테스트)

DB관계도

CREATE TABLE TEST_MEMBER(
    idx INT unsigned NOT NULL AUTO_INCREMENT COMMENT '고유번호',
    mName VARCHAR(20) COMMENT '이름',
    PRIMARY KEY (idx)
) COMMENT='멤버';
insert into TEST_MEMBER(mName) values('홍길동');
insert into TEST_MEMBER(mName) values('이순신');
insert into TEST_MEMBER(mName) values('신사임당');​

CREATE TABLE TEST_SHOP(
    idx INT unsigned NOT NULL AUTO_INCREMENT COMMENT '고유번호',
    sName VARCHAR(20) COMMENT '샵이름',
    sKeyword VARCHAR(100) NOT NULL COMMENT '키워드',
    PRIMARY KEY (idx)
) COMMENT='상점';
insert into TEST_SHOP(sName, sKeyword) values('A반점', '짜장면,냉면');
insert into TEST_SHOP(sName, sKeyword) values('B반점', '짜장면,짬뽕,콩국수');
insert into TEST_SHOP(sName, sKeyword) values('C분식', '라면,김밥');
insert into TEST_SHOP(sName, sKeyword) values('D분식', '냉면,돈까스');

CREATE TABLE TEST_ORDER(
    idx INT unsigned NOT NULL AUTO_INCREMENT COMMENT '고유번호',
    FK_shop INT unsigned NOT NULL COMMENT '주문매장',
    FK_mem INT unsigned NOT NULL COMMENT '주문자',
    FOREIGN KEY(FK_shop) REFERENCES TEST_SHOP(idx) ON DELETE CASCADE,
    FOREIGN KEY(FK_mem) REFERENCES TEST_MEMBER(idx) ON DELETE CASCADE,
    PRIMARY KEY (idx)
) COMMENT='주문';
insert into TEST_ORDER(FK_shop, FK_mem) values(3, 2);
insert into TEST_ORDER(FK_shop, FK_mem) values(2, 2);
insert into TEST_ORDER(FK_shop, FK_mem) values(1, 1);
insert into TEST_ORDER(FK_shop, FK_mem) values(4, 1);

select 
memidx,
sum(case when FIND_IN_SET('냉면', sKeyword) > 0 then 1 else 0 end) as '냉면',
sum(case when FIND_IN_SET('짜장면', sKeyword) > 0 then 2 else 0 end) as '짜장면',
sum(case when FIND_IN_SET('짬뽕', sKeyword) > 0 then 3 else 0 end) as '짬뽕',
sum(case when FIND_IN_SET('돈까스', sKeyword) > 0 then 4 else 0 end) as '돈까스',
sum(case when FIND_IN_SET('라면', sKeyword) > 0 then 100 else 0 end) as '라면',
sum(case when FIND_IN_SET('냉면', sKeyword) > 0 then 1 else 0 end) 
+ sum(case when FIND_IN_SET('짜장면', sKeyword) > 0 then 2 else 0 end) 
+ sum(case when FIND_IN_SET('짬뽕', sKeyword) > 0 then 3 else 0 end) 
+ sum(case when FIND_IN_SET('돈까스', sKeyword) > 0 then 4 else 0 end) 
+ sum(case when FIND_IN_SET('라면', sKeyword) > 0 then 100 else 0 end) 
as tot
from(
select sKeyword, mem.idx as memidx
from TEST_ORDER as ords 
left join TEST_SHOP as shop on ords.FK_shop = shop.idx
left join TEST_MEMBER as mem on ords.FK_mem = mem.idx
) as tbl
group by memidx
order by tot DESC, `라면` DESC, `냉면` DESC, `짜장면` DESC