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文になるのでしょうか？

回答No.6

yamada_g · ベストアンサー

すみません。
t2の抽出条件が抜けていましたね・・・

ただ、No.4の補足にある
>inner join t2 on (t2.key = (SELECT key FROM t2 WHERE name = 't2_name1'))
では正しくないです。
こうしてしまうと、t2とt5の結合条件がなくなってしまうので、
t2に存在しない、t5の'2011-03-03'のレコードまで抽出されてしまうことになります。
ですので結果が13300になってしまいます。

>inner join t2 on (t2.key = (SELECT key FROM t2 WHERE name = 't2_name1'))
こうではなくて
inner join t2 on (t2.key = t5.t2_key and t2.name = 't2_name1')
とすればいいです。
なので、

select sum(t5.amount * (t4.percent / 100.0)) result from
(
　(
　　select strftime('%Y', date) year, t2_key, t3_key, amount
　　from t5
　　where strftime('%Y', date) = '2011'
　) t5
　inner join t2 on (t2.key = t5.t2_key and t2.name = 't2_name1')
　inner join
　　(
　　　select t4.* from t4
　　　inner join t1 on (t4.t1_key = t1.key and t1.name = 't1_name1')
　　) t4
　on (t5.t3_key = t4.t3_key and t5.year = t4.year)
);

これでどうでしょうか。

参考URL：

http://ideone.com/86dQY

お礼

ご返答ありがとうございます。
なるほど、それでできました！
たしかにみなさんのSQL文でも
　　inner join t1 on (t4.t1_key = t1.key and t1.name = 't1_name1')
こういう繋げ方してますね。
参考になりました。
ありがとうございます。

回答No.5

o_chi_chi

t1とt2が逆でしたね。

これでどうでしょう？
---
select sum(a.amount * b.percent / 100)
from
(
(select strftime('%Y', t5.date) as year, t5.t2_key as keya, t5.amount
from t5
where strftime('%Y', t5.date) = '2011')
inner join t2
on t5.t2_key = t2.key and t2.name = 't2_name1'
) a
inner join
(
select t4.year, t4.t1_key as keyb, t4.percent
from t4
inner join t1
on t4.t1_key = t1.key and t1.name = 't1_name1'
) b
on a.keya = b.keyb and a.year = b.year

---
デバッグするときは

まず

select strftime('%Y', t5.date) as year, t5.t2_key as keya, t5.amount
from t5
where strftime('%Y', t5.date) = '2011')
inner join t2
on t5.t2_key = t2.key and t2.name = 't2_name1'

で思ったとおりのデータがとれているか。

つぎに

select t4.year, t4.t1_key as keyb, t4.percent
from t4
inner join t1
on t4.t1_key = t1.key and t1.name = 't1_name1'

つぎに

select a.* , b.*
from
(
(select strftime('%Y', t5.date) as year, t5.t2_key as keya, t5.amount
from t5
where strftime('%Y', t5.date) = '2011')
inner join t2
on t5.t2_key = t2.key and t2.name = 't2_name1'
) a
inner join
(
select t4.year, t4.t1_key as keyb, t4.percent
from t4
inner join t1
on t4.t1_key = t1.key and t1.name = 't1_name1'
) b
on a.keya = b.keyb and a.year = b.year

というふうに順番に確認してください。

お礼

ご返答ありがとうございます。
下のSQL文では、テーブル「t4」以外は
「アクティブではないテーブルにたいしてSELECT文を実行することはできません」
というエラーで、
テーブル「t4」は、
「フィールド："t5.t2_key"が見つかりません。」
というエラーが出てきてしまいます・・・
でもたしかにテーブル「t5」にはt2_keyカラムがあるのですが・・・
なんでこのようなエラーがでるのでしょうか？

select sum(a.amount * b.percent / 100)
from
(
(select strftime('%Y', t5.date) as year, t5.t2_key as keya, t5.amount
from t5
where strftime('%Y', t5.date) = '2011')
inner join t2
on t5.t2_key = t2.key and t2.name = 't2_name1'
) a
inner join
(
select t4.year, t4.t1_key as keyb, t4.percent
from t4
inner join t1
on t4.t1_key = t1.key and t1.name = 't1_name1'
) b
on a.keya = b.keyb and a.year = b.year

また次のSQL文ではどのテーブルでのSQL入力でも

「アクティブではないテーブルにたいしてSELECT文を実行することはできません」

となり実行できませんでした・・・

