- PR -

最多取引先番号取得SQLの作り方を教えて下さい

1|2 次のページへ»
投稿者投稿内容
Ｒ・田中一郎
ぬし
会議室デビュー日: 2005/11/03
投稿数: 979
投稿日時: 2007-09-10 10:54
品番ごとの最多取引先番号を取得する方法を調べています。

品番マスターテーブルのフィールドが「品番(PrimaryKey)・名称」
注文テーブルのフィールドが「注文番号(PrimaryKey)・品番・仕入先番号・・・・」
取得したいビューのフィールドは「品番・名称・最多仕入先番号」

最多取引先とは、注文テーブルに登録されている件数の最も多い仕入先番号です。
以下の部分まで書いて、手がとまってしまいました。

SELECT 品番, 仕入先番号
FROM 注文テーブル
GROUP BY 品番, 仕入先番号;
ORDER BY Count(注文番号)
LIMIT 0, 1

これで全品番で最多仕入先番号を取得することはできそうなのですが、品番単位でこれを行い、品番マスターと結合させる方法がわかりません。

(追記)
DBMSは、MySQLです。書いたつもりになっていました。ごめんなさい。

_________________
Ｒ・田中一郎 - 　R.Tanaka.Ichiro’s Blog 満員御礼

[ メッセージ編集済み 編集者: Ｒ・田中一郎 編集日時 2007-09-10 13:14 ]
よっしー
大ベテラン
会議室デビュー日: 2007/05/17
投稿数: 143
投稿日時: 2007-09-10 12:48
DBはなんですか？って、ぬしさんに聞くのもなんですが。

(
SELECT
品番
, 仕入先番号
, COUNT(*) CNT
FROM
注文テーブル
)
のサブクエリを使って適当にゴニョゴニュすればよいのではないでしょうか？
かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2007-09-10 12:55
DBMSは書きましょう。
たぶん、MySQLだとは思うけど...

MySQLなら表式(Inline View)が使えたような気がしなたようなしないような。
したという前提で、
コード:
SELECT
	V.品番, MAX(V.注文数) AS 注文数
FROM
	(SELECT 
		品番, 仕入先番号, COUNT(注文番号) AS 注文数
	FROM 
		注文テーブル
	GROUP BY 
		品番, 仕入先番号) V
GROUP BY
	V.品番

とでもしたら、よさげ
相変わらず未確認なので、どこかでSQLエラーは出るかもね。
Ｒ・田中一郎
ぬし
会議室デビュー日: 2005/11/03
投稿数: 979
投稿日時: 2007-09-10 13:49
引用:

よっしーさんの書き込み (2007-09-10 12:48) より:

DBはなんですか？って、ぬしさんに聞くのもなんですが。

すみません。MySQL です。質問本文にも加筆しておきました。

引用:

よっしーさんの書き込み (2007-09-10 12:48) より:

のサブクエリを使って適当にゴニョゴニュすればよいのではないでしょうか？

その「適当にゴニョゴニョ」の内容をご教示いただけると助かります^^;

引用:

かずくんさんの書き込み (2007-09-10 12:55) より:
DBMSは書きましょう。
たぶん、MySQLだとは思うけど...

すみません。MySQL です。質問本文にも加筆しておきました。
（ひょっとして、LIMIT から MySQL と推測したのでしょうか？）

引用:

かずくんさんの書き込み (2007-09-10 12:55) より:

コード:
SELECT
	V.品番, MAX(V.注文数) AS 注文数
FROM
	(SELECT 
		品番, 仕入先番号, COUNT(注文番号) AS 注文数
	FROM 
		注文テーブル
	GROUP BY 
		品番, 仕入先番号) V
GROUP BY
	V.品番

とでもしたら、よさげ
相変わらず未確認なので、どこかでSQLエラーは出るかもね。

SQLエラーは良いのですが、この SQL の目的は注文数の最大値を件数から取得しているだけということになるような気がします。
_________________
Ｒ・田中一郎 - 　R.Tanaka.Ichiro’s Blog
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2007-09-10 14:01
「品番, 仕入先番号, 注文数」というビューと、
「品番, 最多注文数」というビューを結合して、
最多注文数の仕入先番号を取得する。という手順は如何でしょうか？

最多注文数が複数あるケースでは、複数件ヒットしてしまいますが。

※以下検証していません。

コード:
SELECT
   注文数V.品番, 注文数V.仕入先番号

FROM
   (SELECT
        品番, 仕入先番号, COUNT(*) as 注文数
    FROM
        注文テーブル
    GROUP BY
        品番, 仕入先番号
   ) 注文数V,

   (SELECT
        品番, MAX(注文数) as 最多注文数
    FROM
       (SELECT
           品番, 仕入先番号, COUNT(*) as 注文数
        FROM
           注文テーブル
        GROUP BY
           品番, 仕入先番号
       )
    GROUP BY
       品番
   ) 最多注文数V

WHERE
   注文数V.品番   = 最多注文数V.品番 and
   注文数V.注文数 = 最多注文数V.最多注文数

かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2007-09-10 15:16
引用:

この SQL の目的は注文数の最大値を件数から取得しているだけということになるような気がします。

すまん、よく読んでなかった。

最大注文数は、比較のためだけに取得するものなので、
こあらさんの回答を元に、EXISTSにおいてみました
コード:
SELECT
   注文数V.品番, 注文数V.仕入先番号

FROM
   (SELECT
        品番, 仕入先番号, COUNT(*) AS 注文数
    FROM
        注文テーブル
    GROUP BY
        品番, 仕入先番号
   ) 注文数V
WHERE
    EXISTS (SELECT 1 FROM
        (SELECT
            品番, MAX(注文数) AS 最多注文数
        FROM
           (SELECT
               品番, 仕入先番号, COUNT(*) AS 注文数
            FROM
               注文テーブル
            GROUP BY
               品番, 仕入先番号
           )
        GROUP BY
           品番
       ) 最多注文数V
    WHERE
        注文数V.品番 = 最多注文数V.品番
        AND 注文数V.注文数 = 最多注文数V.最多注文数
    )


コード:
SELECT
    品番, 仕入先番号, COUNT(*) AS 注文数
FROM
    注文テーブル
GROUP BY
    品番, 仕入先番号

が二度出るのが、いや〜んな感じ。
共通表式(WITH〜)が使えれば、まとめれるけど、MySQLが対応しているかどうかは分かりません。

引用:

すみません。MySQL です。質問本文にも加筆しておきました。
（ひょっとして、LIMIT から MySQL と推測したのでしょうか？）

エスパーをなめてもらっては困る（大嘘）
よっしー
大ベテラン
会議室デビュー日: 2007/05/17
投稿数: 143
投稿日時: 2007-09-10 16:18
引用:

その「適当にゴニョゴニョ」の内容をご教示いただけると助かります^^;


すでに皆さんが回答されたので、かずくんさんの回答を元に、NOT EXISTSにおいてみました
コード:
SELECT
   注文数V.品番, 注文数V.仕入先番号

FROM
   (SELECT
        品番, 仕入先番号, COUNT(*) AS 注文数
    FROM
        注文テーブル
    GROUP BY
        品番, 仕入先番号
   ) 注文数V
WHERE
    NOT EXISTS (
　　　　　　SELECT 1 FROM
               注文テーブル
            WHERE
               品番 = 注文数V.品番
            GROUP BY
               品番, 仕入先番号
            HAVING
               COUNT(*) < 注文数V.注文数
           )


まったく検証していないので、違ってたら無視してください。
パフォーマンスも良くなさそうですし。
でも、この手のSQLってよく掲示板で話題になりますね。
Ｒ・田中一郎
ぬし
会議室デビュー日: 2005/11/03
投稿数: 979
投稿日時: 2007-09-10 19:28
こあらさん、エスパーかずくん、よっしーさん、ありがとうございました。

サンプルとしてアップしていただいた SQL は全て確認して理解できました。

最後のよっしーさんのところが一番難関でした。が、何とか理解できました。
(HAVING 句の符号は、逆ですよね？)

引用:

よっしーさんの書き込み (2007-09-10 16:18) より:

でも、この手のSQLってよく掲示板で話題になりますね。

僕も含めてですが、SQL なロジックを組み立てる脳みそができあがっていない段階の人が多いのではないかと思います。
（そして、皆さんのサンプルを見て、目から鱗を落とす訳です）

＃実は、今朝の時点では、サブクエリー自身よくわからなかったりしたのです^^;
_________________
Ｒ・田中一郎 - 　R.Tanaka.Ichiro’s Blog
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）