無視されるdistinct

質問

お世話になります

　大規模商談をまとめた営業のリストを作る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を書けばよろしいでしょうか。
よろしくお願いいたします。

回答No.4

chukenkenkou · ベストアンサー

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表を結合する

回答No.5

chukenkenkou

#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
;

お礼

すばらしいです。これは難しいんじゃないかと思っていたので、正直いって驚きました。
　実は質問を投稿する前から、これはgroup by で解決できそうに見えて実はそうじゃない、というパターンじゃないかという気がしていました。ですが相関サブクエリなどをちょっと書いてみても、どうもいい方法が思いつかないので質問させていただいた次第です。
　group by なしの having というのはまだあまり勉強していないところなので、これを機に調べてみたいと思います。
　SQLの奥の深さを改めて教えていただいた気分です。本当にありがとうございました。

回答No.3

denbee

回答にあるような条件づけをしたいのであれば、Distinctではいけません。
ちゃんとamountがMAXのものだけを収集する条件づけのためにも、Group by句が必要です。

とりあえず、以下のページを参考にしてみてください。
http://www.techscore.com/tech/sql/05_08.html

補足

ありがとうございます。
実は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を付けても付けなくても同じになります

name | max | customer
------+-------+----------
山田 | 20000 | XX商事
田中 | 10000 | BB電機
山田 | 8000 | RR証券
高橋 | 2000 | ZZ不動産

　group by を使用するとして、名前の重複を避けるにはどのようにしたらよろしいでしょうか。

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

回答No.2

denbee

何をしたいのかがよくわかりません。
上記の例ですと、山田は2件の商談をまとめたにもかかわらず、どちらか1件（金額が大きい方？）でしか
ランキングに載せたくないわけですか？

「各部員のまとめた商談を、金額の大きい順に並べる。
ただし、複数の商談をまとめた部員は、もっとも金額の大きい商談だけを
表示させる」
という動作？

補足

はい、そういうことです。

例を変えるならば、こんなものです

１ ２．１９．４１ 渋井　陽子 三井住友海上 ２００４．　９．２５ 大　　　阪
２ ２．２１．４７ 高橋　尚子 積 水 化 学 １９９８．１２．０６ バ ン コ ク
３ ２．２１．１８ 野口みずき グローバリー ２００３．　１．２６ 大　　　阪
４ ２．２１．４５ 千葉　真子 豊田自動織機 ２００３．　１．２６ 大　　　阪
５ ２．２１．５１ 坂本　直子 天　満　屋 ２００３．　１．２６ 大　　　阪
６ ２．２２．１２ 山口　衛里 天　満　屋 １９９９．１１．２１ 東　　　京
７ ２．２２．４６ 土佐　礼子 三井住友海上 ２００２．　４．１４ ロ ン ド ン
８ ２．２２．５６ 弘山　晴美 資　生　堂 ２０００．　１．３０ 大　　　阪
９ ２．２３．２６ 大南　博美 ＵＦＪ銀行 ２００４．　９．２５ ベ ル リ ン
１０ ２．２３．３０ 小崎　まり ノ ー リ ツ ２００３．　１．２６ 大　　　阪

http://www.geocities.jp/majikanahappy/marathonkiroku.html

高橋尚子は2時間22分台で何回か走っていますが、ここには載ってません。

回答No.1

chukenkenkou

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

重複排除しているのは、nameでだけでなく、name,amount,customerの組み合わせでの指定になっています。

>期待している検索結果

「RR証券」を検索対象から外すのは、どういう条件でですか？

補足

ありがとうございます。
なるほど、distinctは全てにかかるのですね。とても勉強になりました。

RR証券をはずす条件（というか、理由）は、
この検索結果は営業部員個人のランキングですので、山田さんが1位と3位に2回出てくるのは望ましくない、ということです。
上記、補足になっておりますでしょうか。
よろしくお願いいたします。