MySQLのcount取得時、グループごとに日付降順でカウンタ値と外部結合のデータを取得する方法

Question

MySQL におけるデータ取得で悩んでいる箇所があります。

簡略化すると、次のような構成のテーブルがあります。

●テーブルＡ（データ本体）
|DATA_ID|CAT_ID|START_TIME|
|1|1|2016-04-01 14:00:00|
|2|1|2016-04-01 13:25:00|
|3|1|2016-04-01 15:10:00|
|4|2|2016-04-01 15:00:00|

●テーブルＢ（カテゴリマスタ）
|CAT_ID|CAT_SUB_ID|NAME|
|1|test1|テスト１|
|2|test2|テスト２|

●得たい結果
|COUNTER|DATA_ID|START_TIME|CAT_ID|CAT_SUB_ID|
|1|3|2016-04-01 15:10:00|1|test1|
|1|4|2016-04-01 15:00:00|2|test2|
|2|1|2016-04-01 14:00:00|1|test1|
|3|2|2016-04-01 13:25:00|1|test1|

START_TIME列でDESCした上で、CAT_IDでグループ化された結果に対して逆順でカウンタ値を生成し、テーブルＢのデータを取得する、というのが行いことになります。

●試した結果
SELECT (SELECT COUNT()+1 FROM tblA as T2 LEFT JOIN tblB on T2.CAT_ID = tblB.CAT_ID WHERE T2.START_DATE < T1.START_DATE and T2.CAT_ID = T1.CAT_ID and T2.START_DATE < "実行時刻" ORDER BY T2.START_DATE DESC ) AS counter , T1. FROM tblA AS T1 where T1.VIEW = 'TRUE' and T1.START_DATE < "実行時刻" ORDER BY T1.START_DATE DESC

●得られた結果
|COUNTER|DATA_ID|START_TIME|CAT_ID|CAT_SUB_ID|
|3|3|2016-04-01 15:10:00|--|--|
|1|4|2016-04-01 15:00:00|--|--|
|2|1|2016-04-01 14:00:00|--|--|
|1|2|2016-04-01 13:25:00|--|--|

●質問内容
こちら、上記の件を解決させたいのが第一なのですが、今後の為に次の点も教えていただけると助かります。

１）MySQLにおける、countの実行タイミング（上記の結果を見ると、WHERE句の実行時という認識で大丈夫でしょうか？）

２）サブクエリ実行時は、外部結合が使用できない？ それとも上記の記述がまずい？

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

Answer 1

こちら、降順での連番付与、自己解決いたしました。完全にハマっていたところ、hirohiroさんのヒントにより解決いたしました、本当にありがとうございます。

SELECT
  (
    SELECT COUNT(T2.START_TIME)+1 FROM tblA as T2 
    WHERE T1.START_TIME < T2.START_TIME
    AND T1.CAT_ID = T2.CAT_ID 
  ) AS counter 
  , T1.* ,T3.CAT_SUB_ID
FROM tblA AS T1 
LEFT JOIN tblB T3 ON T2.CAT_ID = T3.CAT_ID 
ORDER BY T1.START_TIME DESC

カウント取得用のサブクエリで、

１）全件取得ではなく、降順取得したい列名を指定
２）サブクエリ中のWHERE 句照合対象を逆に（T1.T2ひっくり返した）
３）本クエリでSTART_TIMEをDESC

hirohiroさん並びに、閲覧していただいた方々、ありがとうございます。

Answer 2

SELECT
  (
    SELECT COUNT(*)+1 FROM tblA as T2 
    WHERE T1.START_TIME < T2.START_TIME
    AND T2.CAT_ID = T1.CAT_ID 
  ) AS counter 
  , T1.* ,T3.CAT_SUB_ID
FROM tblA AS T1 
LEFT JOIN tblB T3 ON T2.CAT_ID = T3.CAT_ID 
ORDER BY T1.START_TIME DESC

動作確認はしていませんがこんな感じでしょうか？
やってみたSQLにはサンプルテーブルに無い要素がありましたが、サンプルテーブルデータのほうに合わせたコードにしています。

---

１）MySQLにおける、countの実行タイミング

そのクエリ内のほかの句を実行した後です。

２）サブクエリ実行時は、外部結合が使用できない？

できます。やってみたSQLでは、最初のサブクエリでcount(*)+1しか出力していないので、何を結合しても出力されるのはカウントだけだったということになります。
※そもそもselect句内のサブクエリで複数項目出力はできなかったように思います。(自信無い)

Comments

[kohtan1116]

早速のご回答ありがとうございます。また、例示のＳＱＬに誤りがあり、読み替えしていただいてありがとうございます。

（２）
こちら、例示いただいたSQL文を見てはっとしましたが、あまりにもハマりすぎて、完全に基本的な部分が抜け落ちておりました…。

あまりにも基本的でお恥ずかしいほど…。

いただいた回答をもとに打ってみたところ、tblB側からのデータは当然ですが成功いたしました。ありがとうございます。

カウント値がやはり、START_TIMEの古い方から付番されており、後はこの点を解決できれば…というところです。

[hirohiro]

>　カウント値がやはり、START_TIMEの古い方から付番されており
ああ、サブクエリのWhere句の条件が逆でしたね。
修正しておきました。
WHERE T2.START_TIME < T1.START_TIME →　WHERE T1.START_TIME < T2.START_TIME