初心者のFileMaker pro Q&A (旧掲示板)

ハナ

Guest

重複除外SQL文を改良したい

FMProAd14 Win8.1を使用しています。

売上履歴テーブルから顧客コード毎の商品コード1件につき、売上日付が最新の売上Noを1件取得し仮想リストにて表示したいと思っています。

<売上履歴テーブル>
売上No  商品コード  顧客コード  売上日付
   １            １              １       2016/01/01
   ２            １              １       2016/01/02
   ３            １              ２       2016/01/03
   ４            ２              １       2016/01/04
   ５            ２              ２       2016/01/05
   ６            ２              ２       2016/01/06

以下を取得したいです。

●顧客コード１の場合
売上No  商品コード  顧客コード  売上日付
   ２            １              １       2016/01/02
   ４            ２              １       2016/01/04

●顧客コード２の場合
売上No  商品コード  顧客コード  売上日付
   ３            １              ２       2016/01/03
   ６            ２              ２       2016/01/06

現在は下のSQLで一応表示させることはできているのですが、売上履歴の件数が多い顧客の場合には表示が遅くなったり、応答なしになったりしてしまいます。

Let ( [

~query    = "

SELECT MAX(\"売上No\"), \"商品コード\", MAX(\"売上日付\")
FROM \"売上履歴\"
WHERE \"顧客コード\" = ?
GROUP BY \"商品コード\"

" ] ;

ExecuteSQL ( ~query ; "|" ; "" ; 売上履歴::g検索_顧客コード )
)

そこでINNER JOINが重複除外に良さそうだったので、下のSQL文を作成してみたのですが「?」が表示され動きません。

Let ( [

~query    = "

SELECT a.\"売上No\", a.\"商品コード\", a.\"売上日付\"
FROM \"売上履歴\" AS a
INNER JOIN (
  SELECT \"商品コード\", MAX(\"売上日付\") AS \"売上日付\"
  FROM \"売上履歴\"
  WHERE \"顧客コード\" = ?
  GROUP BY \"商品コード\"
) AS c
ON c.\"商品コード\" = a.\"商品コード\" and c.\"売上日付\" = a.\"売上日付\"

" ] ;

ExecuteSQL ( ~query ; "|" ; "" ; 売上履歴::g検索_顧客コード )
)

SQL文の間違いや、他に良いSQL文がありましたら教えて下さい。
よろしくお願い致します。

barikan

Guest

Re: 重複除外SQL文を改良したい

SQLでなく、小計パートと集計フィールドの使用を検討して下さい。

1. 売上履歴テーブルに商品コードを対象とする小計パートを追加
2. その小計パート上に売上日付の最大集計フィールドを配置
3. ボディパートを削除
4. 顧客コードで検索
5. 商品コードでソート

これで要望の一覧を出すことが出来ます。

qb_dp

Member

Re: 重複除外SQL文を改良したい

INNER JOIN (
  SELECT \"商品コード\", .....

ココだけに反応です。
ExecuteSQL は、サブクエリを使えないと言う残念な仕様です。なので上記の記述は、使えません。

qb_dp

Member

Re: 重複除外SQL文を改良したい

あれ!? サブクエリ 使えましたっけ？ 使えないとどっかに書いてあったと思ったのですが、見当たらない。

ぽんかん

Guest

Re: 重複除外SQL文を改良したい

SELECTの中では使えるけど、FROMの中では使えない。
だった気がします。

ハナ

Guest

Re: 重複除外SQL文を改良したい

すいません、大事なことを書くのを忘れていました。

・取得した履歴は、レコードを選択した後にスクリプトで他のテーブルに商品コードなどを持っていきたいです。
・検索後に売上日付を降順でソートします。

barikan様
せっかく返信頂いたのに当方の記載不足で申し訳ありません。
１つのソートで一覧表示だけする時には早くてよかったです、ありがとうございました。
勉強になりました。

qb_dp様、ぽんかん様
返信ありがとうございます。
そんな仕様だったのですね。
道理で動かない訳ですね。

引き続き、重複除外方法の情報などありましたらよろしくお願い致します。

チポ

Member

Re: 重複除外SQL文を改良したい

SQLはよく分かりませんが、

商品コード＆顧客コード
がユニークになる別テーブルを作り、
それに売上履歴の上記の全ての組み合わせのレコードを作成。

リレーションの設定で、ご希望のレコードの表示が出来ますね。

別テーブルのレコード作成は、
最初だけは全レコードのインポートが必要ですが、
その後は売上履歴で新規レコードを作るたびに
それと同じレコードの有無を判定すればいいでしょう。

Hiro

Member

Re: 重複除外SQL文を改良したい

外国語を話すのは、母国語ではどうしても表現できないケースに限るべきと思っています。
FM君はこの外国語が苦手で片言しか話せませんし、しゃべるのもバカがつくほど鈍いです。

FMでのより簡単な解決案例としては、
条件に適合するレコードのレコードIDを抽出して、そのレコードIDをキーにリレーション参照します。

更にもっとイージーに、参照するだけなら、
全レコードをヒットする[X]デカルト積リレーションポータルに、条件を計算式でフィルター掛け表示とか、

Last edited by Hiro (2016-01-20 11:18:08)

calcer

Guest

Re: 重複除外SQL文を改良したい

WHERE の中は使えた気がする。

SELECT a.\"売上No\", a.\"商品コード\", a.\"売上日付\"
FROM \"売上履歴\" AS a
WHERE a.\"売上日付\" = (
  SELECT MAX(\"売上日付\")
  FROM \"売上履歴\"
  WHERE \"顧客コード\" = a.\"顧客コード\"
    AND \"商品コード\" = a.\"商品コード\"
)
AND WHERE \"顧客コード\" = ?

同じ日付が複数あるとだめかな？

いずれにしろ、SQLではあまり速さは期待できない。

ハナ

Guest

Re: 重複除外SQL文を改良したい

チポ様
SQLが使えない場合は最終手段として作成することになりそうです。
詳細な作成方法を教えて頂いてありがとうございます。

Hiro様
非保存フィールドの検索を試してみましたが件数が多いと表示がかなり遅くなってしまいますね。
デカルト積リレーションポータルは作成したことがないので勉強してみます。
ありがとうございました。

calcer様
WHEREの中で使えることを確認しました。ありがとうございました。
表示はかなり遅いですね、SQLの限界を感じてきています。