select strftime('%Y', t5.date) as year, t5.t2_key as keya, t5.amount
from t5
where strftime('%Y', t5.date) = '2011')
inner join t2
on t5.t2_key = t2.key and t2.name = 't2_name1'

回答No.4

yamada_g

No.2の補足にある、
>'2011-02-02'の方は、テーブル「t4」に
>yearカラム＝'2011'
>かつ
>t1_keyカラム＝(テーブル「t5」のt1_keyカラムのt1_key1)
>訂正後：t1_keyカラム＝(テーブル「t1」のt1_nameカラム＝’t1_name1’のt1_key1)
>かつ
>t3_keyカラム＝(テーブル「t5」のt3_keyカラムのt3_key1)
>を満たさないので、なにもしません（この行は無視します）
とありますが、記載のデータでは条件を満たしますよね。
t5の'2011-01-01'と'2011-02-02'のレコードのt3_keyは同じ値になっているのですから。

ですので、'2011-02-02'のレコードのt3_keyが't3_key2'だとしたら

select sum(t5.amount * (t4.percent / 100.0)) result from
(
　(
　　select strftime('%Y', date) year, t2_key, t3_key, amount
　　from t5
　　where strftime('%Y', date) = '2011'
　) t5
　inner join t2 on (t2.key = t5.t2_key)
　inner join
　(
　　　select t4.* from t4
　　　inner join t1 on (t4.t1_key = t1.key and t1.name = 't1_name1')
　　) t4
　on (t5.t3_key = t4.t3_key and t5.year = t4.year)
);

でどうでしょうか。
全角スペースでインデントしているので半角スペースに置き換えるなどしてください。

参考URL：

http://ideone.com/suVsJ

お礼

ご返答ありがとうございます。
＞とありますが、記載のデータでは条件を満たしますよね。
＞t5の'2011-01-01'と'2011-02-02'のレコードのt3_keyは同じ値になっているのですから。
たしかにそうですね・・・申し訳ありません訂正します。

なんとなく理想的な結果に近づきつつあるのですが、
ただ自分でも本当にあっているのか確認がしずらいので、
もう少し入れるデータを増やしてみました^^;

テーブル「t4」
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key1', 10);// テーブル「t5」の'2011-01-01'の行がpercent「10」を利用する
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key2', 100);// テーブル「t5」の'2011-02-02'と'2011-02-02'の２つの行がpercent「100」を利用する
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key2', 't3_key1', 30);
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);
テーブル「t5」
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-01-01', 't2_key1', 't3_key1', 3000);// '2011'と't2_key1'でとりあえず条件を満たす
/*INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-02-02', 't2_key1', 't3_key1', 2000);*/
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-02-02', 't2_key1', 't3_key2', 2000);// '2011'と't2_key1'でとりあえず条件を満たす
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-03-03', 't2_key2', 't3_key2', 1000);
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-04-04', 't2_key1', 't3_key2', 10000);// '2011'と't2_key1'でとりあえず条件を満たす
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2012-01-01', 't2_key1', 't3_key1', 5000);

このように入れてあるデータを変更すると、
テーブル「t5」からは
'2011-01-01'
'2011-02-02'
'2011-04-04'
の行が選ばれ、その行のamountをテーブル「t4」のpercentを利用して計算して合計を出したいです。
'2011-01-01'　3000×(10/100)=300
'2011-02-02'　2000×(100/100)=2000
'2011-04-04'　10000×(100/100)=10000

300＋2000＋10000＝12300
という結果を期待したいのですが、
yamada_gさんのこのSQL文

（
ただ
t1_keyカラムに't1_name1'でマッチした行のt1_keyを入れたように
t2_keyカラムも't2_name1'でマッチした行のt2_keyを入れたいので

＞ inner join t2 on (t2.key = t5.t2_key)

この部分を

inner join t2 on (t2.key = (SELECT key FROM t2 WHERE name = 't2_name1'))
に変更しました。これについても正しいのかアドバイス頂けないでしょうか？
）

だと、「13300」という結果になってしまいます・・・
これなぜなのでしょうか？
どのようなSQL文にすれば良いのでしょうか？

回答No.3

o_chi_chi

