SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ >>146
SQLおじさん、Qiitaに炎上記事投稿 周囲の反応と垢BANまでの流れ
http://togetter.com/li/1047474 教えてください
アクセスを使ってます
INSERT INTO 日時(日付,時刻)
SELECT 日付,時刻 FROM 日付,時刻
WHERE (日付,時刻) NOT IN(SELECT 日付,時刻 FROM 日時)
データをクロス結合して重複分を排除しつつ日時テーブルに書込みをしたいのですが、
上のSQLではエラーとなってしまいます
自分でも調べてはいるのですがどうにも分からず頼らせてもらえればと思います
よろしくお願いします それクロス集計じゃなくてただの直積じゃないのか…？ すみません、教えていただけますでしょうか。
ACCESSなのですが、
【テーブルA】
ID、商品名
10.90.10、鉛筆赤
10.90.20、鉛筆青
20.800.101、はさみ青
20.800.102、はさみ緑
305.001、のり青
305.005.100、のり黒
【テーブルB】
ID、値段
10.90、100円
20.800、200円
305、500円
というテーブルがあったとします。
テーブルBのIDを取得し、テーブルAから、テーブルBのIDの文字列にて始まるIDを取得し、テーブルAに値段列を付加するSQLを
作成しようとしているのですが、作成方法がいまいちわかりません。
例えば、
【抽出したい結果】
ID、商品名、テーブルBの値段
10.90.10、鉛筆赤、100円
10.90.20、鉛筆青、100円
20.800.101、はさみ青、200円
20.800.102、はさみ緑、200円
305.001、のり青、500円
305.005.100、のり黒、500円
のような感じです。
おそらく、テーブルAとテーブルBをleft joinする形になると思うのですが、
よい方法があれば教えていただけないでしょうか。 >>154
もしかして"10.90.10"で一つの項目に入っていて
そのうちの"10.90"と突き合わせたいとかいう話？
leftとmid組み合わせるとかinstr使うとかlike使うとか、いくつかやり方は思いつくけど
悪いことは言わないからまずDB設計見直せ >>155 早速のレス、ありがとうございます。
>>もしかして すみません、なぜか切れてしまいました。
>>もしかして"10.90.10"で一つの項目に入っていてそのうちの"10.90"と突き合わせたいとかいう話？
はい、そういうことをやりたいと考えています。IDの例があまりよくなかったので、サンプルを変更します。
【テーブルA】
ID、商品名
abc-10、鉛筆赤
abc-20、鉛筆青
ef-101、はさみ青
ef-102、はさみ緑
abdzz-001、のり青
abdzz-100、のり黒
【テーブルB】
ID、値段
abc、100円
ef、200円
abdzz、500円
【抽出したい結果】
ID、商品名、テーブルBの値段
abc-10、鉛筆赤、100円
abc-20、鉛筆青、100円
ef-101、はさみ青、200円
ef-102、はさみ緑、200円
abdzz-001、のり青、500円
abdzz-100、のり黒、500円
のような感じです。
データベース設計を見直したいのですが、もうシステムが動いてしまっていて、
変更がちょっと難しい状態なのです、、、
社内用のシステムでお客様で使っているものではないのが救いなのですが。 テーブルAに一列追加して
B用のキーを追加した方がいいぞ
キー列が変わることなんざ無いだろうし、insertするとこだけ弄ればいい
既にある列も３０分もありゃ出きるやろ
そしたら普通にインナージョインで処理できる >>158
それselect * してるやつがいたらこける可能性ある ＞社内用のシステムでお客様で使っているものではないのが救い
社内システムには直すお金がかけられないとかあるあるだけど
それ救いじゃなくて呪い（負債）だからな >>159
Accessの場合大分こけないはず
フォームとかではいちいちフィールド名指定するし
Select * のフィールド数不一致でエラー吐くパターンがむしろ想像できん
ソースは小規模Accessをフィールド建て増ししまくって用途10倍以上に増やした俺
まぁ、
A inner join B On A.ID like B.ID & '*'
でも動くだろうけど、ミスによるバグがクッソ増えそうだし嫌だわ わざわざ abczz じゃなく abdzz にしてる意図が気になるな >>162
likeしたときに
abc-とabcde-だと被るからじゃない？ >>157
> データベース設計を見直したいのですが、もうシステムが動いてしまっていて、
> 変更がちょっと難しい状態なのです、、、
正しいデータベース設計後、古いテーブルと同じ形式のViewを作ることができれば、
現行システムに影響を与えることなくデータベースの変更が可能。 >>165
view賛成
ま、弊社の場合はviewだらけで訳が分からなくなってるけどね（笑 >>157
クエリ追加したいってことは、少なくとも何らかの変更／追加があるわけで
そのうえでそのテーブルレイアウトで自分でクエリ書けないんだろ
だったらテーブルレイアウト直すべきだと思うけどね
ま、動いてて変えられんとかいう状況ならそのシステムに似たような事してるとこあるだろ
>>165-166
普通のDBMSならビューで逃げる手はあるけど、ACCESSって結構テーブルとクエリで扱い方に差があるからなぁ >>165
accessで困ってる初心者に追加可能な選択クエリとか書けるかっていう疑問はあるけど出来たらそれで良いかもね viewじゃ更新できないカラムのsqlあったらどうすんの oracleのmergeについて質問です。
oracleは11gを使っています。
とあるテーブルにmergeを使ってpkのレコードが無ければレコード追加、レコードがあれば何もしないというsqlがありました。
このようなsqlで行が無い場合はinsertと同じようにロックを取得すると思うのですが、
既に行がある場合ってロックは取得されるのでしょうか？
リファレンスを見ると細かいケースに言及せず、mergeの表ロックモードはsxとあるので、更新処理の有無に関わらずforupdateと同様にロックされるのかなぁと思っているのですが、、 【質問テンプレ】
・DBMS名とバージョン
Access？（Excel2013のデータ接続機能のところに書いて使いたいです）
・テーブルデータ
ID ｜Price｜Name
-----------------
1001 100 ガム
1002 200 あめ
1002 300 チョコ
1003 400 クッキー
1003 500 ポテチ
1003 600 ポテチ
・欲しい結果
ID ｜Price｜Name
-----------------
1001 100 ガム
1002 500 あめ,チョコ
1003 1500 クッキー,ポテチ,ポテチ
・説明
ID毎にPriceを合計してNameの値を結合したいです。
よろしくお願いします。 group_concat()があれば簡単なのに
Access用にはDJoinという関数を作って公開してる人がいたみたいだけど
ページが消えてる AccessならVBAでID受け取ってNameをカンマ連結した文字列返す関数作ればできんじゃね >>174-177
返信おそくなってすみません
質問に不足してた部分があったので
また質問しなおしたいと思います
どうもありがとうございました ・Postgresql 8.4
・テーブルデータ
｜year｜month
-----------------
2017 4
2017 6
2018 3
2018 4
・欲しい結果
｜year｜month
-----------------
2017 4
2017 6
2018 3
・説明
integerの列year、monthに年、月が書かれており、2017年4月〜2018年3月までの会計年度の行を取りたいのですが、そのようなことは可能でしょうか お願いします select * from table where year*100+month between 201704 and 201803;
じゃだめか？ (year=2017 and month>=4) or (year=2018 and month<=3)でいいだろ IIf(Month<4,Year-1,Year) AS 会計年度
見たいなカラム持ったクエリ定義しとけよ あ、なぜかACCESSだと思ってたw
標準的なSQLならCASEでビューか
まあ、わかるだろ >>179
性能は知らん
where make_date(year, month, 1) between '2017-04-01' and '2018-03-31' 日付を分割してintに入れる糞実装、未だに存在するのかよ どうでもいいけど言うならせめて糞設計だよね
実装てｗ 設計：年・月を保存する
実装：年・月を別カラムにする number(8)に日付いれるのが好きなフレンズもいるな 俺は >>180 支持だなぁ
速度的にも見た目的にも >>180
会計年度中も指定できるので非常に参考になりました
他の方法も含めご教示ありがとうございます 年月を保持する要件で、データに意味を持たない日の情報が含まれるって、良いのでしょうか
バグを産む可能性を秘めてるような >>197
あり得ない月とかを突っ込める方が恐いわ >>198
そんなもんなんぼでもあるわwお前ビビりすぎw バグというより、データベースに事実にない情報を含めるとか違和感が numberでもバグを産む可能性を秘めてるし
どっちのリスクをとるかだけじゃね 年月のみを管理したいっていう場合に
・日付としての正当性は保証されるけど不要な日の情報を持つ
・日付としての正当性は保証されないけど不要な情報を持たない
のどちらがいいか？
って話でしょ
個人的にはデータの正当性の方を重視するかな そして2017/4/1と2017/4/2など
同一年月で複数レコードできてしまうのでした 年月情報をユニーク制約を保持する仕様で日付計算のためにdate使ってたら、皆さんはどう思いますか もうUNIQUE制約のある年月列と日付計算用の列を分けろよ レコードの入力時に日付が何入っていようと、1にしてしまえば良いだけだろう そんなもん制約がなければ全く信用できん
本当に頭が悪いなお前ら >>205
2017/00 とか 2017/13 とか入ってるのとどっちがいい？ 1日の0時になっているか確認するcheck制約つければいい そもそもカラムが年と月別なんだからcheck制約でもMonth>=1 and Month<=12でいいから簡単だろ
プログラムも同様
まさか日付を変換して1日かとかやるとか？
いらない情報付与からして、そんなのうちでは許されないわｗ >>182
後の人間を苦しめるコードをまき散らすのは止めよう 3年分取ってきてと言われて初めて問題に気付くパターンや where fiscal_year(year, month) = 2017
みたいな感じで関数使うかビュー使うほうがロジックが一箇所に集まっていい気がする
パフォーマンス気にするレベルならcalender yearとは別にfiscal yearをデータに持たせるかな あと fiscal_year(date) みたいにオーバーロードしとけば
インターフェースが統一されて使いやすい SQLにオーバーロードあるんですか？どんなRDB？ え、、、普通にあるでしょ
むしろできないDBってどれよ そうなんか、OracleとPostgreSQLで出来てるから普通なのかと・・・ てか、そもそもOracleでも単純なオーバーロードってあったっけ？ このケースはComputed Columnが使えればそれがいいと思うけど
Postgresでは使えないから関数オーバーロードしとくって話
Computed Columnなら使えるDB多いだろ
SQL標準ならView使えって話かもしれんがViewは管理上のオーバーヘッドが高くなる傾向があるから
使わなくてすむケースならなるべく使わないようにしてる >>228
会計年度を必要とするたびに繰り返し同じことをするのでよければね
年度別に集計したい場合とかしんどいし俺はごめんだわ 普通に関数でいいと思うんだが、あえて(関数?)オーバーロードって言ってるのはなんで？ >>230
そういうこと言ってるからいつまでたってもスキルが向上しないんだよ
select case when month < 4 then year - 1 else year end as fiscal_year, sum(hoge)
from foo
group by case when month < 4 then year - 1 else year end つか、いたるところで会計年度を意識するようなシステムなら、会計年度カラムを作っとけばいいよな >>232
where句も入れて何回繰り返すのさ
面倒くさいとは思わないの？
単発の作業ならわからんでもないが会計年度みたいなのは1回じゃすまないだろ
>>231
日付型でデータを持ったテーブルがあったり
日付型に徐々に移行したい場合に同じ名前で扱えるとうれしいから
会計年度って概念をDBに反映させる一つの手段 >>234
> 面倒くさいとは思わないの？
いや、まったく
>>232レベルのクエリなら1０秒もかからずタイプできるだろ >>234
> where句も入れて何回繰り返すのさ
ストアドでも似たようなもんだろ。
select fiscal_year(year, month), sum(hoge) from fuga group by fiscal_year(year, month)
しかもストアド使うと、year, monthにインデックスあっても使われないし。 kantomi@qiita_banned < ストアド！ストアド！ >>235
面倒くさいと思わないならいいんじゃね
>>236
asで名前つけとけばgroup byでは繰り返す必要ないよ
たとえ繰り返しが必要だったとしても概念をその名前で直接扱える状態と
毎回展開しなきゃいけない状態では全く意味が違うんだけどね
インデックスは必要ならはればいい >>233
算出できる情報を別途カラム作って持たせるのは最終手段 >>180 重み付けの定石
>>182 単年度でしか動かないクソ
>>185 意図が分かりやすい
>>217 ビジネスロジックをDBMSに持たせる派とアプリに持たせる派で戦争勃発 >>234
fiscal_year(year, month)って関数自体はまだなにもオーバーロードしてないだろうが。 >>241のどこが馬鹿なのか論理的に説明できないやついるの？ >>240
分解せずに日付型で持っていればよかったって事だな
以降>>188へ戻る 管理上のオーバーヘッドってのがユーザ側の話なのかシステム側の話なのかしらんが
関数オーバーロードができたとして、それがビューより管理が重いとか信じられん
会計年度が必要ならそう言うビュー作れ、で終わりじゃないのか ■ このスレッドは過去ログ倉庫に格納されています
