SQL文について
SQLite バージョン3.7.10 を使用しています。
次のようなテーブル構造で、
PRAGMA foreign_keys = ON;
CREATE TABLE t1 (
key TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL UNIQUE
);
INSERT INTO t1 (key, name) VALUES ('t1_key1', 't1_name1');
INSERT INTO t1 (key, name) VALUES ('t1_key2', 't1_name2');
CREATE TABLE t2 (
key TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL UNIQUE
);
INSERT INTO t2 (key, name) VALUES ('t2_key1', 't2_name1');
INSERT INTO t2 (key, name) VALUES ('t2_key2', 't2_name2');
CREATE TABLE t3 (
key TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL UNIQUE
);
INSERT INTO t3 (key, name) VALUES ('t3_key1', 't3_name1');
INSERT INTO t3 (key, name) VALUES ('t3_key2', 't3_name2');
CREATE TABLE t4 (
year TEXT NOT NULL,
t1_key TEXT NOT NULL,
t3_key TEXT NOT NULL,
percent INTEGER NOT NULL,
UNIQUE(year, t1_key, t3_key),
FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE,
FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE
);
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key1', 10);
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key2', 50);
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key1', 100);
CREATE TABLE t5 (
no INTEGER PRIMARY KEY AUTOINCREMENT,
date TEXT NOT NULL,
t1_key TEXT NOT NULL,
t2_key TEXT NOT NULL,
t3_key TEXT NOT NULL,
amount INTEGER NOT NULL,
FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE,
FOREIGN KEY(t2_key) REFERENCES t2(key) ON DELETE CASCADE,
FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE
);
INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2011-01-01', 't1_key1', 't2_key1', 't3_key1', 3000);
INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2011-02-02', 't1_key2', 't2_key1', 't3_key1', 2000);
INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2012-01-01', 't1_key2', 't2_key1', 't3_key1', 5000);
例えばテーブル「t5」から、
dateカラム＝’2011’
t1_keyカラム＝ (テーブル「t1」のt1_nameカラム＝’t1_name1’のt1_key1)
t2_keyカラム＝ (テーブル「t2」のt2_nameカラム＝’t2_name1’のt2_key1)
という条件の含まれてる全ての対応する行に、
テーブル「t4」のpercentカラムとテーブル「t5」のamountカラムを
掛け合わせた数値の合計を出力する。
ただしテーブル「t4」に対応するt1_keyとt3_keyがなければ、
percentを「0」としてamountと掛ける。
例えばこの条件だと
'2011-01-01'と'2011-02-02'の2つの行のamountの合計を求めることになりますが、
'2011-01-01'の方は、テーブル「t4」にpercent「10」があるので 3000×(10/100)=300
'2011-02-02'の方は、テーブル「t4」にpercentがない（対応する行がない）ので 2000×(0/100)=0
合計 300＋0=300
という結果が得たいです。
これはどのようなSQL文になるのでしょうか？