こういうことでしょうか？(動作確認なし）

----

select
sum(a.amount * b.percent / 100)

from

(select strftime('%Y', t5.date) as year, t5.t2_key as keya, t5.amount
from t5
inner join t1
on t5.t2_key = t1.key
and t1.name = 't1_name1'
where strftime('%Y', t5.date) = '2011') a

inner join

(select t4.year, t4.t1_key as keyb, t4.percent
from t4
inner join t2
on t4.t1_key = t2.key
and t2.name = 't2_name1') b

on a.keya = b.keyb
and a.year = b.year

お礼

ご返答ありがとうございます。
PupSQLiteというソフトを使って（使い方がいまいち分からないのですが^^;）
SQL入力を実行しているのですが、
o_chi_chiさんが提示してくださったSQLを実行すると
なぜかテーブル「t5」では

「アクティブではないテーブルにたいしてSELECT文を実行することはできません」

というエラーが出てしまいます・・・
テーブル「t4」では実行できたのですが、得られた結果は「null」でした。
そもそもなぜこの文でテーブル「t4」で実行できるのでしょうか？

www.kkaneko.com/rinkou/addb/pupsqlite.html

回答No.2

o_chi_chi

ちょっと修正

INNER JOIN 使えば両方にデータがあるものだけ

抽出できるので場合わけの必要がなくなる

----
SELECT
SUM(t5.AMOUNT * t4.percent / 100)
FROM t5
INNER JOIN t4
ON strftime('%Y', t5.date) = t4.year
AND t5.t1_key = t4.t1_key
AND t5.t3_key = t4.t3_key

お礼

ご返答ありがとうございます。
今試してみたのですが、5300と出てしまいます・・・
（自分が求めていた結果は'2011-01-01'分の300だけです）
これは何か違うのでしょうか？
ただ、申し訳ありません、
もう一つ質問にお答え頂けないでしょうか？
質問を立ててからうっかり気づいたのですが、自分が求めている結果が
このテーブル構成だと違うことに気づきました・・・
テーブルか「t5」のt1_keyカラムが必要ではなくて

CREATE TABLE t5 (
no INTEGER PRIMARY KEY AUTOINCREMENT,
date TEXT NOT NULL,
t2_key TEXT NOT NULL,
t3_key TEXT NOT NULL,
amount INTEGER NOT NULL,
FOREIGN KEY(t2_key) REFERENCES t2(key) ON DELETE CASCADE,
FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE
);
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-01-01', 't2_key1', 't3_key1', 3000);
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-02-02', 't2_key1', 't3_key1', 2000);
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2012-01-01', 't2_key1', 't3_key1', 5000);

でした・・・
この状態で、条件は

テーブル「t5」から、
dateカラム＝’2011’
t2_keyカラム＝ (テーブル「t2」のt2_nameカラム＝’t2_name1’のt2_key1)
の行のamount×(「t4」テーブルの)percentの合計なのですが、
テーブル「t4」は
t1_keyカラム＝ (テーブル「t1」のt1_nameカラム＝’t1_name1’のt1_key1)
かつ
yearカラム＝’2011’
かつ
t3_keyカラム＝ (テーブル「t5」のt3_keyカラムの値）

という条件のなのですが、この場合だと
先ほどと同様'2011-01-01'と'2011-02-02'の2つの行のamountの合計を求めることになりますが、
'2011-01-01'の方は、テーブル「t4」に以下の条件を満たすpercent「10」があるので　3000×(10/100)=300
'2011-02-02'の方は、テーブル「t4」に
yearカラム＝'2011'
かつ
t1_keyカラム＝(テーブル「t5」のt1_keyカラムのt1_key1)
かつ
t3_keyカラム＝(テーブル「t5」のt3_keyカラムのt3_key1)
を満たさないので、なにもしません（この行は無視します）
ですので'2011-01-01'分の
合計　300
という結果が得たいです。
これはどのようなSQL文になるのでしょうか？
何度も申し訳ないのですが、お答え頂けないでしょうか？

補足

すいません、訂正です・・・

「
かつ
t1_keyカラム＝(テーブル「t5」のt1_keyカラムのt1_key1)
かつ
」

これではなく

「
かつ
t1_keyカラム＝(テーブル「t1」のt1_nameカラム＝’t1_name1’のt1_key1)
かつ
」

でした・・・（テーブル「t5」にt1_keyカラムはなかったですね・・）

回答No.1

o_chi_chi

こんな感じで（動作確認なし）

----
SELECT
SUM(CASE WHEN t4.percent IS NULL THEN 0
ELSE t5.AMOUNT * t4.percent / 100 END)
FROM t5
LEFT OUTER JOIN t4
ON strftime('%Y', t5.date) = t4.year
AND t5.t1_key = t4.t1_key
AND t5.t3_key = t4.t3_key