こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

SQL構文を手助けしてください

(1)のようなテーブルデータを(2)のように表示したいと思います。


AA,BB,CC,DD,EEは列名としてください。

(1)
AA BB CC DD EE
__________________________
00 01 2004/01/01 XX YY
01 00 2004/01/01 XE YD
01 00 2004/01/05 XZ YZ
02 01 2004/01/10 X2 YE
02 02 2004/01/05 XW YI
02 02 2004/01/06 XF YL

(2)
AA BB CC DD EE
__________________________
00 01 2004/01/01 XX YY
01 00 2004/01/05 XZ YZ
02 01 2004/01/10 X2 YE
02 02 2004/01/06 XF YL

<条件>
AAとBBでキーを作り、CCのMAXの該当レコードを表示したい。
SELECT AA,BB,MAX(CC),DD,EE
FROM TEST_TABLE
GROUP BY AA,BB

というような感じかなと思ったのですが、「GROUP BY の式ではありません」と怒られてしまいます。
どなたか簡単なやり方をご存知で無いでしょうか?

投稿日時 - 2004-05-17 18:03:54

QNo.861744

すぐに回答ほしいです

質問者が選んだベストアンサー

 そのSELECT文、根本的に、条件に無理があります。条件が足りないというか・・・

 たとえば、元テーブルの、AA,BB = 01,00 の所を例に取りましょう。該当するレコードは、2件あります。CCの最大値は、明確に定義されていますから、簡単です。2004/01/05です。
 ところで、このとき、DD,EEは、いったい何になるんでしょうか?(2)のテーブルでは、XZ,YZとなっていますが、これはなぜですか?なぜ、XE,YDではないのですか?と、まぁこういう事をデータベースは問いかけています。
 そう。DD,EEに対する条件が足りないんです。「GROUP BYの式ではありません。」と言うのは、そういう意味です。
 基本的に、GROUP BYを指定した場合は、SELECTの列リストには、GROUP BYに列挙された式と集合関数しか書けません。文法的に言うとこうなります。

 CCがMAXの列のDDとEEとなると、少々面倒です。
 ここで、新しい条件として、AA,BB,CCの組みは、一意のキーを構成するという条件を付けます。でないと、一つのAA,BB,CCに対して、複数の列が該当すると言うことになり、さっきの話がぶり返しますので。
 わかりやすい方法としては、VIEWを作っちゃうことでしょうか。

CREATE VIEW TEST_TABLE_M (AA,BB,CCM)
AS SELECT AA,BB,MAX(CC) FROM TAST_TABLE
GROUP BY AA,BB

として、VIEWを定義し、このテーブルと結合します。

CREATE AA,BB,CCM,DD,EE
FROM TEST_TABLE A, TEST_TABLE_M M
WHERE A.AA=M.AA AND A.BB=M.BB AND A.CC=M.CCM

といったところでしょうか。
 VIEWを作りたくないのであれば、サブクエリーで条件を書くことになりますが、なんだか、条件が結構、長くなりそうです。「簡単な」とはいいがたくなります。

投稿日時 - 2004-05-17 19:34:04

お礼

回答ありがとうございます。
例題のSQLの矛盾は投稿した後に気付きました。
うん、DD,EE列になにを表示していいかわからないですもんね、、、

VIEWを使えばわかりやすくなりますね。
まぁ結果的に言えばそれほど簡単ではないですね(^^;)
どうもありがとうございました

投稿日時 - 2004-05-18 09:18:53

このQ&Aは役に立ちましたか?

11人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

下記SQL文は一応動作確認しました。そのまま、エラー無しで使用できると思います。
但し、テーブルの列定義は

create table TEST_TABLE ( AA char(2), BB char(2), CC date, DD char(2), EE char(2) );

と仮定しています。列の型が違う場合、文字列処理部分を修正して下さい。

サブクエリーを使用しないシンプルなSQL文を紹介します。ですので、
レスポンスは早いと思います。
(但し、列DD, EEの列長があまりにも長い場合は、レスポンスが遅くなるかも
しれませんので、その場合は、#1さんの方法や、サブクエリーを使った方法を
試してください。そうでなければ、このSQL文で大丈夫です。)

(SQL1)
SELECT AA, BB, MAX ( to_char( CC, 'yyyy/mm/dd' ) || '*' || DD || '*' || EE )
FROM TEST_TABLE
GROUP BY AA,BB;

MAX()で、同一キーの、列CC, DD, EE を文字連結した文字列の最大値を返します。
日付形である列CCを連結文字の先頭に配置しているため、直近の日付のレコードの
CC, DD, EE を取得できます。 '*' は一応デメリッタです。
プログラム側で、3つ目の列(MAXの列)の値を分解し、それぞれの列の値(CC, DD, EE)を
取得します。


(SQL2)

プログラム側で3つ目の列(MAXの列)の文字列分解処理を行いたくない場合、
SQL1をFROM句のサブクエリーとすることで、文字列分解処理をSQL文側で
行うことも可能です。この場合のサブクエリーはレスポンスに殆ど悪影響を
及ぼしません。

下記では、列DD, EEの値は固定長と仮定していますが、可変長の場合は、
INSTR()でデミリタ '*'を検索し、切り取る位置を動的に処理することも可能です。
また、プログラムから使用することも考慮し、列名 CC, DD, EE も別名をつけて、
アクセスできるようにしています。

select AA, BB, to_date ( substr( max_row, 1, 10 ), 'yyyy/mm/dd' ) CC,
substr( max_row, 12, 2 ) DD, substr( max_row, 15, 2 ) EE
from (
SELECT AA, BB, MAX ( to_char( CC, 'yyyy/mm/dd' ) || '*' || DD || '*' || EE ) max_row
FROM TEST_TABLE
GROUP BY AA,BB);

投稿日時 - 2004-05-17 21:07:28

お礼

デリミタの使用ですか。これまた考えていませんでした。
というか今までそういう考えをしたことがありませんでした。これからも役立ちそうなことを教えていただけたと思います。
また、MAXの中でMAX ( to_char( CC, 'yyyy/mm/dd' ) || '*' || DD || '*' || EE )
という使い方も出来るというのは知りませんでした。
ちょっとビックリしました。

お二方のご助言でなんとかなりそうです
ありがとうございました!

投稿日時 - 2004-05-18 09:24:42

あなたにオススメの質問