質問をすることでしか得られない、回答やアドバイスがある。

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

ただいまの
回答率

90.04%

MYSQLで複雑な条件で抽出する

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,679

SugiuraY

score 242

お世話になっております。

下記のようなtable_aがあり、以下のような条件でレコードを抽出したいと考えております。
① Company columnはAに絞る
② Yearが最大のもので絞る。
③ ただし、②のYear最大は少なくとも一つはvalueがNOT NULLが存在する場合の最大のyearとする

この条件の場合id4,id7の2つのカラムが抽出されると思います。
id4 -> company Aであり、Year2016でNOT NULLが存在するため,Yearは2016である。
id6-> company Aであり、Year2016でNOT NULLが存在するため,Yearは2016である。

例えばCompany Bのように2016のレコード自体が存在するが その場合のvalueがすべてNULL(NOT NULLのレコードが存在しない)であるため、Yearは2015が最大として,id8とid12のレコードだけに絞られる。

そこで以下のようなSQL文を考えたのですが、うまく意図した条件で絞ることができません。どなたか、上記の条件で絞ろうとする場合のSQL文をご助言願えますでしょうか。

よろしくお願い申し上げます。

table_a

id  company      name     Year    value
1   A                  asset1  2013   100
2   A                  asset1  2014   150
3   A                  asset1  2015   200
4   A                  asset1  2016   220
5   A                  asset2  2015   100
6   A                  asset2  2016    NULL 
7   A                  asset3  2016   60
8   B                  asset4  2015   100
9   B                  asset4  2016   NULL
10  B                  asset5  2016   NULL
11  B                  asset6  2014   30
12  B                  asset6  2015   25
SQL文

SELECT t1.* from table_a t1 JOIN (SELECT max(year) year 
from table_a GROUP BY company where value IS NOT NULL)
t2 using (year) where t1.company="a"
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • SugiuraY

    2016/11/01 21:20

    はい、仰る通りです。

    キャンセル

  • hatena19

    2016/11/01 21:27

    欲しいのは、A:「結果が1行」なのか B:「該当するレコード全体」なのか、A または B で答えてください。

    キャンセル

  • SugiuraY

    2016/11/01 21:33

    Bになります。

    キャンセル

回答 3

checkベストアンサー

0

記載されたSQLに対して、コメントを。

SELECT t1.* from table_a t1 JOIN (SELECT max(year) year 
from table_a GROUP BY company where value IS NOT NULL)
t2 using (year) where t1.company="a"



まず、文法が違います。GROUP BY の後にWHERE句は書けません。

SELECT t1.* from table_a t1 JOIN (SELECT max(year) year 
from table_a where value IS NOT NULL GROUP BY company)
t2 using (year) where t1.company="a"


次に、
t1(NULLを含まないtable_aのはずが、table_aのまま)と
t2(company毎のNULLを含まないMAX YEARのはずが、companyがないただの年月リスト)による
紐づけがないので、t1に条件を加え、t2にcompanyを付与し、紐づけします。
(ついでに見やすくします)

SELECT t1.* 
from table_a t1 
        INNER JOIN (    SELECT company
                            ,    max( year ) as year
                        from table_a
                        where value IS NOT NULL 
                        GROUP BY company
                        ) t2 ON(
                t2.company    = t1.company
            AND    t2.year        = t1.year
        )
where t1.company="a"
    and    t1.value IS NOT NULL


やりたかったのはこの形ですかね。
ちなみに、私なら、以下のSQLです。

