無視されるdistinct

Question

お世話になります

　大規模商談をまとめた営業のリストを作るSQLを書こうとしています。
　
営業部員テーブル　（test.person）

 id | name
----+------
  1 | 山田
  2 | 高橋
  3 | 田中

商談テーブル (test.deal)
  
   id | person_id | customer | amount
----+-----------+----------+--------
  1 |         1 | XX商事   |  20000
  2 |         3 | BB電機   |  10000
  3 |         2 | ZZ不動産 |   2000
  4 |         1 | RR証券   |   8000
  

期待している検索結果

 name | amount | customer
------+--------+----------
 山田 |  20000 | XX商事
 田中 |  10000 | BB電機
 高橋 |   2000 | ZZ不動産

下記のようなSQLを書きました。

select distinct name,amount,customer 
from test.deal,test.person 
where person.id=deal.person_id 
order by amount desc

　しかし、nameをdistinctしているにもかかわらず、結果は下記の通り、「山田」が重複しています。

 name | amount | customer
------+--------+----------
 山田 |  20000 | XX商事
 田中 |  10000 | BB電機
 山田 |   8000 | RR証券
 高橋 |   2000 | ZZ不動産

いろいろと調べて、group by を使えばいい、というようなやり方を示している例も多かったのですが、それもうまくいきません。（必要であれば載せます）

期待通りの結果を得るにはどのようなSQLを書けばよろしいでしょうか。
よろしくお願いいたします。

chukenkenkou · Accepted Answer

SQLの基本操作の一つである「group by」を、理解できていませんね。
基本操作から、しっかり勉強すべきです。

>実はgroup by も試してみたのですが、どうもうまくいきません。
>とりあえず、下記のようなSQLを書いてみました。
>select name,max(amount),customer
>from test.deal,test.person
>where person.id=deal.person_id
>group by name
>order by max(amount) desc

理解できていないまま、いい加減なことをやっていますよ？

> ERROR: column "deal.customer" must appear in the GROUP BY clause or be >used in an aggregate function;
>言われるままに、group byの中にcustomerを入れると下記のような結果に
>なり、group byを付けても付けなくても同じになります

エラーの内容を取り違えて、デタラメな対処を行っていますよ？

>group by を使用するとして、名前の重複を避けるにはどのようにしたら

名前の重複を起こさない（つまり、どのデータを活かすか？）が、不明瞭だし、SQLとして指定されていないのですよ。
どうしたい（どのデータを活かしたい）のですか？

また、得たい結果（仕様）で、まだまだ不明瞭な点があります。

person_id毎に、最大のamountを得るのは簡単です。
しかし、同じperson_idで求めた最大のamountが、二箇所以上のcustomerで記録されていた場合、どういう結果を得たいのでしょうか？

理解できていないのに、デタラメの上にさらにデタラメを繰り返すのでなく、簡単なクエリから順次、組み立てていきましょう。

今回のクエリは、次の(1)～(3)の順に組み立てていくと、具体的な仕様が明確でない部分が明確になり、SQLの理解も進むと思います。

(1)test_deal表で、person_id毎に最大のamountを得る
(2)(1)に該当するtest_deal表のidを得る
　→全idとするのか、最大のidあるいは最小のidとするのか、仕様の明確化が必要
(3)(2)のidと、test_person表を結合する

chukenkenkou · Answer

#4回答の1箇所訂正。

＜訂正前＞
(3)(2)のidと、test_person表を結合する

↓
＜訂正後＞
(3)(2)のidの行のperson_idと、test_person表を結合する

＝＝＝＝＝以下、独り言＝＝＝＝＝
select
  person_id,max(amount)
 from test_deal
 group by person_id
;

select
  *
 from test_deal as d
 where id=(select min(id)
            from test_deal
            where d.person_id=person_id
            having d.amount=max(amount)
          )
 order by id
;

select
  *
 from test_person as p
  inner join (
select
  *
 from test_deal as d
 where id=(select min(id)
            from test_deal
            where d.person_id=person_id
            having d.amount=max(amount)
          )
             ) as x
 on p.id=x.person_id
 order by p.id
;

denbee · Answer

回答にあるような条件づけをしたいのであれば、Distinctではいけません。
ちゃんとamountがMAXのものだけを収集する条件づけのためにも、Group by句が必要です。

とりあえず、以下のページを参考にしてみてください。
http://www.techscore.com/tech/sql/05_08.html

denbee · Answer

何をしたいのかがよくわかりません。
上記の例ですと、山田は2件の商談をまとめたにもかかわらず、どちらか1件（金額が大きい方？）でしか
ランキングに載せたくないわけですか？

「各部員のまとめた商談を、金額の大きい順に並べる。
ただし、複数の商談をまとめた部員は、もっとも金額の大きい商談だけを
表示させる」
という動作？

chukenkenkou · Answer

>nameをdistinctしているにもかかわらず

重複排除しているのは、nameでだけでなく、name,amount,customerの組み合わせでの指定になっています。

>期待している検索結果

「RR証券」を検索対象から外すのは、どういう条件でですか？

無視されるdistinct

#4回答の1箇所訂正。

SQLの基本操作の一つである「group by」を、理解できていませんね。

回答にあるような条件づけをしたいのであれば、Distinctではいけません。

この回答への補足

何をしたいのかがよくわかりません。

この回答への補足

>nameをdistinctしているにもかかわらず

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング