質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.61%

並びというフィールドに別の行から値を持ってくるSQLを考えています

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,171

score 13

t仕入というテーブルの複数の行から値を持ってくるSQL文を考えています

Microsoft Accessでシステムを組みデータ側はmysqlを動かしてODBC接続しております。
以下のようなテーブルがありまして
並びというフィールドに値を挿入したいと考えています。

日付 仕入先id 商品ID 価格 並び
2/2 1 1 600
2/2 2 1 700
2/2 3 1 700
2/2 4 1 500
2/2 5 1 300
2/2 6 1 100
2/1 5 2 800
2/1 4 2 900
2/1 3 2 800
2/1 2 2 900
2/1 1 2 800
2/1 6 2 900

最終的に以下のようなテーブルにしたいと思っていま

日付 仕入先id 商品ID 価格 並び
2/2 1 1 600 677531
2/2 2 1 700 677531
2/2 3 1 700 677531
2/2 4 1 500 677531
2/2 5 1 300 677531
2/2 6 1 100 677531
2/1 5 2 800 293989
2/1 4 2 900 293989
2/1 3 2 300 293989
2/1 2 2 900 293989
2/1 1 2 200 293989
2/1 6 2 900 293989

並びカラムが必要な理由は行数が多く100万行ほどあり更新頻度は1日一回で問い合わせの頻度は1日に100回ほどでいろいろな条件が組み合わされるため一度テーブルを作ってしまってからSELECTクエリなどで動かしたほうが動作が早いと思ったのが理由です。

条件というのは例えば日付と仕入れ先idで絞って表示した場合に1行で6仕入先分の価格の目安がわかるので直感的に見やすい。
並びカラムで検索することによって6仕入先の価格変動の傾向が見やすいといったところを考えています。

並びカラムのルールは日付と商品id別で絞り
仕入先id順で並べた価格の
百の位を左から並べていきたいと思っています

価格は必ず100円以上999円以下になっています。
仕入れIDは1~6までが必ず1日1商品につき一つずつ存在します。
商品IDは日々1000種類ほどあります

update文をどのように記述したらいいでしょうか?
拙い説明文で申し訳ありませんが
よろしくお願いいたします。

GROUP_CONCATでも表示上は実現できそうでしたが
追加した後変更されるデータではないので
毎回select問い合わせのたびに処理を実行するより
テーブルに残したいと考えています。
よろしくお願いいたします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • mather

    2019/02/18 18:45

    > データベースがmysqlでAccessで制作をしている
    どういうことですか?
    > 日付と商品id別に仕入先id順で値を並べたい
    ORDER BY で並べればいいと思うのですが、なぜ「並び」カラムが必要なのか、どういったルールで「並び」カラムの値が決まるのかが書かれていません。

    キャンセル

  • Orlofsky

    2019/02/18 18:52

    タイトルと質問の内容が合っていませんから、質問の内容にふさわしいものに修正してください。

    キャンセル

  • mather

    2019/02/18 19:56

    まだ「並び」が必要な理由が理解できません。

    > いろいろな条件が組み合わされる
    どういった条件でしょうか。その条件について「並び」がどう関係するんでしょうか。
    > 一度テーブルを作ってしまってから動かしたほうが動作が早いと思った
    「動かす」とはSELECTクエリを実行するということでしょうか?

    内容がわかっていませんが、個人的な直感を言うと「並び」は必要なくなるのではと思っています。

    キャンセル

  • yambejp

    2019/02/18 19:59

    sampleが足りなすぎてどうしていいかわからない
    もっといっぱい例示ください

    キャンセル

回答 3

+2

テーブル名を T_Test とすると、下記のUPDATE文でご希望の結果になります。

UPDATE (
SELECT T2.価格\100 AS X, T1.並び
FROM T_Test AS T1 INNER JOIN T_Test AS T2
 ON T1.日付 = T2.日付 AND T1.商品ID = T2.商品ID
ORDER BY T2.仕入先id
) AS Q1 
SET Q1.並び = Q1.並び & Q1.X;

ODBC接続で 100万件だと実用的な速度になるかは分かりません。

原理は下記で紹介している方法と同じものになります。

更新クエリで定義域集計関数を使わずに集計する - hatena chips

複数の文字列置換をクエリで一気に実行する-改良版 - hatena chips

追記

のんびり検証しながら回答を作成していたら、解決済みになっちゃいましたが、
DJoinよりは、たぶん高速だと思います。
実データで検証した結果を出来れば教えてほしいです。

別案

上記の更新クエリは、先頭レコードから順次処理されていくということが前提になります。
いろいろ、検証してみた限りでは問題ないようですが、保証できるかといわれると断言はできないので、
DAOかADOで更新するほうが確実かも。

Public Sub UpdateOrd()
    Dim strSQL As String
    strSQL = "SELECT 価格, 並び FROM T_Test " & _
             "ORDER BY 日付, 商品ID, 仕入先id;"
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL)

    Dim strOrd As String, i As Long
    Do Until rs.EOF
        For i = 1 To 6
            strOrd = strOrd & (rs!価格 \ 100)
            rs.MoveNext
        Next
        rs.Move -6
        For i = 1 To 6
            rs.Edit
            rs!並び = strOrd
            rs.Update
            rs.MoveNext
        Next
        strOrd = ""
    Loop
    rs.Close
End Sub


仕入先は1~6で100%固定、抜けはない、という前提です。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/02/19 10:03

    シンプルに言うと自己結合によるループ処理ですね。

    キャンセル

  • 2019/02/20 19:10

    sazi様hatena様コメントありがとうございます。
    後学の為教えていただければなと思い再度コメントさせていただきました。
    動作は問題なさそうでしたが、今後SQLやAccessの仕様変更等で希望の動作をしなかったりする場合があるかもしれないが今のところ希望の動作をしない理由がないという認識で問題ないでしょうか?
    よろしくお願いいたします。

    キャンセル

  • 2019/02/20 19:36

    サブクエリで並べ替えを指定して、それに対して更新するSQLですので、サブクエリの並べ替えを無視して更新するという実装は考えにくいので、問題はないだろうと思いますが、仕様に明記されているわけではないので保証できるかというといえません。
    別案のDAOのレコードセットに対して更新する方法なら、問題ないと断言できます。

    キャンセル

checkベストアンサー

+1

日付と商品id別に仕入先id順で価格の内容を列挙するというのが意図なら、

mysqlならgroup_concat
AccessならDJoin

で取得は出来ます。

元が変更になる場合に常に更新を考える必要があるので、取得できるものはカラムとして保持しないのが基本です。

追記

update t仕入れ target set
並び=(
      SELECT GROUP_CONCAT(TRUNCATE(価格/100,0) separator '') FROM t仕入れ
      where 日付=target.日付 and 商品id=target.商品id
     )


※手打ちで検証していませんので、悪しからず。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/02/18 20:56

    並びの編集ルールは何ですか?
    仕入先ID順で価格の先頭1桁を並べるって事ですか?

    キャンセル

  • 2019/02/18 21:02

    はいそうです。
    こんな感じでしょうか?
    update t仕入れ set 並び =SELECT GROUP_CONCAT(価格/100) FROM t仕入れ GROUP BY 商品ID,日付;
    よろしくお願いいたします。

    キャンセル

  • 2019/02/18 21:40

    ありがとうございます!
    Accessですのでおっしゃられた通りGroup_CONCATが使えないみたいでしたのでDJoinで頑張ってみたいと思います。
    DJoin知りませんでした。高速版もあるみたいですので頑張ってみたいと思います。

    キャンセル

+1

UPDATE文は他の人の回答に期待するとして、
通常、行の追加・変更・削除があり得るから[並び]列は持ちません。
SQLではデータがどういう順番で入っていようと、ORDER BYで明示的にソートしないと出力順は保証されませんから、SELECT ... ORDER BY ... でソートされては?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る