同じ条件を持つ場合の表を作るときの考え方

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 720

10morokoshi

score 12

連日の質問失礼いたします。知恵をお貸しいただけると幸いです。
Visual Studio2015で開発しております。

まず、データベース(phpMyAdmin使用)には以下のテーブルがあります。
イメージ説明
そして、下図のように表の出力をしたいです。
イメージ説明

こういった結果を得ることはできるのですが、(SQLでINNERJOIN結合したものを取得して、クラスにためたあと、ディクショナリーに加える)↓
イメージ説明
身長を横に並べるのにはどうしたらいいかわかりません。
先日投稿した質問にいただいた回答を参考にpivotなどを調べ、試してみましたが、思うようにいかなかったため、SQLで解決しようとするのが違うのかなという結論に至りました。
最終的に帳票として出力します。最後の図のようなものであれば出力することはできています。

これを解決するには、どのようなコード、考え方をすればよいのでしょうか…。パソコン、プログラミングなどの知識が全くない状態から始めて3カ月ほどなのですが、未だに考え方に柔軟な対応ができず…。

ご回答お願いいたします。

--追記です

詳細を書かせていただきます。
取得したいのは去年と今年だけで、データベースに保存されている年度は2016/01/01というような日付は1月1日固定で、年だけが違う日付型で登録されています。
motuo様が書いてくださったSQLの max(CASE WHEN tableb.year = 2016 then tableb.sintyo END)などの年度を指定しているところにはその日付を予めプログラムで取得しており、それを入れるようにしたので、そこは変数に変更しています。Addyear(-1)で去年分も出しています。
もっと言うと、検索条件は出席番号A1番からB31番まで、ですとか、欲しいテーブルは去年の身長と今年の身長、今年の委員会など、去年のはいらないけれど、今年のは欲しいという値も存在するのですが、それはいただいた回答を応用することで解決するかなと思ったので書きませんでした。
このプログラムで更新ですとかそういったことをすることはなくて、ただそのときに欲しい範囲の表を出力するだけです。
稚拙な文章ですみません。追記の指摘やご回答いただけると幸いです。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • mattn

    2017/07/05 17:09

    10morokoshi さん、そろそろお気付きかもしれませんが、この年毎に増えるテーブルを横列にするという事は、来年新しく身長を登録するとこの SQL も改変する必要があります。例えば去年と今年の分だけを表にしたいといった場合はプログラムを書いたり、バッチ処理という手続きが必要になります。かたやプログラムで作る場合ですと、SQL を組み立てる際に去年(2016)と今年(2017)という数字を得て文字列結合するといった事で毎年 SQL を改変しなくても良くなります。 やろうとされておられる内容や、要望(毎年更新したくない)等により回答内容が異なりますので、もう少し詳細を説明下さい。

    キャンセル

  • 10morokoshi

    2017/07/05 17:36

    ありがとうございます。追記いたしました。

    キャンセル

回答 4

checkベストアンサー

+1

すいません。前回の質問とほぼ同じ内容に見えるのですが…
まず、下記のSQLで欲しい表を取得する事が出来ると思います。

Select
 shussekiNo,
 name,
 max(CASE WHEN tableb.year = 2016 then tableb.sintyo END) as sintyo2016,
 max(case when tableb.year = 2017 then tableb.sintyo END) as sintyo2017
From
 tablea
Left Outer Join tableb
 on tablea.ID = tableb.id
Group by
 tablea.shussekiNo


結果
イメージ説明

前回の質問でわからなかった部分を、もう少し具体的に書くと、違った観点からアドバイスできるかも
しれません。

去年と今年を実行日で判定するSQL

Select
 shussekiNo,
 name,
 max(CASE WHEN tableb.year =  YEAR(CURDATE()) - 1 then tableb.sintyo END) as lastYear,
 max(case when tableb.year =  YEAR(CURDATE())  then tableb.sintyo END) as currentYear
From
 tablea
Left Outer Join tableb
 on tablea.ID = tableb.id
Group by
 tablea.shussekiNo

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/05 17:50

    ご丁寧にありがとうございます。年度に関しては、仕様上決まっているためこれを使用しております。今後使う知識として、参考にさせていただきます!

    キャンセル

  • 2017/07/05 17:59

    知りたいことに対する回答は上記で十分でしょうか?(この件ではもう詰まっていませんか?)
    十分であれば良いのですが、追記を見ても最終的に何を知りたいか私には読み切れず…

    キャンセル

  • 2017/07/05 18:02

    はい、おかげ様でだいたい解決したように思います。しかし、本格的に試すのが明日になってしまうため、mattan様の回答も待ちつつ、解決済にするのは後日にしようと思います。

    キャンセル

+1

前回の回答のとおりだと思いますが、どこがつまっているのでしょうか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/05 17:52

    私の理解力の無さが問題でした、申し訳ございません。おおよそは解決したと思います。しかし、他の問題にぶつかりそうですので、他の方の回答を参考にしたうえでさらに追記させていただきました。

    キャンセル

