【mysql】以下のSQL文が読み解けない

Question

※修正済(2016/07/22 17:48)

SELECT
    tb1.column1,
    MAX(tmp.column2) AS max_column2
FROM
    tb1_name tb1 JOIN
    (
        SELECT
            (COALESCE(column3, column4)) AS column2,
            column1
        FROM
            tb1_name
    ) tmp
ON  tmp.column1 = tb1.column1
GROUP BY
    tb1.column5

()の中身は一時テーブル？というものですか？
table_nameと一時テーブルをJOINしていると捉えて良いのでしょうか？
一時テーブルとはこのように作れるものなのですか？

Comments

[yambejp]

SELECT句に出現しないcolumn5でGROUP BYするのは適切ではありません。
GROUP BY column1ということでしょうか？

[kobahito]

>GROUP BY column1ということでしょうか？
いえ、テーブル：tb1_nameのカラムの一つとしてあるものです。
ここで初めて出現しました。

[yambejp]

具体例をだして検証してみるとわかると思いますが、前述のとおりSELECT句に出現しないカラムを利用してGROUP BYすることはおすすめしません。おそらく訳の分からないデータしか抽出できません

Answer 1

まぁ普通に考えればそんな非効率な書き方はしませんね

SELECT t1.column1 FROM table_name as t1
JOIN table_name as t2
ON  t1.column1 = t2.column1;

追記：サンプルデータ

CREATE TABLE table_name(column1 int,index(column1));
INSERT INTO table_name VALUES(1),(1),(2),(3),(3);

追記：新サンプル

とりあえずcolumn5はcolumn1に読み替えて回答します

CREATE TABLE table_name(column1 int,column3 int null,column4 int null);
INSERT INTO table_name VALUES(1,null,100),(1,200,null),(1,null,null),(2,100,300);

上記に対して

SELECT t1.column1,MAX(t2.column2) AS max_column2
FROM table_name AS t1
JOIN (SELECT COALESCE(column3, column4) AS column2,column1 FROM table_name) AS t2
ON  t1.column1 = t2.column1
GROUP BY column1;

を実行するなら、以下で十分です

SELECT column1,MAX(COALESCE(column3, column4)) AS max_column2
FROM table_name
GROUP BY column1

サブクエリで自分自身を結合するのでよく使うのは
特定のカラムの最大値を持つデータを表示するなど
サブクエリの中で集計が発生する場合です。

なお今回のCOALESCEの使い方ではcolumn3とcolumn4のどちらかにある最大値をとる
という仕様にはなっていません。なのでcolumn2_maxがもし上記条件を希望する場合は
正確にデータを取ることができません。
column3が合致した時点でcolumn4を検証しないのでcolumn3<column4の場合データが拾えない
もしどちらか最大値をとりたいならこんな感じ

SELECT column1,MAX(GREATEST(COALESCE(column3,0),COALESCE(column4,0))) AS max_column2
FROM table_name
GROUP BY column1;

Comments

[kobahito]

すいません。
先ほど載せたSQL文だと、元のSQLから削りすぎて、メリットがなくなってそうだったので、
元のSQLになるべく近い形で挙げ直しましたので、
このSQLの処理内容、メリットを今一度お教えいただけないでしょうか。。
段取りが悪くなってしまい申し訳ありませんが、よろしくお願いします。

[yambejp]

新サンプル追記しておきました

[kobahito]

なるほど。。。
とても詳しい回答をありがとうございます。
結果として下から2番目のSQLと同じになるんですね。
何をしているか感覚的にわかった気がします。
ありがとうございました！

Answer 2

おそらく、どちらのテーブルも'table_name'で同じなので混乱されているのではないでしょうか。

SQLはテーブルに別名をつけることができます。
このSQLだと'tmp'がそれになります。
同じテーブルに別名をつけることで、あたかも２つテーブルがあるようにみなすことが出来ます。

ちなみにこのSQLの結果は、

SELECT
    table.column1
FROM
    table_name table

の結果と同じになると思います。

Comments

[yambejp]

column1がユニークなカラムだとは限らないので提示の方法では
解を導けません

[kobahito]

回答ありがとうございます。
そういう事なんですね。
その書き方と同じになるとしたら、質問文の様に書くメリットか意味かはあるのでしょうか？
質問は解決しましたが、お教えいただけるとありがたいです。

[yambejp]

＞質問文の様に書くメリットか意味かはあるのでしょうか？

メリットは一切無いですね。
使い方がよくわからないままどうしてもサブクエリが使いたかったんじゃないですか？

[kobahito]

すいません。
先ほど載せたSQL文だと、元のSQLから削りすぎて、メリットがなくなってそうだったので、
元のSQLになるべく近い形で挙げ直しましたので、
このSQLの処理内容、メリットを今一度お教えいただけないでしょうか。。
段取りが悪くなってしまい申し訳ありませんが、よろしくお願いします。

Answer 3

MySQLの一時テーブルは「CREATE TEMPORARY TABLE～」とで作成するので、これはサブクエリだと
思います。

SQLとしては、table_nameとのJOINをしています。

Comments

[kobahito]

table_nameとサブクエリをJOINしているという事でしょうか？
このSQLは、FROM以下を下の様に書くのと同じになるのでしょうか？
FROM
column1 in (
SELECT
column1
FROM
table_name
)

[tomo.ina]

同じになるのは以下のように書いたときだと
思います。

SELECT
table.column1
FROM
table_name table
WHERE
table.column1 IN
(SELECT
column1
FROM
table_name
)

[kobahito]

すいまんせん。コメントに書いたSQLが変な感じになってました。
やはりそうですよね。ありがとうございます。

しかしそうなると質問文の様に書くメリットはあるのでしょうか？
質問は解決していますが、できればお教えいただければと思います。

[yambejp]

column1がユニークでないケースを想定してください

[kobahito]

すいません。
先ほど載せたSQL文だと、元のSQLから削りすぎて、メリットがなくなってそうだったので、
元のSQLになるべく近い形で挙げ直しましたので、
このSQLの処理内容、メリットを今一度お教えいただけないでしょうか。。
段取りが悪くなってしまい申し訳ありませんが、よろしくお願いします。