SELECT t1.* 
from table_a t1
where    t1.company="a"
    and    t1.value IS NOT NULL
    and    t1.year    = (
                    SELECT    MAX( t2.year )
                    FROM    table_a t2
                    WHERE    t2.company    = t1.company
                        and    t2.value    IS NOT NULL
                    )

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/11/01 22:09

    ご回答ありがとうございます。
    t1.value IS NOT NULLについて、少し疑問なのです。
    サブクエリでt2.value IS NOT NULLにした上で、where t1.year=t2.yearとしており、どちらもテーブルが同じtable_aである以上、t1.value IS NOT NULLを条件してする意味に理解が及びませんでした。あくまで明示的に示すといったことでしょうか?

    キャンセル

  • 2016/11/01 22:17

    t1.value IS NOT NULL がないと、Yearが最大でvalueがNullのレコード(id=6)も抽出されちゃいますね。

    キャンセル

  • 2016/11/01 23:44

    今回の回答は、「valueにNULLがあるデータを抽出しない」場合のSQLになります。
    もちろん、抽出結果にNULLが含まれていても構わないのであれば、
    「t1.value IS NOT NULL」は不要になります。
    (他の回答者が含めていたので、私も流れに乗りました)

    なお、抽出結果にNULLを含めない事が前提で、少し疑問に思われている場合、
    恐らく同一テーブルを参照すれば、t2に条件を付与すると、
    t1にも勝手に紐づくものと思われているのかもしれませんが、
    異なるFROM句内のテーブルのデータに対して、勝手に紐づく事はありません。

    分かりやすく言うと、
    全く同じ形のビルが2つあったとしても、FROM句ごとに建っている場所が異なります。

    そのため、1つのビルに対して、ある階の電気を消したとしても、
    もう1つのビルの電気は付いたままになるんです。

    今回のケースでいうと、
    t1に対しては、(valueがNULLを含む)company=aのデータを抽出しており、
    t2については、(company単位でvalueがNULLを除く)最大yearになります。
    t1とt2を紐づける際、
    t1.year = t2.year
    だけでは、
    t1.valueがNOT NULLのデータもNULLのデータも抽出されることになるため、
    t1.value IS NOT NULL
    を追加した感じです。

    キャンセル

0

create table table_a(id int not null primary key,company varchar(20),name varchar(20),year int,value int null);
insert into table_a values
(1,'A','asset1', 2013,100),
(2,'A','asset1',2014,150),
(3,'A','asset1',2015,200),
(4,'A','asset1',2016,220),
(5,'A','asset2',2015,100),
(6,'A','asset2',2016,NULL),
(7,'A','asset3',2016,60),
(8,'B','asset4',2015,100),
(9,'B','asset4',2016,NULL),
(10,'B','asset5',2016,NULL),
(11,'B','asset6',2014,30),
(12,'B','asset6',2015,25);


だとして

SELECT t1.company,group_concat(id) as id from table_a as t1 
INNER JOIN (
SELECT company,max(year) as  year 
from table_a where value IS NOT NULL GROUP BY company 
) as t2
on t1.year=t2.year and t1.company=t2.company
group by company;

 追記

該当するレコードをすべてとりだすならこうしてください

SELECT t1.* from table_a as t1 
INNER JOIN (
SELECT company,max(year) as  year 
from table_a where value IS NOT NULL GROUP BY company 
) as t2
on t1.year=t2.year and t1.company=t2.company
WHERE t1.company='A';

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/11/01 22:02

    ご回答をいただき、ありがとうございます。また、言葉たらずの中、諸々汲み取っていただいてしまい、申し訳ございません。
    ご教示いただいた方法で実現できました。
    yambejp様のお力添え、いつも感謝をしております。
    よろしくお願い申し上げます。

    キャンセル

0

companyが"A" で、value が Null でないYearが最大のレコードを抽出

SELECT table_a.*
FROM table_a
WHERE 
company="A" AND
value is not null AND
year = (SELECT Max(Year)
FROM table_a
WHERE value Is Not Null AND company="A");

company毎に、value が Null でないYearが最大のレコードを抽出

SELECT a.*
FROM
table_a AS a INNER JOIN
(SELECT company, Max(Year) AS maxYear
 FROM table_a
 WHERE value Is Not Null
 GROUP BY company)  AS b 
 ON a.company = b.company AND a.year = b.maxYear
 WHERE a.value Is Not Null;

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/11/01 22:11

    ご回答いただきありがとうございます。
    質問をうまくお伝えできなかったにもかかわらず、粘り強く聞いてくださり、ありがとうございます。
    ご教示いただいた方法で、実現できました。もっとSQL文を勉強しないとなかなか実践で使用するレベルに至りませんね、、、精進いたします。よろしくお願い申し上げます。

    キャンセル

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

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