Re: 分類付き m_byochu

このトピックの投稿一覧へ

なし Re: 分類付き m_byochu

msg# 1.3.2.1.1.2
depth:
5
前の投稿 - 次の投稿 | 親投稿 - 子投稿.1 | 投稿日時 2010.11.03 22:45 | 最終変更
OhYeah!  管理人   投稿数: 983 オンライン
修正版です。'%活性化%' とすべきところが '活性化%' となっていたのも直しました。

--/d
drop table if exists t_byochu;
create temp table t_byochu as select distinct byochu from m_tekiyo order by byochu;
drop table if exists m_byochu;
create table m_byochu (idbyochu integer primary key autoincrement, byochu varchar unique, cid integer);
begin transaction;
insert into m_byochu (byochu, cid) select byochu, 5 from t_byochu where ifnullstr(byochu, '-') != '-';
--分類処理
update m_byochu set cid = 4 where byochu in (select distinct byochu from m_tekiyo where mokuteki = '忌避' or bango in (select bango from m_kihon where koka like '%忌避%'));
update m_byochu set cid = 2 where byochu in (select distinct byochu from m_tekiyo where mokuteki in ('誘引', '交尾阻害') or bango in (select bango from m_kihon where yoto like '殺虫%' or koka like '%誘引' or koka like '%殺虫%'));
update m_byochu set cid = 3 where byochu like '%雑草%' or byochu in (select distinct byochu from m_tekiyo where bango in (select bango from m_kihon where yoto = '除草剤'));
update m_byochu set cid = 1 where byochu like '%病%' or byochu like'%症%' or byochu like '%菌%' or byochu like '%腐%' or byochu like '%タケ';
update m_byochu set cid = 5 where byochu like '%活性化%' or byochu like '%覚醒%';
--分類例外処理
update m_byochu set cid = 2 where byochu like '%ナメクジ' or byochu like '%ハダニ%';
update m_byochu set cid = 4 where byochu in ('イノシシ', 'カモシカ', 'ニホンジカ', 'モグラ', '野ウサギ', '野ソ', '害獣');
commit;
drop table if exists t_byochu;
投票数:2 平均点:5.00

投稿ツリー

  条件検索へ