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

解決済みの質問

SQLの書き方

以下の条件を取得したいのですがどのようなSQLを組めばいいか悩んでます。

テーブルA(列はC1,C2,C3,C4 PKはC1,C2です。)

■テーブルAのデータ
C1  C2   C3   C4
1   1   50   備考1です。
1   2   75   備考2です。
2   1   25   備考3です。
3   1   75   備考4です。
3   2   90   備考5です。
3   3   50   備考6です。

■取得したい結果
C1  C2   C3   C4
1   2   75   備考2です。
2   1   25   備考3です。
3   2   90   備考5です。

■取得したい条件
C1でグルーピングしたなかでC3の値が一番大きな行を取得する。


現在は以下のようなSQLを書いています。
SELCT T1.*
FROM テーブルA T1,
(SELECT C1,MAX(C3) AS C3MAX FROM テーブルA GROUP BY C1) T2
WHERE T1.C1 = T2.C1
AND T1.C3 = T2.C3MAX

もう少し簡単なSQLで出来そうですが、うまく思いつきません。
宜しくお願いします。

投稿日時 - 2009-02-26 14:00:43

QNo.4750759

困ってます

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

掲示板を勘違いしてOracleで作成していました。
create table a
(
c1 int,
c2 int,
c3 int,
c4 nvarchar(50)
);

select x.C1,x.C2,x.C3,x.C4
from (
select C1,C2,C3,C4,max(C3) over(partition by C1) max_c3
from a) AS x
where x.C3 = x.max_c3;

SQL Serverだとこっち。

投稿日時 - 2009-02-26 18:42:21

お礼

ご回答ありがとうございます。
対象のテーブルが120万件以上あるのでパフォーマンスを考慮したSQLだととても助かります。
試してみたいと思います。

投稿日時 - 2009-02-27 19:28:51

ANo.4

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

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

回答(4)

ANo.3

あえていうなら分析関数を使う。
テーブルAのFullスキャン2回を1回に減らせるからデータ量が多いと効果があるかもしれません。

create table a
(
c1 number(2),
c2 number(2),
c3 number(2),
c4 varchar2(50)
);
insert into a values(1,1,50,'備考1です。');
insert into a values(1,2,75,'備考2です。');
insert into a values(2,1,25,'備考3です。');
insert into a values(3,1,75,'備考4です。');
insert into a values(3,2,90,'備考5です。');
insert into a values(3,3,50,'備考6です。');


select C1,C2,C3,C4
from (
select C1,C2,C3,C4,max(C3) over(partition by C1) max_c3
from a)
where C3 = max_c3

C1 C2 C3 C4
---------- ---------- ---------- -----------
1 2 75 備考2です。
2 1 25 備考3です。
3 2 90 備考5です。
実行計画
----------------------------------------------------------
Plan hash value: 2104940997

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 474 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 6 | 474 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 6 | 396 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 6 | 396 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C3"="MAX_C3")

Note
-----
- dynamic sampling used for this statement


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

select T1.*
FROM A T1,(SELECT C1,MAX(C3) AS C3MAX FROM A GROUP BY C1) T2
WHERE T1.C1 = T2.C1
AND T1.C3 = T2.C3MAX;

実行計画
----------------------------------------------------------
Plan hash value: 494933846

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 92 | 8 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 6 | 396 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 6 | 156 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 6 | 156 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| A | 6 | 156 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."C1"="T2"."C1" AND "T1"."C3"="T2"."C3MAX")

Note
-----
- dynamic sampling used for this statement


統計
----------------------------------------------------------
44 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
3 rows processed

投稿日時 - 2009-02-26 18:35:34

ANo.2

現状のSQLでは一番単純な方法と思います

WHERE句で最大値の行をなんて指定できるようになると違うのでしょうが…

でも、この書き方はOracle風ですね
SQL Serverなら
SELCT T1.*
FROM テーブルA AS T1
INNER JOIN(SELECT C1,MAX(C3) AS C3MAX FROM テーブルA GROUP BY C1) AS T2
ON T1.C1 = T2.C1
AND T1.C3 = T2.C3MAX

かな?
今はOracleでもJOIN記述も出来ますが(笑)

投稿日時 - 2009-02-26 14:43:01

お礼

>WHERE句で最大値の行をなんて指定できるようになると違うのでしょうが…
そのようなことができないかなと思い投稿した次第です。

現状できないようですね・・・。

今のSQLで構築しようと思います。ありがとうございました。

投稿日時 - 2009-02-26 14:59:19

ANo.1

現在のSQLで問題ないように思われます。

投稿日時 - 2009-02-26 14:06:42

お礼

今のSQLで問題ないようですね。
ありがとうございました。

投稿日時 - 2009-02-26 15:00:11

あなたにオススメの質問