+1

それぞれJOINしてはどうでしょうか?

SELECT A.出席番号, A.名前, B2016.身長 身長2016, B2017.身長 身長2017
FROM A
INNER JOIN B B2016 ON B2016.ID = A.ID AND B2016.年 = 2016
INNER JOIN B B2017 ON B2017.ID = A.ID AND B2017.年 = 2017

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/05 17:38

    ありがとうございます。参考にさせていただきます。

    キャンセル

+1

考え方だけ。
やり方はたくさんあります。が基本的に
1.「こんなテーブルが存在したら簡単にできる」とういう状態を考える
2.サブクエリと集計関数でほしい"こんなテーブル"の形を作る
3.2て作った形を1に代入する。
と考えると考えやすい。
部品をつくって組み上げていくという意味では普通の手続き型プログラムと考え方は同じです。
通常のプログラムがモジュールを作って組み上げていくのにたいし、SQLはサブクエリ等"部分集合"をつくって組み上げる。
方法その1:
テーブルBが20016年と20017年で別だったら?と考える
テーブルBがテーブルB_2016、テーブルB_2017という2テーブルだったら簡単。ただの結合です。

//テーブルB_2016、テーブルB_2017があれば動くSQL
SELECT a.出席番号, a.名前, b1.身長 AS 身長2016, b2.身長 AS 身長2017
FROM テーブルA a
LEFT JOIN テーブルB_2016 b1 ON b1.ID = a.ID
LEFT JOIN テーブルB_2017 b2 ON b2.ID = a.ID


ここまで作ってテーブルB_2016、テーブルB_2017をサブクエリで作れば良い。

//テーブルB_2016と同じ
SELECT ID,身長 FROM テーブルB WHERE 年=2016
//テーブルB_2017と同じ
SELECT ID,身長 FROM テーブルB WHERE 年=2017


これを置き換えればできあがり

//完成
SELECT a.出席番号, a.名前, b1.身長 AS 身長2016, b2.身長 AS 身長2017
FROM テーブルA a
LEFT JOIN (SELECT ID,身長 FROM テーブルB WHERE 年=2016) b1 ON b1.ID = a.ID
LEFT JOIN (SELECT ID,身長 FROM テーブルB WHERE 年=2017) b2 ON b2.ID = a.ID


方法その2:
こんなテーブルがあると楽。
テーブルX

ID 身長2016 身長2017
1 150 151
2 142 145

これなら

//テーブルXがあれば動く
SELECT a.出席番号, a.名前, x.身長2016, x.身長2017
FROM テーブルA a
LEFT JOIN テーブルX x ON x.ID = a.ID


でできる。テーブルXはどうすればできるかかんがえると、こんなテーブルがあれば良い。
テーブルY

ID 身長2016 身長2017
1 2016 150 0
1 2017 0 151
2 2016 142 0
2 2017 0 145

テーブルXはテーブルYがあれば

//テーブルYからテーブルXをつくる
SELECT ID,SUM(身長2016) AS 身長2016,SUM(身長2017) AS 身長2017 FROM テーブルY GROUP BY ID
または
SELECT ID,MAX(身長2016) AS 身長2016,MAX(身長2017) AS 身長2017 FROM テーブルY GROUP BY ID


でできる。で、テーブルYはテーブルBをつかって

//テーブルBからテーブルYをつくる
SELECT 
ID,
CASE WHEN 年=2016 THEN 身長 ELSE 0 END AS 身長2016,
CASE WHEN 年=2017 THEN 身長 ELSE 0 END AS 身長2017
FROM テーブルB


でできる。これを最初のSQLに代入すれば

//完成(改良前)
SELECT a.出席番号, a.名前, x.身長2016, x.身長2017
FROM テーブルA a
LEFT JOIN 
(SELECT ID,
MAX(身長2016) AS 身長2016,
MAX(身長2017) AS 身長2017 FROM
(SELECT 
ID,
CASE WHEN 年=2016 THEN 身長 ELSE 0 END AS 身長2016,
CASE WHEN 年=2017 THEN 身長 ELSE 0 END AS 身長2017
FROM テーブルB) y GROUP BY ID) x ON x.ID = a.ID


これでも動くが、X,Yはまとめられるので

//完成
SELECT a.出席番号, a.名前, x.身長2016, x.身長2017
FROM テーブルA a
LEFT JOIN 
(SELECT ID,
MAX(CASE WHEN 年=2016 THEN 身長 ELSE 0 END) AS 身長2016,
MAX(CASE WHEN 年=2017 THEN 身長 ELSE 0 END) AS 身長2017 
FROM テーブルB GROUP BY ID) x ON x.ID = a.ID


性能とか考えるとまた別の話も必要ですが、とりあえずわりと汎用性の高い考え方の一つ。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/07/06 16:30

    ありがとうございます、考え方という点でとても助かりました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.21%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる