dポイントプレゼントキャンペーン実施中!

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

A 回答 (6件)

すみません。


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
    • good
    • 0
この回答へのお礼

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

お礼日時:2012/10/03 19:57

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

というふうに順番に確認してください。
    • good
    • 0
この回答へのお礼

ご返答ありがとうございます。
下の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'

お礼日時:2012/10/03 16:48

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
    • good
    • 0
この回答へのお礼

ご返答ありがとうございます。
>とありますが、記載のデータでは条件を満たしますよね。
>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'の2つの行が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文にすれば良いのでしょうか?

お礼日時:2012/10/03 13:40

こういうことでしょうか?(動作確認なし)



----

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
    • good
    • 0
この回答へのお礼

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

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

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

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

お礼日時:2012/10/03 12:58

ちょっと修正



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

この回答への補足

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


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


これではなく


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


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

補足日時:2012/10/03 00:42
    • good
    • 0
この回答へのお礼

ご返答ありがとうございます。
今試してみたのですが、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文になるのでしょうか?
何度も申し訳ないのですが、お答え頂けないでしょうか?

お礼日時:2012/10/03 00:38

こんな感じで(動作確認なし)



----
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
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!