Q&A
> データベースがmysqlでAccessで制作をしている
どういうことですか？
> 日付と商品id別に仕入先id順で値を並べたい
ORDER BY で並べればいいと思うのですが、なぜ「並び」カラムが必要なのか、どういったルールで「並び」カラムの値が決まるのかが書かれていません。
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問い合わせのたびに処理を実行するより
テーブルに残したいと考えています。
よろしくお願いいたします。
タイトルと質問の内容が合っていませんから、質問の内容にふさわしいものに修正してください。
まだ「並び」が必要な理由が理解できません。
> いろいろな条件が組み合わされる
どういった条件でしょうか。その条件について「並び」がどう関係するんでしょうか。
> 一度テーブルを作ってしまってから動かしたほうが動作が早いと思った
「動かす」とはSELECTクエリを実行するということでしょうか？
内容がわかっていませんが、個人的な直感を言うと「並び」は必要なくなるのではと思っています。
sampleが足りなすぎてどうしていいかわからない
もっといっぱい例示ください
回答3件
0
テーブル名を T_Test とすると、下記のUPDATE文でご希望の結果になります。
sql
1UPDATE ( 2SELECT T2.価格\100 AS X, T1.並び 3FROM T_Test AS T1 INNER JOIN T_Test AS T2 4 ON T1.日付 = T2.日付 AND T1.商品ID = T2.商品ID 5ORDER BY T2.仕入先id 6) AS Q1 7SET Q1.並び = Q1.並び & Q1.X;
ODBC接続で 100万件だと実用的な速度になるかは分かりません。
原理は下記で紹介している方法と同じものになります。
更新クエリで定義域集計関数を使わずに集計する - hatena chips
複数の文字列置換をクエリで一気に実行する-改良版 - hatena chips
追記
のんびり検証しながら回答を作成していたら、解決済みになっちゃいましたが、
DJoinよりは、たぶん高速だと思います。
実データで検証した結果を出来れば教えてほしいです。
別案
上記の更新クエリは、先頭レコードから順次処理されていくということが前提になります。
いろいろ、検証してみた限りでは問題ないようですが、保証できるかといわれると断言はできないので、
DAOかADOで更新するほうが確実かも。
vba
1Public Sub UpdateOrd() 2 Dim strSQL As String 3 strSQL = "SELECT 価格, 並び FROM T_Test " & _ 4 "ORDER BY 日付, 商品ID, 仕入先id;" 5 Dim rs As DAO.Recordset 6 Set rs = CurrentDb.OpenRecordset(strSQL) 7 8 Dim strOrd As String, i As Long 9 Do Until rs.EOF 10 For i = 1 To 6 11 strOrd = strOrd & (rs!価格 \ 100) 12 rs.MoveNext 13 Next 14 rs.Move -6 15 For i = 1 To 6 16 rs.Edit 17 rs!並び = strOrd 18 rs.Update 19 rs.MoveNext 20 Next 21 strOrd = "" 22 Loop 23 rs.Close 24End Sub
仕入先は1～6で100%固定、抜けはない、という前提です。
投稿2019/02/18 12:47
編集2019/02/18 15:57
総合スコア33715
完全に完璧に100点満点でございました。
hatena様いつもブログ滅茶苦茶拝見させていただいております。
hatena様に解決済後にも関わらずご回答いただくなんて・・・
感動です。。。本当にありがとうございました。
TERA175064は誤記ですか？
リンク先見ました。
結果セットに対する更新だと考えても単なる更新が累計になるのは素直に受け入れ難い。
再帰的な動きですけど、再帰が明記されている訳でもないから、いつの日にかこの動作は保証されなくなる可能性もありそうな気もします。
再帰というか、ループ処理のネストと同じような動きかな。
単純な更新クエリは、
先頭レコードから最終レコードまで順に更新していくループ処理、
今回の場合は、
日付、仕入先id、商品ID でユニークなのを、あえて、
仕入先id は結合しないことによって、仕入先id の6種類分繰り返し出力されます。
これを更新クエリで順次結合していくという処理になります。
再帰と言ったのはrecursiveの事で、行われるのはループ処理みたいなものです。
シンプルに言うと自己結合によるループ処理ですね。
sazi様hatena様コメントありがとうございます。
後学の為教えていただければなと思い再度コメントさせていただきました。
動作は問題なさそうでしたが、今後SQLやAccessの仕様変更等で希望の動作をしなかったりする場合があるかもしれないが今のところ希望の動作をしない理由がないという認識で問題ないでしょうか？
よろしくお願いいたします。
サブクエリで並べ替えを指定して、それに対して更新するSQLですので、サブクエリの並べ替えを無視して更新するという実装は考えにくいので、問題はないだろうと思いますが、仕様に明記されているわけではないので保証できるかというといえません。
別案のDAOのレコードセットに対して更新する方法なら、問題ないと断言できます。
0
ベストアンサー
日付と商品id別に仕入先id順で価格の内容を列挙するというのが意図なら、
mysqlならgroup_concat
AccessならDJoin
で取得は出来ます。
元が変更になる場合に常に更新を考える必要があるので、取得できるものはカラムとして保持しないのが基本です。
追記
SQL
1update t仕入れ target set 2並び=( 3 SELECT GROUP_CONCAT(TRUNCATE(価格/100,0) separator '') FROM t仕入れ 4 where 日付=target.日付 and 商品id=target.商品id 5 )
※手打ちで検証していませんので、悪しからず。
投稿2019/02/18 10:51
編集2019/02/18 12:24総合スコア25184
GROUP_CONCATでも表示上は実現できそうでしたが
追加した後変更されるデータではないので
毎回select問い合わせのたびに処理を実行するより
テーブルに残したいと考えています。
よろしくお願いいたします。
例えば後付けで仕入先が追加されるような事は無いという事ですか？
更新自体はgroup_concatで取得したもので更新すれば良いかと。
はい、仕入先は１～６で100%固定です。
よろしくお願いいたします。
並びの編集ルールは何ですか？
仕入先ID順で価格の先頭１桁を並べるって事ですか？
はいそうです。
こんな感じでしょうか？
update t仕入れ set 並び =SELECT GROUP_CONCAT(価格/100) FROM t仕入れ GROUP BY 商品ID,日付;
よろしくお願いいたします。
ありがとうございます！
Accessですのでおっしゃられた通りGroup_CONCATが使えないみたいでしたのでDJoinで頑張ってみたいと思います。
DJoin知りませんでした。高速版もあるみたいですので頑張ってみたいと思います。
0
UPDATE文は他の人の回答に期待するとして、
通常、行の追加・変更・削除があり得るから[並び]列は持ちません。
SQLではデータがどういう順番で入っていようと、ORDER BYで明示的にソートしないと出力順は保証されませんから、SELECT ... ORDER BY ... でソートされては？
投稿2019/02/18 09:49
総合スコア16415
あなたの回答
tips
プレビュー
