複数テーブル間の集計

以下のようなテーブル構造のデータベースですが

---

社員tb
　社員ID
　社員名

地区tb
　地区ID
　地区名

成績tb
　成績ID
　社員ID
　地区ID
　営業成績

---

「地区ごとの成績トップ社員の一覧」を出そうとして

SELECT
　社員tb.社員ID,
　社員tb.社員名,
　地区tb.地区ID,
　地区tb.地区名,
　max(成績tb.営業成績)
FROM
　社員tb,地区tb,成績tb
WHERE
　社員tb.社員ID = 成績tb.社員ID
　AND 地区tb.地区ID = 成績tb.地区ID
GROUP BY
　地区ID

というSQL文を実行したら
なぜか社員名フィールドがすべて特定の社員しか表示されない
（Ｂが地区トップのはずなのにＡと表示される等）状態です。

どなたか解決法をご教示下さい…
ちなみにバージョンは 5.0.33 です。

No.1 ベストアンサー 回答者： chukenkenkou 回答日時： 2007/03/02 16:11

表の列構成だけでなく、各表のデータ例、得たい結果例も示してください。

回答者側で作るのは、意外と大変なんです。

>「地区ごとの成績トップ社員の一覧」を出そうとして

group byの使い方を間違っている(MySQLでは文法エラーにしていないが、標準SQLや他のRDBMSでは文法誤り）し、成績トップの社員を求めるSQLになっていません。

標準SQLや他のRDBMSでは、group by指定時、selectで指定できるのは、group byで指定した列とsumやmaxなどの関数だけです。MySQLではその制限を緩和してエラーとしない代わりに、group byで指定した列以外をselectで指定する場合は、一意になるようにしなければ、結果はどうなるか保証されていません。【誤り１】

例）
正しい→select c1,max(c2) from t1 group by c1

誤り→select c1,c3,max(c2) from t1 group by c1
　標準SQLや他のRDBMSでは文法エラー。
　MySQLでは、c1のグループ毎にc3の値が一意になるなら指定していい。一意にならないなら、結果は保証しない。

質問者さんが作ったSQLでは、MAX(営業成績)という記述をしていますが、誰が上げた成績かを求めるSQLになっていません。【誤り２】

求め方を、段階的に(1)～(3)で示します。
(3)が、「地区ごとの成績トップ社員の一覧」を求めるSQLになります。

(1)地区毎の営業成績の最大値を求める
select 地区ID,max(営業成績)
from 成績tb
group by 地区ID
;

(2)(1)+社員IDを求める
select 社員ID,地区ID,営業成績
from 成績tb
where (地区ID,営業成績) in(
select 地区ID,max(営業成績)
from 成績tb
group by 地区ID
)
;

(3)(2)+IDを名称に変換
select x.社員ID,社員名,x.地区ID,地区名,営業成績
from 成績tb as x,社員tb as y,地区tb as z
where (x.地区ID,営業成績) in(
select 地区ID,max(営業成績)
from 成績tb
group by 地区ID
)
and x.社員ID=y.社員ID and x.地区ID=z.地区ID
order by x.社員ID,x.地区ID
;

この回答へのお礼

＞回答者側で作るのは、意外と大変なんです。
思慮の足りない質問の仕方でした…
大変申し訳ないとともに、それでも親切にお教えいただいて
大変ありがたく思っております。

誤り１・誤り２の指摘もありがとうございます。
さらに、実際的かつ段階的に説明していただいたお陰で
SQLに関しての知識を深めることができました。

ありがとうございました。

お礼日時：2007/03/02 17:07