ビューを使わずに同一テーブルから異なる基準で集計した2つの結果を結合するクエリー

Question

いつもありがとうございます。

いま、A,B,Cの3名のプレーヤーが20面ダイスを振り合い、勝ったほうが振り続け、負けた者は交代するというもので、最初はA,Bが対戦するものとします。

そして各プレーヤーの先攻時と後攻時のスコアを集計します。

具体的には下記のようにDBを作りました。viewを使って先攻、後攻時のスコア集計テーブルを作って両者を結合するという流れになっています。

これをviewを使わずに1文で実現するqueryを考えているのですがなにかよい案があればご教示ください。inner joinを使うものと思うのですが、いろいろ試したのですがよく使い方が分かりませんでした。特に1つのqueryで複数のgroup byを使いつつ、結合をどうすればいいか、頭が混乱しています。

よろしくお願い致します。

// p1:先攻プレーヤー、s1：先攻スコア、p2：後攻プレーヤー、s2：後攻スコア
sqlite> create table dice (p1 text, s1 integer, p2 text, s2 integer);

// サンプルデータ投入
sqlite> insert into dice values ("A", 12, "B", 5);
sqlite> insert into dice values ("A", 15, "C", 9);
sqlite> insert into dice values ("A", 8, "B", 18);
sqlite> insert into dice values ("B", 20, "C", 15);
sqlite> insert into dice values ("B", 5, "A", 8);
sqlite> insert into dice values ("A", 9, "C", 11);
sqlite> insert into dice values ("C", 15, "B", 12);
sqlite> insert into dice values ("C", 15, "A", 9);
sqlite> insert into dice values ("C", 3, "B", 17);
sqlite> insert into dice values ("B", 12, "A", 7);

// 各プレーヤーの先攻時、後攻時のスコアを集計するview
sqlite> create view sum1 as select p1, sum(s1) as t1 from dice group by p1;
sqlite> create view sum2 as select p2, sum(s2) as t2 from dice group by p2;

// 結合
sqlite> select p1, t1, t2 from sum1 left outer join sum2 on p1=p2;
A|44|24
B|37|52
C|33|35

sqlite3 --version 3.6.20

Answer 1

select p1, t1, t2 from sum1 left outer join sum2 on p1=p2

のsum1,sum2を、それぞれのcreate view文のas以降を括弧で括り、置き換えてみてください。

Comments

[true]

ありがとうございます！組み立ての流れが分かりやすく、役に立ちました。

Answer 2

インラインビューを使う事で単純に一文で表現できます。

select sum1.p1, sum1.t1, sum2.t2 
from (select p1, sum(s1) as t1 from dice group by p1) sum1 
left outer join (select p2, sum(s2) as t2 from dice group by p2) sum2 
on sum1.p1=sum2.p2

Comments

[true]

ありがとうございます！インラインビューは知りませんでした。しかもFROM句に入れるとは想像も付きませんでした。勉強になりました。