このスレは
「こういうことをやりたいんだけどSQLでどう書くの?」
「こういうSQLを書いたんだけどうまく動きません><」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ:
SQL質疑応答スレ 9問目
http://pc11.2ch.net/test/read.cgi/db/1252492296/
よくある質問1 (問) ID | DATE | DATA --+----------+----- 1 | 2007-11-11 | aaa 2 | 2007-11-11 | bbb 1 | 2007-11-10 | ccc 3 | 2007-11-12 | ddd 3 | 2007-11-11 | eee 4 | 2007-11-10 | fff 1 | 2007-11-12 | ggg このようなテーブルから、下記のように 1 | 2007-11-12 | ggg 3 | 2007-11-12 | ddd 2 | 2007-11-11 | bbb 4 | 2007-11-10 | fff 各idに対して最新の1件だけ抽出するSQLの書き方を教えてください。 (答) select A.ID, A.DATE, A.DATA from TableName A inner join (select ID, max(DATE) as MAX_DATE from TableName group by ID ) B on A.ID = B.ID and A.DATE = B.MAX_DATE
よくある質問2 (問) key data ---------------- 1 a 1 a 1 b 1 b 1 a 2 b 2 a 2 a というテーブルから key a b -------------------- 1 3 2 2 2 1 というExcelのピボットの様なデータを取得したいのですが、どういうSQLになりますか? a,bというのは固定なので、仮にcというデータがあっても無視して構いません。 (答) SELECT key, SUM(CASE data WHEN 'a' THEN 1 END) AS a, SUM(CASE data WHEN 'b' THEN 1 END) AS b FROM table GROUP BY key ORDER BY key ;
よくある質問3 (問) ID HOGE 01 A 01 B 01 C 02 A 03 B HOGEをAもBもCも持っている、ID:01だけ取り出すにはどうすればよかですか (答1) SELECT id FROM TableName WHERE hoge in ('A','B','C') GROUP BY id HAVING count(DISTINCT hoge) = 3 ; (答2) select * from TableName T1 where not exists (select * from (values 'A', 'B', 'C') T2 (HOGE) where not exists (select * from TableName T3 where T1.ID = T3.ID and T2.HOGE = T3.HOGE ) ) ; ※valuesの部分(Table Value Constructor)はDBMSによって文法がかなり違うので注意
8 :
NAME IS NULL :2010/05/26(水) 05:20:59 ID:bivGLNrt
さぐぇ
質問です。 ・DBMS名とバージョン MySQL5.1.42 ・テーブルデータ(アクセスログのようなもの) ID CREATED --+--------------------- 1 2010/5/10 12:00:00 2 2010/5/10 15:00:00 3 2010/5/11 13:15:00 4 2010/5/14 10:00:00 5 2010/5/15 16:30:00 ・欲しい結果 対象テーブルの日毎のレコード数 ただしグラフ化したいのでゼロの日はゼロと出力したい ・説明 SELECT date_format(created,'%Y/%m/%d') d, count(*) from test_table group by d; とすると、日毎のレコード数が出力できるのは確認済です。 スキマ(5/12&5/13分)をゼロで出力する簡単な方法はないでしょうか。
>>10 カレンダーテーブルを別途用意してOUTER JOIN
てかこれもFAQにしたほうがよかったかも?
12 :
10 :2010/05/26(水) 22:01:56 ID:???
>>11 薄々そんな気はしてたんですが、これが一番ですよね。
ありがとうございました。
まあテーブルでもいいけどそこだけなら当月の日付一覧を検索するサブクエリでいいしょ。
14 :
10 :2010/05/27(木) 22:22:26 ID:PcEBL+w/
>>13 うわ、それ気になります!!
例えば今月の日付一覧を出力するSQLってどう書くんでしょう。
お願いします。
アンケート生成システムを作っているのですが、 アンケート項目数が可変なのでアンケートテーブルを別途作り さらに回答項目数(チェックボックスとか)も可変なので回答項目テーブルも作りました。 アンケートテーブル→アンケート項目テーブル→回答項目テーブル という感じで紐づいてます。 ユーザさんが(会員制ではないためユーザテーブルはありません)回答したログはどこに置こうかと悩んで 結局回答ログテーブルを回答項目テーブルと紐付けて 1.ユーザID(回答時に暫定的に割り振る) 2.回答項目ID 3.テキストエリアの場合の回答用カラム として作成しました。 精一杯考えて結局上記のようになったのですが これだと例えばあるアンケートの回答一覧をユーザごとに出したい場合 アンケートIDから全ての項目IDを割り出してその項目ID全てに紐づいた回答項目IDを出し その回答項目IDから回答テーブルを検索しなくてはならず煩雑です。 他の方法としてはアンケートテーブルに質問1 質問2 質問3...と数十個カラムを足すしか思いつかないのですが 何かいい方法がないでしょうか。
回答項目テーブル、回答テーブルにアンケートID項目をつくる。
ご回答ありがとうございます。そうします。こんな長い質問読んでくださりありがとうございます。 しかも以下間違ってました。読みづらくてごめんなさい。 ×アンケート項目数が可変なのでアンケートテーブルを別途作り ○アンケート項目数が可変なのでアンケート項目テーブルを別途作り
>>18 知ってます。in句が重なるとパフォーマンスも落ちるし
もうちょっと簡潔に出来る方法ないかな…と思ってお聞きしました。
>>19 もしかしてjoinを理解してない?
inなんて使う所が思い浮かばないんだが
つか、自分で考えたテーブルレイアウトとSQL書いてみ
INNER JOINで質問。 2つのテーブルに同じ名前のフィールドがある時、結合するとどうなるか、どこかに解説はないでしょうか。 PHPからMySQLでやってみて、そのフィールド名のデータを取ったら片方のデータは取り出せた。もう片方は取り出せない。 phpMyAdminで試してみたら結果には両方のフィールドが入っています。 好きな方のデータを取り出すにはどうやったら良いのでしょうか。テーブル名.フィールド名だとエラーでした。
まさか列名はテーブル名で修飾できるって知らないのか?
それが何故か上手くいかないんで。 テーブル名付きで指定すると、そんな名前ありませんみたいなエラーが出ちゃう。
24 :
NAME IS NULL :2010/05/28(金) 23:19:29 ID:0+Zm+ajr
ぐだぐだ書いてないでそのSQL晒した方が早いんでね?
うまくいくのでSQL待ちだな
テーブル名に別名つけてるのにその別名で修飾してないと予想
MySQL5.0.77 テーブルAの中の特定のidを持つレコードのうち特定の範囲だけをUPDATEしたい場合、例えば UPDATE A SET flag=1 WHERE id=1 ORDER BY date DESC LIMIT 10,10 とやるとMDB2 Error: syntax errorになります。 UPDATE A SET flag=1 WHERE id=1 ORDER BY date DESC LIMIT 10 なら大丈夫なんですが、オフセットを指定するにはどうすればいいでしょうか。
id以外にキーはないのか?
29 :
NAME IS NULL :2010/06/02(水) 06:50:35 ID:CZR/Bmnf
まったくわからないのです教えてください・・ 1、名前はわかっているが、コードが不明な商品の受注明細を照会するSELECT文を記述せよ。 副照会:商品名からコードを求めるSELECT文 主照会:商品コードから商品の受注明細を照会するSELECT文 2、平均受注個数を超える商品に仕入れ先を照会するSELECT文を記述せよ。 副照会:商品の平均受注個数を求めるSELECT文 主照会:商品コード別に受注個数合計を求め、受注個数合計 > 平均受注個数であれば、仕入れ先を表示 3、1月と2月に\50,000以上の注文があった顧客からの3月受注額を照会するSELECT文を記述せよ。 副照会:4月と5月に受注額合計が共に50000以上であるという条件をみたす顧客を照会 主照会:顧客別に6月の受注額合計を求めるSELECT文。 よろしくお願いいたします・
宿題は自力でやれ
答えられる人がいないスレw
まあ、質問テンプレ全部守れとは言わないが ここはエスパーに回答してもらうスレじゃないんでな
元のテーブル構成まで推測しろとw
学校の宿題は自分でな。
だめだw 1はまず間違いないだろうが2と3は別解がありすぎるw
>>29 1.
select 商品コード from コードテーブル where 商品名 = ?
select 商品受注明細 from 商品受注明細テーブル
join 商品コードテーブル on 商品コード = 商品受注明細テーブル.商品コード
where 商品コードテーブル.商品名 = ?
2.
select 商品平均受注個数 from 商品平均受注個数テーブル
select 商品コード別受注個数合計, 仕入先
from 商品コード別受注個数合計テーブル
where 受注個数合計 > 商品平均受注個数
3.
select 顧客 from 顧客テーブル
where4月の受注額合計 > 500000
and 5月の受注額合計 > 500000
select 6月の顧客別受注額合計
from 顧客別受注額合計テーブル
俺の質問に誰か答えてくれよ
質問どれだよw
MySQL5です。 maindata ----------------------------- main_id | main_name | junle ----------------------------- 1000001 | aaaaaaaaa | 1 1000002 | bbbbbbbbb | 2 1000003 | ccccccccc | 1 1000004 | ddddddddd | 3 1000005 | eeeeeeeee | 1 itemdata -------------------- item_id | item_name -------------------- 9000001 | AAAAAAAAA 9000002 | BBBBBBBBB 9000003 | CCCCCCCCC 9000004 | DDDDDDDDD 9000005 | EEEEEEEEE itemlist ------------------ main_id | item_id ------------------ 1000001 | 9000001 1000001 | 9000004 1000002 | 9000002 1000002 | 9000005 1000004 | 9000002 1000004 | 9000004 1000004 | 9000005 という3つのテーブルがある場合に、maindata.junle='1'を条件に main_id | main_name | itemlists ---------------------------------------------------- 1000001 | aaaaaaaaa | AAAAAAAAA DDDDDDDDD 1000003 | ccccccccc | 1000004 | eeeeeeeee | BBBBBBBBB DDDDDDDDD EEEEEEEEE ---------------------------------------------------- ※itemlistsは空白で連結 という結果を求めたいと思います。 サブクエリを利用して一度に求めることは可能でしょうか? それとも、アプリケーション側でループの際に都度itemlistsを生成する必要がありますでしょうか?
>>39 maindataテーブルとitemdataテーブルをitemlistテーブルを媒介にしてinner join
あとは普通に検索条件をかければいい
41 :
39 :2010/06/03(木) 15:15:55 ID:???
>>40 ありがとうございます。
・・・でも、そのSQLの書き方が判りません…
大変申し訳ありませんが、サンプルをご提示いただけると助かります。
よろしくお願いいたします。
ああ縦を横にしようとしているのか
45 :
39 :2010/06/03(木) 19:50:07 ID:???
>>44 ありがとうございました。
おかげさまで、目的を達成する事ができました。
テーブルの主キーを日付にする時、一般論としてDate型で持つのとNumber(14)やNumber(17??年くらいからのミリ秒・・16桁くらい?)で持つのとで速度に差は出ますか? ? <= xx_date and xx_date < ? のように範囲検索を多用する場合です。 データベースはMySQLかPostgreSQLだと思ってください。
厳密には当然違いはある。一般論としては悩むだけムダ。
【質問テンプレ】 ・DBMS名とバージョン MySQL5.0.77 ・テーブルの構造 CREATE TABLE IF NOT EXISTS table ( id integer NOT NULL auto_increment, value integer NOT NULL, PRIMARY KEY (id), ); ・テーブルデータ ID VALUE --+-------- 1 相沢 2 飯田 3 上井 ・操作 INSERT INTO table(id, value) values (NULL, '遠藤'), (NULL, '岡田'); ・欲しい結果 4 5 ・説明 ID列をauto_incrementにしているのですが、要素を追加したときに そのIDを取得したいと考えています。どのようにしたらいいでしょうか?
放置されてる質問はわかる奴がここにはいないってことか 手頃な金額で依頼したSQL書いてくれるサービスないかな
>>48 INSERTは一件ずつにする。
INSERTしたら
SELECT LAST_INSERT_ID() AS LAST
51 :
NAME IS NULL :2010/06/05(土) 19:47:59 ID:IU+phbd/
【質問テンプレ】 ・DBMS名とバージョン postgresql 8.2.4 ・説明 left join や right join を例えばsybaseでのwhere句の式"*="ように postgresqlでも符号であらわすことはできるのでしょうか? (テンプレに沿ってなくてすみません。)
出来ないんじゃね。メリットないし。 OracleとSQLServer(sybase)だけだろうな。
【質問テンプレ】 ・DBMS名とバージョン Oracle Database 10g 10.2.0.1 ・テーブルデータ なし ・欲しい結果 指定した年月に対応する年月日 ・説明 年月(yyyyMM)を指定し、その年月に対応する年月日を取得したい --------- yyyyMMdd --------- 20100601 20100602 ・ ・ ・ 20100630 できるにはできたのですが、スマートでなかったので・・・ お願いします。 hoge:201006 SELECT TEST FROM ( SELECT hoge || '01' AS TEST FROM DUAL UNION ALL SELECT hoge || '02' AS TEST FROM DUAL UNION ALL ・ ・ ・ SELECT hoge || '31' AS TEST FROM DUAL ) WHERE TEST <= TO_CHAR(LAST_DAY(hoge || '01'),yyyyMMdd) ORDER BY TEST
最大でも31行までのレコードを得られれば良いので、適当なビューを使えば 行数を返す関数を作る必要も無い。 ↓適当に作ってみた SQL> select to_char(TEST,'YYYYMMDD') as TEST from 2 ( select to_date( '201006','YYYYMM' )+rownum-1 as TEST from all_catalog where rownum<=31 ) 3 where trunc(TEST,'MM')=to_date('201006','YYYYMM'); TEST ------------------------ 20100601 20100602 : 20100629 20100630 30行が選択されました。 10gなら SQL> select to_char(TEST,'YYYYMMDD') as TEST from 2 ( select to_date( '201006','YYYYMM' )+level-1 as TEST from dual connect by level<=31 ) 3 where trunc(TEST,'MM')=to_date('201006','YYYYMM'); なんての可能だけど、使い所が限られるかも。
SQLでデータを「作りたがる」人は相変わらずいるもんだね。
まあ、なんでもSQLでやりたくなる気持ちはわからんでもないが...
>>11 も言ってるけど、本気でFAQのテンプレ化したほうがいいかもな
ただ、PostgreSQLみたいに連番生成する関数がもっとほかのDBに広まってもいいとも思う
PostgreSQLは新しい実装に積極的だよね。 果敢にチャレンジし続けるイメージがある。
59 :
53 :2010/06/06(日) 10:07:57 ID:???
60 :
NAME IS NULL :2010/06/06(日) 19:29:43 ID:sC7klrj/
create databaseとcreate schemaのsqlがあるわけですが、 database=schemaじゃないんですか?
違う。
62 :
60 :2010/06/06(日) 19:59:13 ID:sC7klrj/
それじゃ、スキーマって何なんですか?
心と心の間を埋めるものだよ
SQL92 には CREATE DATABASE なんて存在しないな。
65 :
60 :2010/06/06(日) 20:24:09 ID:sC7klrj/
そんじゃ、ユーザー=スキーマってことでおk?
厳密には違うが、ユーザー=スキーマな実装もあるかもしれない
67 :
60 :2010/06/06(日) 20:59:25 ID:sC7klrj/
>>66 それだけわかれば十分だよ
厳密な意味はそのうち勉強することにする
thx
>>62 「テーブルを作った人」くらいの感覚でいいよ。
昔受けた講習で「スキーマとはテーブルなどの所有者のことです」って 臆面もなく説明されたことがあったな
>>65 スキーマはオブジェクトの集合。
Oracleのようにユーザとスキーマが一対一で不可分の場合は
ユーザ=スキーマで差し支えないけど、
SQL Serverのようにユーザとスキーマが別々に定義されていて、
それぞれを結び付けるようなデータベースもある。
【質問テンプレ】
・Oracle10g
TableA
IDa C1
-------------
A1 X
A2 Y
A3 Z
TableB
IDb C2
-------------
B1 S
B2 T
B3 U
TableC(TableAとTableBを紐付けているテーブル)
IDa IDb
-------------
A1 B1
A3 B3
欲しい結果
IDa IDb C1 C2
-----------------
A1 B1 X S
A2 Y
A3 B3 Z U
B2 T
説明
TableA,B,Cの3つのテーブルからTableCに存在しないIDのTableAとTableBのデータも出力したい
>>39 と似てる気もしますが、よろしくお願いします。
ヒント:OUTER JOIN
SELECT IDa, IDb, C1, C2 FROM TableA LEFT OUTER JOIN TableC USING(IDa) FULL OUTER JOIN TableB USING(IDb) ORDER BY IDa
>>73 こんな単純にできたんですね・・・
2時間試行錯誤してた自分ェ・・・
ありがとうございました。
mysqlでsqlの勉強をしているところなのですが、 alter databaseでdbの名前を変えられないんですか?
RENAME DATABASE xdb TO ydb
SQLを仕事で書いてますが、困ってます。 ○テーブルA number nendo gaku riyu ○テーブルB number code テーブルAのgakuの値を更新するSQLを作りたいです。条件は ・テーブルAのriyu <> -1の全てのレコードのgakuに値をセットする。 セットする値は、レコードそれぞれのnumberでテーブルAとテーブルB を結合して、テーブルBのcode=1なら1000,code=2なら2000、code=3なら3000 以下のようなSQLを書きましたがうまくいきません。 update テーブルA a set gaku = ( select case WHEN b.code = 1 1000 WHEN b.code = 2 2000 WHEN b.code = 3 3000 ELSE 0 END gaku from テーブルA a,テーブルB b where a.number = b.number and (a.number,a.nendo) IN ( select number,nendo from テーブルA where riyu <> -1 ) ) where (a.number,a.nendo) IN ( select number,nendo from テーブルA where riyu <> -1 ) DBはOracle10gです。 よろしくお願いします。
UPDATE テーブルA a SET a.gaku = (CASE (SELECT b.code FROM テーブルB b WHERE b.number = a.number) WHEN '1' THEN '1000' WHEN '2' THEN '2000' WHEN '3' THEN '3000' ELSE NULL END) WHERE a.riyu != '-1' 結合する必要ない・・よね? UPDATEは一気にぜんぶ書くんじゃなくて、更新したいレコードを抽出するSQL→SETの中身と順に書くのがええかと。
MYSQLやSQLITEでTIMESTAMP型の比較ってどうやって書くのでしょうか? 条件に現在の時間から3時間以内のデータを取り出す等のようなSQL文を書きたいのですが。 ただ現在の時間からというのなら select * from hoge where time < CURRENT_TIMESTAMP で良さそうなんですが、時間の指定がよくわかりません。
そのDBMSで試してないけど、たとえば '2010/06/09 02:23:38'::timestamp
create databaseでデータベースを作成したわけですが、 rootでデータベースを作成しました rootでshow tables;をすると作ったデータベースが見えるのですが、 一般ユーザからはshow tables;をしてもデータベースが見えません なんでですか? dbms:mysql 5
権限がないからです そしてSQL言語でない、MySQL固有のコマンドの質問はスレ違いです
MySQLでのやり方をお尋ねします。 table1 −−−−−−−−−−− | id1 | column1 | −−−−−−−−−−− | 1 | ああああ1 | | 2 | ああああ2 | −−−−−−−−−−− table2 −−−−−−−−−−−−−−− | id2 | id1 | column2 | −−−−−−−−−−−−−−− | 1 | 2 | かかかか1 | | 2 | 1 | かかかか2 | | 3 | 2 | かかかか3 | −−−−−−−−−−−−−−− id1が一致するものをくっつけたいのですが、table2でid1=2のレコードが2つあるので その場合はid2が大きいほうを選びたいと思っています(つまりid2=3のレコードです)。 −−−−−−−−−−−−−−−−−−−−− | id1 | id2 | column1 | column2 | −−−−−−−−−−−−−−−−−−−−− | 1 | 2 | ああああ1 | かかかか2 | | 2 | 3 | ああああ2 | かかかか3 | −−−−−−−−−−−−−−−−−−−−− CREATE TEMPORARY TABLE tmp SELECT MAX(id2) AS id2 FROM table2 GROUP BY id1; SELECT * FROM table1,table2 WHERE table1.id1=table2.id1 AND table2.id2=tmp.id2; 試行錯誤してこのようになりましたが、一文にすっきりまとめることはできないでしょうか?
88 :
78 :2010/06/09(水) 23:45:21 ID:???
>>79 レス遅れて申し訳ありません。
なるほど、こんな書き方があったんですね。
明日早速試してみます。
ありがとうございました。
>>87 ありがとうございます。
>>4 はテーブルが1つの場合で、
>>86 は2つの場合ですが、
やり方は同じですか?
4行目の「from TableName A」の部分をテーブル2つにする方法が
わからなくて困っています。
from table1 A,table2 C とか
from table1,table2 A とか
やってみましたが、エラーが出ます。
90 :
86、89 :2010/06/10(木) 00:45:34 ID:???
度々すみません。 SELECT A.id1, A.column1, B.* FROM table1 A INNER JOIN (SELECT *,MAX(id2) as max_id2 FROM table2 GROUP BY id1 ) B ON A.id1 = B.id1 とやってみたら −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− | id1 | column1 | id2 | id1 | column2 | max_id2 | −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− | 1 | ああああ1 | 2 | 1 | かかかか2 | 2 | | 2 | ああああ2 | 1 | 2 | かかかか1 | 3 | −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− という結果になってしまいました。 最後の行に「AND B.max_id2 = table2.id2」のような指定をする必要があるんでしょうか。 これを追加すると、Unknown column 'table2.id2' in 'on clause'というエラーになります。
table2に
>>4 を適用して、その結果とtable1を結合すればいいんじゃね?
JOINしたら1テーブルに扱えるでしょ。普通に並べて書けば。
SELECT a.id1, b.id2, a.column1, b.column2, c.max_id2
FROM table1 a
INNER JOIN table2 b ON a.id1 = b.id1
INNER JOIN (SELECT id1, MAX(id2) AS max_id2 FROM table2 GROUP BY id1) c ON b.id1 = c.id1
WHERE b.id2 = c.max_id2
個人的には
>>4 のやり方より↓の方が直感的だと思うんだが。
SELECT a.id1, b.id2, a.column1, b.column2
FROM table1 a INNER JOIN table2 b ON a.id1 = b.id1
WHERE id2 = (SELECT MAX(id2) FROM table2 WHERE id1 = a.id1 GROUP BY id1)
93 :
86、89 :2010/06/10(木) 01:00:03 ID:???
>>91-92 ありがとうございます。
>>92 さんのSQL文はどちらも正しく動きました!
下のほうがスッキリしているので、じっくり見て仕組みを考えてみたいと思います。
こういうのをさっと書けるようになりたいですけど、もっと慣れないと混乱して無理っぽいです。
精進します。
>>92 GROUP BYは要らないだろ。
WHERE b.id2 = (SELECT max(id2) FROM table2 WHERE table2.id1 = a.id1)
教えてください MS Accessで下記の(dept,place)でのくくりで count のtop3を抽出したいです table a dept|place|ext|count aaaa|aaaaa|jar|10 aaaa|aaaaa|com|4 aaaa|aaaaa|scr|20 aaaa|aaaaa|exe|80 aaaa|aaaaa|bat|2 aaaa|bbbbb|ace|10 aaaa|bbbbb|arj|4 aaaa|bbbbb|zip|20 aaaa|bbbbb|rar|80 aaaa|bbbbb|lzh|2 bbbb|aaaaa|mdb|10 bbbb|aaaaa|xls|4 bbbb|aaaaa|ppt|20 bbbb|aaaaa|doc|80 bbbb|aaaaa|txt|2 bbbb|bbbbb|gif|10 bbbb|bbbbb|png|4 bbbb|bbbbb|jpg|20 bbbb|bbbbb|bmp|80 bbbb|bbbbb|tif|2 ↓ aaaa|aaaaa|exe|80 aaaa|aaaaa|scr|20 aaaa|aaaaa|jar|10 aaaa|bbbbb|rar|80 aaaa|bbbbb|zip|20 aaaa|bbbbb|ace|10 bbbb|aaaaa|doc|80 bbbb|aaaaa|ppt|20 bbbb|aaaaa|mdb|10 bbbb|bbbbb|bmp|80 bbbb|bbbbb|jpg|20 bbbb|bbbbb|gif|10 こんな感じ さっぱりわかりません 助けてください
countが重複したときは?
count のtop3 ってのはトータルでのトップ3なのか、 (dept,place)でのくくり ごとのトップ3なのか?
>>97 重複は稀すぎるので、今回考えなくてかまいません
>>98 >>96 の出力例みればわかってもらえると思ったのですが
>(dept,place)でのくくり ごとのトップ3なのか?
こちらです
>>99 >出力例みればわかってもらえると思ったのですが
お前の出力例ではトップ3は全部の組み合わせで同じだから判断できねえだろうが
SELECT * FROM TableA a WHERE count IN (
SELECT TOP 3 count FROM TableA WHERE dept=a.dept AND place=a.place ORDER BY count DESC
)
ORDER BY dept,place,count DESC
>>100 >お前の出力例ではトップ3は全部の組み合わせで同じだから判断できねえだろうが
ここの意味がわからなくてイラッっとしてますが
ものすごく正解のSQLいただいたので、土下座して感謝します
まじでありがとうございました
「重複は考えなくていい」もそうだが、仕様とは何かをまずわかってないんだろうな
104 :
NAME IS NULL :2010/06/10(木) 19:34:40 ID:X6f9EMRL
>イラッっとしてますが www
俺もイラッとしたw もちろん >お前の出力例ではトップ3は全部の組み合わせで同じだから判断できねえだろうが がわからないからだ。
日付順で並べ替えて10件目から20件目までUPDATEしたい場合 どう書けばいいでしょうか? LIMIT 10,20が使えないぽい
UPDATE table1 a SET column1 = 'hoge' WHERE a.主キー IN (table1の日付順の10件目から20件目までの主キーを取得するSELECT文)
table as a table a この2つってどう違うんでしょうか? ついでに、そういう細かいことがわかるサイトも 教えていただけると助かります。
110 :
NAME IS NULL :2010/06/12(土) 00:06:10 ID:nH6zlsmE
今日、OracleでFULL OUTER JOINを使って、ちょっとはまったのですが、 SQL ServerとOracleでSQLの解釈に違いってあるもんでしょうか? SQL Serverの場合 SELECT * FROM 名簿 A FULL OUTER JOIN ON 名簿 B ON A.部活 = 'テニス部' AND B.部活='陸上部' A.名前=B.名前 のような書き方で、名簿テーブルの中でテニス部に所属している人の一覧 と、陸上部に所属している人の一覧を結びつけられると思ったのですが、 どうもOracleの場合は、ON句でWHERE句の条件を書いても絞り込まれない ような印象でした。 最終的には、 SELECT * FROM ( SELECT * FROM 名簿 WHERE 部活='テニス部') A FULL OUTER JOIN (SELECT * FROM 名簿 WHERE 部活='陸上部') B ON A.名前 = B.名前 のような形にして対応しました。 どうもOracleはなんでもかんでもサブクエリにしないとまともな SQLが書けない印象。わたしの勘違いなのかなぁ。
SQLServerはそんなまともじゃないSQLでも動くんだな。
>>109 ありがとうございます。
読んで勉強します!
>>110 ,111
SQL Serverでも上のSQLはそのままじゃ動かないぞ
ONが多いのとANDが抜けてるとしても、そもそもその二つのSQLは同じ結果じゃないと思うが
なにがしたいのかいまいちよくわからん
それぞれのDBMSのバージョンいくつだ?
とりあえず full join バグ あたりでググってみるとなんかあるかもよ
SQLServerで複数列を更新する際に、 同じ条件なんだけど更新する内容が列によって違う場合、 以下のように同じ条件をだらだらと書かずに出来ないもんでしょうか? UPDATE table_a SET cd5 = CASE WHEN fee IN (80, 90) THEN SUBSTRING(cd, 1, 5) ELSE NULL END, cd2 = CASE WHEN fee IN (80, 90) THEN SUBSTRING(cd, 1, 2) ELSE NULL END, varkey = CASE WHEN fee IN (80, 90) THEN post + user ELSE post END ↓こんな感じにまとめられないですかね? UPDATE table_a SET (cd5, cd2, varkey) = CASE WHEN fee IN (80, 90) THEN (SUBSTRING(cd, 1, 5), SUBSTRING(cd, 1, 2), post + user) ELSE (NULL, NULL, post) END
2回投げるのが楽そうだけど、どうしても一回でやりたいのかなぁ
最初は二回に分けてたけど、レコードが数十万件あるから一回で済ましたほうが効率良いかと思いまして・・・
>>114 特定のDBMSに固有のSQLはそのDBMSのスレで聞け
CASE使ってまで1文で更新したい理由がわからん
俺なら素直に
UPDATE table_a SET
cd5=SUBSTRING(cd, 1, 5),
cd2=SUBSTRING(cd, 1, 2),
varkey=post + user
WHERE
fee IN (80, 90)
と
UPDATE table_a SET
cd5=NULL,
cd2=NULL,
varkey=post
WHERE
fee NOT IN (80, 90)
の二つ流すが
>>117 二つ流すのははっきり率悪いだろう・・・。
>>114 そのSQLでしょうがないんじゃね。
119 :
NAME IS NULL :2010/06/12(土) 09:53:54 ID:d+6B+4E8
すなおに二つにします。 皆さんありがとうございました
あ、まだどっちがパフォ良いか試してないんでそれから検討します。
122 :
NAME IS NULL :2010/06/12(土) 11:15:33 ID:1rzl2/wK
Oracleでの「定数との比較を含む外部結合」をACCESS形式に変換したいです。 <TABLE1> <TABLE2> |COLUM1|COLUM2| |COLUM3|COLUM4|COLUM5| ├――――┼――――┤ ├――――┼――――┼――――┤ | あああ | XX | | かかか | XX | AAA | ├――――┼――――┤ ├――――┼――――┼――――┤ | いいい | YY | | ききき | XX | BBB | └――――┴――――┘ ├――――┼――――┼――――┤ | くくく | YY | AAA | └――――┴――――┴――――┘ 上の2つのテーブルに対して以下のSQLを実行すると下の実行結果が得られると思います (今机上ででっちあげたもんだから試してないけど) SELECT TABLE1.COLUM1, TABLE2.COLUM3 FROM TABLE1, TABLE2 WHERE TABLE1.COLUM2=TABLE2.COLUM4(+) AND 'AAA'=TABLE2.COLUM5(+) <実行結果> |COLUM1|COLUM3| ├――――┼――――┤ | あああ | かかか | ├――――┼――――┤ | いいい | | └――――┴――――┘ これを、ACCESS文法に変えたいのですが、「TABLE1.COLUM2=TABLE2.COLUM4(+) AND 'AAA'=TABLE2.COLUM5(+)」 の部分(定数との比較を含む外部結合)のやり方がわかりません。 「TABLE1 LEFT JOIN TABLE2 ON TABLE1.COLUM2=TABLE2.COLUM4 AND TABLE2.COLUM5='AAA'」 では上手くいきませんでした。
・まずACCESS文法って造語が意味分からない ・結果の2行目に"くくく"が表示されないのが意味分からない ・定数との外部結合をする意味がわからない ・行選択のための条件をWHERE句でなくON句で書く意味がわからない
実行結果が(いいい,くくく)じゃ無いのはおかしくないか? なにがしたいのか不明だ。どう上手くいかなかったんだよ 外部結合である必要ないんじゃないのか?
>まずACCESS文法って造語が意味分からない Oracle用のSQL表記とmdbファイル参照する時のSQLと違うでしょ? >結果の2行目に"くくく"が表示されないのが意味分からない TABLE1.COLUM2=TABLE2.COLUMと'AAA'=TABLE2.COLUM5をともに満たすTABLE2の レコードがある場合はCOLUM3を表示し、無い場合は外部結合なんでCOLUM1だけ 表示する、と思うだけど >定数との外部結合をする意味がわからない だって元のSQLがそうなってるんだもん Oracleサーバを参照してたSQLをサーバ負荷軽減のためにローカルにDLした mdbを参照するようにしたいんで、OracleSQLと等価なmdb用SQLに変換したいんだ >行選択のための条件をWHERE句でなくON句で書く意味がわからない でも「'AAA'=TABLE2.COLUM5(+)」(COLUM5が'AAA'に等しいTABLE2のレコードがあれば 実行結果のCOLUM3を表示し、なければCOLUM3はNULLにしてCOLUM1だけ表示する) っていうのはmdb用SQLじゃWHERE句に乗せられないでしょ Oracleなら(+)付ければ一発だけど
TABLE2の3行目は ('くくく', 'YY', 'BBB') の間違いじゃないの? でなければOracleだってその実行結果にはならん。 そうだと仮定して、 select T1.COLUM1, T2.COLUM3 from TABLE1 T1 left outer join (select * from TABLE2 where COLUM5 = 'AAA' ) T2 on T1.COLUM2 = T2.COLUM4 ;
>>126 ありがとうございます
まあぶっちゃけ実行結果はどうでもよくてOracle形式と等価なmdb用SQLさえ
わかればよかったんで適当でした
すいません
ただFROM句に副問合せを含むとDAOからの実行が出来なくなる気が・・・
(似たようなSQLでmdbのSQLビューからは実行できたのにDAOからは出来なかったことあり)
ともあれありがとうございます
参考にします
サンプルが不適切なんで要件が捻じ曲がった可能性があるけど まあ質問者の自業自得だな
つかそれなら本人がダメだったって言ってるSQLで行ける気がするんだがな
alter tableで列にnot null制約を追加したいのですが、 modify 列名 データ型 not null と、わざわざデータ型を入力しないと駄目なのでしょうか? いちいちデータ型を記入するのは面倒なんですが
少なくともOracleではデータ型は書かなくてもいいんだけど DBMSは何なの?
132 :
130 :2010/06/13(日) 21:41:34 ID:???
133 :
NAME IS NULL :2010/06/13(日) 21:42:00 ID:bWmBmCbn
mysqlと見た
>>121 そもそもそのSQLってしょっちゅう流すわけじゃないでしょ?
移行かなんかのバッチだよね。
もしそうなら、高々数十万件の更新の処理なんてバッチとしてはすぐ終わる部類だし、
せっせとCASE文書いて漏れがないかを検証する作業がもったいないと思わんかね。
どう見直すんだろw
まあ、設計はスレ違いだし、提示されてない前提条件もあるのかもしれんが たとえば 初めからCD1を分割しておけば、一部切り出して別コードにするなんて不要 毎回UPDATEしないで、UPDATE後の値を取得できるVIEWをつくるなりトリガで更新する とか
期間の抽出について相談です。 元情報 ID START END ----- ----- ----- 10 2010/06/01 2010/06/12 10 2010/06/13 2010/06/14 10 2010/06/15 NULL 20 2010/06/01 2010/06/11 20 2010/06/13 2010/06/15 ↓ 欲しい情報 ID START END ----- ----- ----- 10 2010/06/01 NULL 20 2010/06/01 2010/06/11 20 2010/06/13 2010/06/15 ・開始日、終了日の期間重複はなし ・IDごとにデータを抽出。抽出単位は連続している期間の最小開始日と最大終了日 ・期間は綺麗に連続(ID=10)することもあれば非連続することもある(ID=20) アドバイス願います。
140 :
139 :2010/06/14(月) 19:13:32 ID:???
追記、DBはOracle 11gです。
こうかな? select "ID", min("ROOT_START"), "END" from (select "ID", connect_by_root "START" as "ROOT_START", "END" from "TableName" where connect_by_isleaf = 1 connect by "ID" = prior "ID" and "START" = prior "END" + 1 ) group by "ID", "END" order by "ID", "END" ;
今気づいたけどサブクエリにしなくてもいいんだな select "ID", min(connect_by_root "START"), "END" from "TableName" where connect_by_isleaf = 1 connect by "ID" = prior "ID" and "START" = prior "END" + 1 group by "ID", "END" order by "ID", "END" ;
○○idという列が主キーになる時が多いですが、 それ以外が主キーになる時ってあんまりないんですか?
設計によりますが、 どの列も主キーになりうります。
どっちかっつーと○○idという列は主キーにするために作ることが多いと思う 別にそうでなければならないわけではない
146 :
143 :2010/06/14(月) 22:37:51 ID:???
147 :
139 :2010/06/14(月) 22:40:28 ID:???
>>142 thx
葉から根を参照するようにすればいいのか・・・。
根を基準に見ていたから気づかなかったよ
すみません SELECT user_id FROM member_m WHERE user_id = {$user_id} AND password = {$password}" これはphpのソースなんですが { } は多分SQL側に渡されると思うのですが、なにか意味があるのでしょうか?
>>148 すれ違いだか、{ } はSQL側へは渡らない。
$a="hoge";echo "a={$a}"; ってして見りゃわかる。
意味とか、その例だけだとプログラマ側の癖みたいなものだが、
詳しくはPHP関係のスレでどーぞ。
>>149 ありがとうこざいます
perlだと ${a} だったので先入観がありましたorz
スレ違い申し訳ありませんでした
2つの列の値の重複を調べたいのですが aとbは重複なしの値にしたいのです。 SELECT COUNT(a, b) FROM test WHERE a = '入力データ1' OR b = '入力データ2' これで結果が 0行 ならデータを追加としたいのです。 aかb片方だけだと上手くいくのですがエラーがでます。 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' b どうしたらいいのでしょうか
>>151 結果が0行か否かなら、
SELECT EXISTS(SELECT * test WHERE a = '入力データ1' OR b = '入力データ2');
でboolが返るが、行数が欲しいのなら、
SELECT COUNT(1) FROM test WHERE a = '入力データ1' OR b = '入力データ2';
でいける。COUNT(1)の他にSUM(1)でもいいが。
153 :
151 :2010/06/16(水) 16:05:45 ID:???
質問です。 Oracle10gです。 部門の親子関係を示す以下のようなテーブルがある。 親部門ID 子部門ID 子部門の区分 1 2 0 1 3 1 3 4 0 3 5 1 5 6 0 7 8 0 7 9 1 9 10 0 ある親部門IDを指定し、その部門以下の階層に含まれる部門のうち区分が0となっているものを取得したい。 上の例だと部門ID=1と指定した場合2,4,6を取得したい。 よろしくお願いします。
>>154 Oracle10gって再起クエリ使えたっけ?
カラム名やテーブル名を以下のように変えて書くけど、
親部門ID=p_id
子部門ID=c_id
子部門区分=c_sec
部門テーブル sector
WITH RECURSIVE Temp(p_id,c_id,c_sec) AS
(SELECT T1.* FROM sector AS T1 WHERE p_id=1
UNION ALL
SELECT T2.* FROM sector AS T2 WHERE T2.p_id = T1.c_id)
SELECT * FROM Temp WHERE c_sec=0;
階層クエリって結構使うのかね。 練習になるからもっとこういう問題出して欲しいわw
再起クエリってなんだよ > 俺 < 再帰クエリだった
>>157 部品展開とか、メールや掲示板のツリーとかぐらいしか思い浮かばん。
以前、隣接リストで入れ子集合やってみたけど、スッキリしなくてめんどいだけだった。
階層構造って、入力する人がどこに入れりゃいいのか迷うのよね。
最近ならタグ(ラベル)的扱いの方が、楽なのかも。
159 :
NAME IS NULL :2010/06/18(金) 16:33:02 ID:DdYe8KWn
再帰SQLは、oracleの11gのR2から使えるそうだ。
11gのR1までは、階層問い合わせを使うしかない!!!
select 子部門ID
from テーブル
where 子部門の区分 = 0
start with 親部門ID= ある親部門ID
connect by prior 子部門ID= 親部門ID
ところで、これは参考になったお。
http://codezine.jp/article/detail/4847
ID | mail ---------------------- というテーブルがあったとして複数のIDのmailを取得するには where ID = '***' OR ID = '***' と OR で繋げていくのでしょうか?
その複数のIDはどこで定義されているんだ? とりあえず ID in ('***','***', ... )とか ID in (select ID from 条件のテーブル) みたいなやり方もあるぞ
ありがとうこざいました! IDは別テーブルです
163 :
NAME IS NULL :2010/06/19(土) 01:03:48 ID:mva1Tcfi
Oracleなんですが下記のSQLが通りません。 問題は★部分で、ここのNVLを外すか、内部結合にすれば 通ります。 しかしNVLで変換は必要です。また外部結合にもできない。 どういうSQLを組み立てたら良いのでしょうか? SELECT main.*, sub.* FROM maintbl main , subtbl sub WHERE main.depertment = 'hogehoge' AND sub.fkey (+) = main.key AND NVL(sub.date,'20990101') (+) <= '20100510'(★)
NVL(sub.date(+),'20990101') <= '20100510' じゃないのか?
>>164 NVL部分にも(+)書かないと内部結合になっちゃうようです。
>>163 >, subtbl sub
ここの部分を
, (SELECT fkey, NVL(sub.date,'20990101') AS date FROM subtbl) sub
にすりゃ通るだろうけど。
167 :
NAME IS NULL :2010/06/19(土) 09:10:02 ID:mva1Tcfi
>>166 こんな感じですかね。
SELECT main.*, sub.*
FROM maintbl main
, (SELECT fkey,NVL(date,'20990101') as date FROM subtbl) sub
WHERE main.depertment = 'hogehoge'
AND sub.fkey (+) = main.key
AND sub.date (+) <= '20100510'
条件付けをしないでサブクエリを書いてしまっても、
速度的には問題ないのですかね?
今回の場合、fkeyにはindexが貼ってあるのですが、
サブクエリにしたテーブルをさらに結合した場合、
indexは有効なんでしょうか?
内部結合になるって言う意味がわからん どういうデータでどういう結果が欲しいんだ?
なんで
>>164 じゃダメなのかワカンネ
それかもっと素直に
sub.fkeyが存在しない場合は結果に含める
sub.fkeyが存在してdateがnullの場合は含めない
sub.fkeyが存在してdateがnullでない場合は'20100510'以下
ってことだとして最後の行を
and (sub.fkey is null or sub.date <= '20100510')
にするとか
パフォーマンス的なものは今どきのDBMSなら意外とうまくやってくれることが多いけど 実際にプラン見てみないと分からんね
>>167 テーブル側のカラムに関数をかましてる時点で(NVL(date,'20990101')のこと)
subtblの全レコードを舐めるのが確定なので、FROM句サブクエリ、WHERE句サブクエリとも速度には期待できない。
>>164 すいません、詳細を見落としていました。
NVL(sub.date(+), ・・・
なんですね。
こんな書き方もあるんですか。
NVL(sub.date,'20990101') (+) <= '20100510'
これは試して内部結合になっちゃったのは確認したのですが
>>164 さんのやり方はまだ試してないです。
>>171 sub.fkeyはPKになっていて、これだけで
一意に定まるんですよ。あとは、そのレコードが有効か
無効かを日付で判断したいだけです。
それでも全件検索になるんですかね?
それじゃ AND sub.fkey (+) = main.key の(+)はどういう意味なんだよw
だから、どういうデータでどういう結果が欲しいんだ ちょっと例だして書いてみろよ 内部結合になるとか意味不明だぞ
てか結合条件と検索条件の区別がついてない? Oracle記法じゃなくてOUTER JOIN ・・ ON ・・ WHEREでやりたい事を書き直してみ。
まあそんな感じか 外部結合した上でその行を全部はじく検索条件書いてその結果だけみて 内部結合になってると言ってると予想 そもそも NULL <= '20100510' は成立しないし '20990101'<= '20100510' も成立しないんだが NVL(sub.date,'20990101')<= '20100510'な理由もわからん sub.date <= '20100510' でいいんじゃないか
177 :
NAME IS NULL :2010/06/20(日) 17:47:01 ID:Pj6iNEOz
質問させてください。 ・DBMS名とバージョン MySQL 5.0.27-1 ・テーブルデータ DATE | DATA ------------+---------- 2010-06-01 | aaa 2010-06-02 | bbb 2010-06-03 | ccc 2010-06-04 | ddd 2010-06-05 | eee 2010-06-06 | fff 2010-06-07 | ggg ・欲しい結果 最新3件を昇順で。 DATE | DATA ------------+---------- 2010-06-05 | eee 2010-06-06 | fff 2010-06-07 | ggg ・説明 select DATE, DATA from TABLE order by DATE desc limit 3; とすれば、最新3件を降順で取得できますが、 できれば降順でなく昇順で最新3件を取得したいです。
も一度ソートすればいいだけじゃないの?
MySQLで未検証だけど、SELECT * FROM (そのSQL) ORDER BY date とか
select * from (select DATE, DATA from TABLE order by DATE desc limit 3) t order by DATE;
181 :
177 :2010/06/20(日) 23:41:17 ID:Pj6iNEOz
>>178-180 ありがとうございます。select結果をselectし直す方法でうまくいきました!ありがとうございます
SELECTやFROMのようなキーワードを大文字、
テーブル名やカラム名を小文字
で書くように学んだけど、
>>180 みたいにその逆の書き方をするのは何か意味があるの?
大文字より小文字のほうが見やすい ならたくさんある方を小文字にすれば、見やすいってことだ
大文字はうるさいよ
>>182 カラム名を大文字表記で定義してるからでしょ。
DATEとか予約語だしなw
学校でSQLの授業とっててこんな問題が出たけど理解不能だったんで助けてくだしあ。 どなたかお願い致します。 全くもって意味が分からなかった\(^o^)/ コンピュータの意味処理ってどういう意味なんですかねぇ・・・ >XMLについて、その概要をコンピュータの意味処理という視点から書いて下さい。 >また、実際にどのような分野で使われていますか? >具体例を一つあげて説明してください。 XMLスレで聞こうと思ったら最終書き込みが2009年とか見てスッ、って帰ってきた。
意味は意味解析や構文解析でぐぐれ。 あとSQL質疑応答とXMLはカスリもしてないw というかXMLとデータベースが全く関係ないのでこんな過疎板の過疎スレでなくてプログラミング板にでもいくといい。w
意味処理って単語は初めて聞いたな。 まあスレチ
191 :
sage :2010/06/24(木) 07:25:52 ID:S+fksH8m
>>189 あとSQL質疑応答とXMLはカスリもしてないw <- 真.
というかXMLとデータベースが全く関係ないので <- 偽.
いや、関係ないだろ。
>>191 データ保持の実装がXMLだろうが実ファイルだろうが関係なかろう。
RDBのスレでOSのファイルシステムについての質問はありなのか?
質問自体はスレチつうより板違いだろうけど、
「XMLとデータベースが全く関係ない」と言い切るのはおかしいだろ。
>>193 データベースに適したFSは? とか、データベース専用FSについて
なんてのはありなんじゃね。
EsTerra
ほとんど関係ない。でいいよ。スレ違いだから、この話題はそろそろ終了な。
・DBMS名とバージョン VB2008でOLEDBを使ってOracle10gに繋いでます。 ・テーブルデータ どの部品にはどの部品が使われてて〜といううような、 部品の構成を表す、下記のような親子関係と必要数のテーブルがあります ------------- OYA KO KAZU ------------- A B 1 A G 1 B C 1 B F 1 C D 1 C E 1 G C 2 G H 1 ツリーで表記すると、 A ├B │├C ││├D ││└E │└F └G ├C │├D │└E └H Aという部品は、BとGの部品で出来ている。 Bという部品はCとFの部品で出来ている〜という感じです。 ・欲しい結果 これを階層のレベルと、それぞれの子の合計数を出したい。 ここでいうBの下のCは1なので、DもEも1だが、 Gの下のCは2なので、DもEもx2されて2になる ---------------- LV KO GOUKEI ---------------- 1 B 1 2 C 1 3 D 1 3 E 1 2 F 1 1 G 1 2 C 2 3 D 2 3 E 2 2 H 1 今は、プログラム側で親から子を見る所をサブルーチンで組み、回帰処理でループさせてるので、 リクエストが多く、あまりにも酷いかなぁ。。。と。 色々サンプルを見てconect byとかよく似てるんで、何かやり方があるのかな?と思い質問してみました。
答えじゃないけど、 >回帰処理でループさせてるので 「再帰」な。
これでGOUKEIの結果をアプリ側で演算する・・・とか?(格好悪っ) select level as LV , KO , sys_connect_by_path(KAZU, '*') as GOUKEI from TableName start with OYA = 'A' connect by prior KO = OYA ;
JDK1.4とオラクル9iです TESTのテーブルから下の結果を得たいのですが可能でしょうか? 1カラム目はIDで 2カラム目は開始日で 3カラム目は終了日です テーブルTEST a,20110403,20110611 b,20110505,20110721 結果 a,201104 a,201105 a,201106 b,201105 b,201106 b,201107
不可能とは言いませんがアプリ側でやったほうがいいです
202 :
200 :2010/06/26(土) 00:56:49 ID:???
>>201 レスありがとうございます。
方法を教えてもらいたいです。
SQLはデータを生成するためのものではありません やっぱりこれFAQに入れようぜ
>>200 Javaでしょ?
20110403、20110611を日付変換して
FOR 201104 TO 201106 EACH 1 MONTH
とかでいいんじゃない
>>202 select distinct ID
, to_char(add_months(to_date(substr(開始日, 1, 6), 'YYYYMM'), level - 1), 'YYYYMM')
from TEST
connect by add_months(to_date(substr(開始日, 1, 6), 'YYYYMM'), level - 1) <= to_date(substr(終了日, 1, 6), 'YYYYMM')
order by 1,2
;
テーブルの規模によっては非常に残念なパフォーマンスになると思う
>>53 ,
>>55 あたりを参考にFAQ書いてみた
(問)
年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい
例:201006を指定したら、以下の結果を得たい
20100601
20100602
・
・
・
20100630
(答)
SQLでは存在しないデータを生成することはできません。
この問いの場合は素直にカレンダーテーブルを用意しましょう。
どうしてもやりたければ以下のような方法もなくはないですが、
これを見て意味が分からない人は使わないほうが良いでしょう。
(意味が分かる人はなおさら使いません。)
select to_char(to_date('201006', 'YYYYMM') + NUM, 'YYYYMMDD')
from (select row_number() over (order by 1) - 1 as NUM from all_catalog)
where to_date('201006', 'YYYYMM') + NUM < add_months(to_date('201006', 'YYYYMM'), 1)
;
select to_char(to_date('201006', 'YYYYMM') + level - 1, 'YYYYMMDD')
from dual
connect by to_date('201006', 'YYYYMM') + level - 1 < add_months(to_date('201006', 'YYYYMM'), 1)
;
※後者はOracle限定です。他のDBMSの場合はconnect byの代わりに
再帰問合せを使えばできるかもしれません。
初心者なんですけど、エラーを調べるコマンドってありますか?
マニュアル見ればいいじゃない
>>206 後半のどうしてもってとこのSQLいらなくね?
どうしてもやりたいなら再帰SQLでできなくもないです
ぐらいでいいと思う
>後者はOracle限定
前者もall_catalogが存在して必要な行数以上の行をもってないと無理
実質ORACLE専用だろ
ダミーテーブルと結合させるのはSQLの手段としては間違ってると思う
いらんよね。逆に、
>>206 の通りにやったけどできません、とか
MySQLで同じことするにはどうすればいいですか?なんて
質問が出そう。
ちょっと修正した (問) 年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい 例:201006を指定したら、以下の結果を得たい 20100601 20100602 ・ ・ ・ 20100630 (答) SQLでは存在しないデータを生成することはできません。 この問いの場合は素直にカレンダーテーブルを用意しましょう。 どうしてもやりたければ以下のような方法もなくはないですが、 こんなSQLを書いているとセンスを疑われます。 やめておくことを強くお奨めします。 (PostgreSQLのgenerate_series()関数なら辛うじてセーフかもしれませんが 微妙なところでしょう。) select to_char(to_date('201006', 'YYYYMM') + NUM, 'YYYYMMDD') from (select row_number() over (order by 1) - 1 as NUM from all_catalog) where to_date('201006', 'YYYYMM') + NUM < add_months(to_date('201006', 'YYYYMM'), 1) ; select to_char(to_date('201006', 'YYYYMM') + level - 1, 'YYYYMMDD') from dual connect by to_date('201006', 'YYYYMM') + level - 1 < add_months(to_date('201006', 'YYYYMM'), 1) ; ※いずれの例もOracleの場合です。 他のDBMSの場合でもどのみちお奨めしません。
俺も別に要らんと思うが、WITH RECURSIVEなら! と思ってみたがLIMIT付けないと無限ループする。
LIMITクエリって相変わらず処理系依存? 以下、PostgreSQL用
WITH RECURSIVE Temp(datedata) AS
(SELECT to_timestamp('201006','YYYYMM')
UNION ALL
SELECT datedata+interval'1 day' FROM Temp)
SELECT to_char(datedata,'YYYYMMDD') FROM Temp
LIMIT EXTRACT(day FROM to_timestamp('201006','YYYYMM')+interval'1 month -1 day');
>>211 にも書かれてあるが、PostgreSQLならgenerate_seriesを使った方が楽。
ま、こんなのも出来るって参考程度に。
LIMITじゃなくて普通にWHEREじゃダメなのか?
214 :
212 :2010/06/26(土) 18:05:55 ID:???
>>213 可能だった。
WITH RECURSIVE Temp(datedata) AS
(SELECT to_timestamp('201006','YYYYMM')
UNION ALL
SELECT datedata+interval'1 day' FROM Temp WHERE datedata <to_timestamp('201006','YYYYMM')+interval'1 month -1 day')
SELECT to_char(datedata,'YYYYMMDD') FROM Temp
そうか、最後の行にWHERE句ではなくて、再帰される分のところに入れりゃいいのか。
>>200 は9iだそうなのでWITH RECURSIVEがつかるのかな、
でもその質問でWITH RECURSIVEをつかった解はまだわからない。
215 :
212 :2010/06/26(土) 18:17:40 ID:???
でけた。 WITH RECURSIVE Temp(mark,startdate,enddate) AS (SELECT mark,startdate,enddate FROM testtable UNION ALL SELECT mark,(startdate+interval'1 month')::date,enddate FROM Temp WHERE date_trunc('month',startdate)<date_trunc('month',enddate)) SELECT mark,to_char(startdate,'YYYYMM') FROM Temp ORDER BY mark; date_truncってひょっとしてPostgreSQL依存な関数をつかっているが、 Oracleにも似たような関数あるっしょ。
216 :
212 :2010/06/26(土) 18:24:38 ID:???
> (startdate+interval'1 month')::date PostgreSQL表記がここにも残っていた。 DATE型+INTERVAL型=TIMESTAMP型になるので、DATE型にキャストしている。 startdateがTIMESTAMP型ならキャスト不要。 他なら、適当にキャスト演算子or関数つかってくれ。 連投スマソ
Oracleでwithを使った再帰問合せが書けるのは11g(R2?)からかな recursiveキーワードはつけない(というかキーワードじゃない) 日付の演算も違うけど面倒臭いんで書かない
218 :
197 :2010/06/26(土) 22:42:12 ID:???
>>198 再帰デスね、、寝ぼけてました。。
>>199 ありがとうございます。
参考にさせてもらいますー。
219 :
212 :2010/06/26(土) 23:36:59 ID:???
>>217 わりぃ、上で出ていたけど、アルファベット表記のところしか覚えてなかった。
g->h->i でiの方が新しいと思ったが、9iと11g-R2じゃ11g-R2の方が新しいんだね。
ところで、WITH RECURSIVEが使えるOracleで比較的簡単そうな以下のようなSQLでもダメなの?
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100)
SELECT sum(n) FROM t;
--PostgreSQLのマニュアルより
>>219 ダメなの?って11gなら通るよ。
ただしrecursiveキーワードは要らないのと
valuesを使ったTable Value Constructorは使えない。
WITH t(n) AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT n+1 FROM t WHERE n < 100)
SELECT sum(n) FROM t;
221 :
212 :2010/06/27(日) 00:14:52 ID:???
>>220 サンクス。
なるほど、そういうことなのね。
222 :
211 :2010/06/27(日) 21:29:59 ID:???
さらに修正した (ダミーテーブルを使うのはいくらなんでもアレなのでボツ、 代わりに再帰問合せにした) (問) 年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい 例:201006を指定したら、以下の結果を得たい 20100601 20100602 ・ ・ ・ 20100630 (答) SQLでは存在しないデータを生成することはできません。 この問いの場合は素直にカレンダーテーブルを用意しましょう。 どうしてもやりたければ以下のような方法もなくはないですが、 再帰問合せ/階層問合せの本来の使い方ではありません。 やめておくことを強くお奨めします。 (PostgreSQLのgenerate_series()関数なら辛うじてセーフかもしれませんが 賛否の分かれるところでしょう。) with TEMP (NUM) as ( select 1 from dual union all select NUM + 1 from TEMP where NUM < 31 ) select to_char(to_date('201006', 'YYYYMM') + NUM - 1, 'YYYYMMDD') from TEMP where to_date('201006', 'YYYYMM') + NUM - 1 < add_months(to_date('201006', 'YYYYMM'), 1) ; select to_char(to_date('201006', 'YYYYMM') + level - 1, 'YYYYMMDD') from dual connect by to_date('201006', 'YYYYMM') + level - 1 < add_months(to_date('201006', 'YYYYMM'), 1) ; ※いずれの例もOracleの場合です。(前者は11gR2以降) ※再帰問合せをサポートするDBMSなら前者を適当に改変すれば動きますが どのみちお奨めしません。
223 :
NAME IS NULL :2010/06/27(日) 21:51:11 ID:jCqDQodk
これでいいじゃん (問) 年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい 例:201006を指定したら、以下の結果を得たい 20100601 20100602 ・ ・ ・ 20100630
224 :
NAME IS NULL :2010/06/27(日) 21:53:35 ID:jCqDQodk
コピペ失敗した (問) 年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい 例:201006を指定したら、以下の結果を得たい 20100601 20100602 ・ ・ ・ 20100630 (答) SQLでは存在しないデータを生成することはできません。 この問いの場合は素直にカレンダーテーブルを用意しましょう。 どうしてもやりたければ、再帰問合せ/階層問合せを 利用すれば可能ですが、本来の使い方ではありません。 やめておいたほうがよいでしょう。 (PostgreSQLのgenerate_series()関数なら辛うじてセーフかもしれませんが 賛否の分かれるところでしょう。)
225 :
222 :2010/06/27(日) 22:13:38 ID:???
ちなみにSQLサンプルを残した理由は ・可能であるものをわざわざ隠す必要もないので ・「再帰問合せを使って書くってどうやるんですか?」と聞かれるのが一番ウザいので ・FAQに再帰問合せのサンプルがないので (そしてわざわざ再帰問合せのFAQを捏造するのもアレなので) ・せっかく書いたので ←実は最大の理由
withはともかく、connect byみたいな特定のDBMSに強く依存するものをテンプレに乗せるのはどうなんだ
227 :
222 :2010/06/27(日) 22:40:11 ID:???
確かにそれは一理あるね connect byのほうはなくてもいいかも そういう意味では日付演算もDBMSにかなり依存するんで 問いの選び方としてイマイチっちゃイマイチなんだけど 実際に多い問いなんでこれはしょうがないな
俺的にはテンプレならありなんだけどな。> 処理系依存
>>4 も処理系によってはもっと簡素に書けるわけだし、ひとつのDBMSだけじゃなくて
汎用、Oracle、MS-SQL、PostgreSQL、MySQL.... って並んでると便利かも。
ただ、テンプレが太ってしまうので、どこかWikiなんぞに書ければいいが。
質問です。 今から11gのbronzeやりはじめようこてこての初心者なんですが 11gインスコしてもSQL plusが起動しません。 実行したら一瞬SQL plusの画面がふわっと出てすぐ消えちゃいます。 補足 ・インストールは警告無視してました。 ・OSはVistaのビジネス(対応してる) っス。よろしくおながいするッス
231 :
NAME IS NULL :2010/06/29(火) 07:20:08 ID:gwfYytti
tbl1にnum1,num2,id1,id2,id3 tbl2にid,name,ssというフィールドがあるとして SELECT name, num1, num2, id1, id2, id3, time FROM tbl1, tbl2 WHERE num1 = 1 AND id1 = ( SELECT id FROM tbl2 WHERE ss = "monga") 上記のようなSQLを 特定のフィールドが他の行と重複してたら抜かすというのはどうやればいいでしょうか? 単にdistinctつけるだけだとどれかひとつでも重複していなかったら抽出されますよね。 id1,id2,id3が他のレコードのid1,id2,id3と重複してたら 検索結果に入れないみたいな感じにしたいのですが。 MYSQLです。
>>231 まずtbl1とtbl2をクロス結合してるところで要件ミスか実装ミスの可能性大。
>>229 コマンドプロンプトを起動してからsqlplusを叩いてエラーメッセージを見れ。
あと仮想マシンでいいからLinuxいれろ。
233 :
NAME IS NULL :2010/06/29(火) 13:34:11 ID:gwfYytti
>>232 レスありがとうございます。
えと、AND id1 = ( SELECT id FROM tbl2 WHERE ss = "monga")の部分で内部結合のつもりなんですが
tbl1.id1 = tbl2.id
もつけないといけないんでしょうか。
あと、要件ミス、実装ミスってことは、そういう重複の取り除き方はありえないってことでしょうか?
234 :
NAME IS NULL :2010/06/29(火) 13:46:03 ID:gwfYytti
すいませんなんか難しく考えていましたがGROUP BYでいいんですね・・・
できたようでよかった。 その質問をするときは重複判定しないカラムの扱いも書いとかないと困るよ。
カレンダー作るのってアンチ多いのね。 カレンダーテーブルを作るとしても大して変わらないだろ。
237 :
NAME IS NULL :2010/06/30(水) 14:58:43 ID:vqtVeRmY
すいません、初心者なのですが教えてください。 sqlplusにて SQL>!ls と打つと「ls」コマンドが実行されると思うのですが、 自宅のPCに、書籍の付録のオラクルをインストールして実行しても「SP2-0042:不明なコマンドです」 と言われてしまいます。 「!」を認識させるにはどのようにすれば良いのでしょうか?
>>237 スレ違いだが。
! は、Unix(Linux)の場合のみ使える。
Windowsの場合は、$
どちらも、hostコマンドの省略形。
おまけにwinだったら、ふつうはlsは入ってないわな。
Powershell経由で起動してたら、lsあるな。
vistaからls使えた気がする
すみません質問です。 Accessのクエリで SELECT CASE pref_name WHEN '徳島' THEN '四国' WHEN '香川' THEN '四国' WHEN '愛媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福岡' THEN '九州' WHEN '佐賀' THEN '九州' WHEN '長崎' THEN '九州' ELSE 'その他' END AS district, SUM(population) FROM PopTbl GROUP BY CASE pref_name WHEN '徳島' THEN '四国' WHEN '香川' THEN '四国' WHEN '愛媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福岡' THEN '九州' WHEN '佐賀' THEN '九州' WHEN '長崎' THEN '九州' ELSE 'その他' END; というSQLを実行したいのですが、構文エラーとなってしまいます。 SQLの達人という参考書からそのままコピッただけのなので、 普通に考えてエラーが出るはずがないのですが、どうでしょうか?? 助けてくださいお願いいたします。
AccessのSQLでは、Case Whenが使えない。IIfとかで代用しろ
>>244 そうなんですかぁ。。。
素早い回答どうも有り難うございました!
主キーってインデックスが自動的に作成されるって書いてるけど、実際はインデックスを明示して作らない速くならない様なんだけど本当のところはどうなの?
DB次第。SQL には関係ないからスレ違いだな。
248 :
NAME IS NULL :2010/07/05(月) 22:22:34 ID:03f+1PMv
TABLE test id | data ---+------ 100 | AAA TABLE hoge id | value ---+------ 100 | 10 100 | 20 TABLE 100 id |val1| val2 ---+---+---- 100 | 20 | 40 100 | 30 | 60 SELECT a.id, test.data, a.cnt + b.cnt + c.cnt FROM (SELECT id, SUM( aa.val1 ) cnt FROM (SELECT id, val1 FROM `100`)aa)a, (SELECT id, SUM( bb.val2 ) cnt FROM (SELECT id, val2 FROM `100`)bb)b, (SELECT id, SUM( value ) cnt FROM hoge WHERE id = 100)c,test WHERE a.id = c.id AND test.id = 100 欲しい結果 id data a.cnt + b.cnt + c.cnt ---------------------------- 100 AAA 180 やむなくMySQLのバージョンが5.0.77-logから5.1.22-rcに変更することになり、 5.0.77-logで動作していた上記のSQLを実行すると #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause と怒られてしまいます。。。 今も色々試しているのですがGROUP BYをうまく使えません。 申し訳ないのですが教えて頂けませんか><
idがuniqueじゃないのって初めて見た・・・
>>248 test.id = hoge.id = `100`.id で連結してvalueとval1,val2を全て合計するってことでいいのかな?
SELECT test.id,test.data,T1.cnt+T2.cnt FROM test JOIN
(SELECT id,SUM(val1)+SUM(val2) AS cnt FROM `100` GROUP BY id)AS T1 ON test.id = T1.id JOIN
(SELECT id,SUM(value) AS cnt FROM hoge GROUP BY id )AS T2 ON test.id = T2.id WHERE test.id = 100;
テーブル名に`100`ってしているから、実使用とは意味が違うSQLになっているかもしれん。
>(SELECT id, SUM( aa.val1 ) cnt FROM (SELECT id, val1 FROM `100`)aa)a, ↑ ここの部分のサブクエリはなんか意味あるのか?
どうやったらこんなテーブル構造になるのかが不思議だ
>>251 ありがとうございます><
欲しい結果を得ることが出来ました!
>>252 まったくその通りですね、大切な部分を省略してしまいました。。。
実際は日付で降順ソートして上位100件の合計値が欲しかったのです。
SELECT test.id, test.date, T1.cnt + T2.cnt
FROM test JOIN
(SELECT id, SUM( VC.val1 ) + SUM( VC.val2 ) AS cnt FROM (SELECT id, val1, val2 FROM `100` ORDER BY date DESC LIMIT 100) AS VC GROUP BY id) AS T1 ON test.id = T1.id JOIN
(SELECT blog_id, SUM( value ) AS cnt FROM hoge WHERE id = 100 GROUP BY id) AS T2 ON test.id = T2.id
WHERE test.id = 100;
>>253 恥ずかしいです><
でも実際はもう少しちゃんとした構造だと・・・思いますよ!w
皆様ありがとうございました。
255 :
NAME IS NULL :2010/07/06(火) 16:10:21 ID:wdn3FAny
同一テーブルで、自分自身を除く前回の履歴を結合したいです SQLServer2005 設備 X 生産履歴 履歴ID 設備 製造ロット ロット枝番 状態 10 X 002 2 生産中 09 X 002 1 完了 08 X 001 1 完了 設備と生産履歴から設備稼働状況を表示 SELECT * FROM 設備 LEFT OUTER JOIN 生産履歴 ON 設備.設備=生産履歴.設備 AND 生産履歴.状態='生産中' 設備稼働状況 ※これに前回ロットを表示したい。 設備 製造ロット ロット枝番 前回ロット X 002 2 001 製造ロット002が完了したら 設備 製造ロット ロット枝番 前回ロット X NULL NULL 002 となる。 LEFT OUTER JOIN SELECT MAX(製造ロット) AS MAX製造ロット FROM 生産履歴 生産履歴前回 WHERE 製造ロット<>生産履歴.製造ロット※2 GROUP BY 設備 ※2で引っかかる。 どなたか回答よろしくお願いします。
>>255 「ひとつの設備に対して生産中の履歴は最大でひとつ」ということにして
SELECT *,(SELECT MAX(製造ロット) FROM 生産履歴 WHERE 設備=T1.設備 AND COALESCE(ロット<T2.ロット,TRUE) AS 前回ロット
FROM 設備 AS T1
LEFT JOIN (SELECT * FROM 生産履歴 WHERE 状態='生産中') AS T2 ON T1.設備=T2.設備;
で、いけるかな? SQL鯖で動くかどうかはしらね。
>>255 別解
SELECT
E.設備,
(select MAX(製造ロット) from 生産履歴 group by 設備) AS 製造ロット
,P.ロット枝番,
(MAX(p.ロット枝番) over(partition by E.設備,P.製造ロット)) -1 AS 前回ロット番号
FROM 設備 E LEFT OUTER JOIN 生産履歴 P ON E.設備= P.設備
AND P.状態='生産中
ただしMSSQL2008で。
2005でover句が使えるかは知らん。
>>255 前回ロットの定義があいまいだが
生産中でない製造ロットの最大値でいいのか?
それなら
SELECT *,
(SELECT MAX(製造ロット) from 生産履歴 where 設備=T.設備 and 製造ロット<>T.製造ロット) as 前回ロット
FROM 設備
LEFT OUTER JOIN 生産履歴 T
ON 設備.設備=T.設備
AND T.状態='生産中'
でいいんじゃね?
前回ロットは履歴IDを見てとか言うなら2行目を適当に
259 :
259 :2010/07/07(水) 04:14:54 ID:???
ああ、違う SELECT *, (SELECT MAX(製造ロット) from 生産履歴 where 設備=S.設備 and 状態<>'生産中') as 前回ロット FROM 設備 S LEFT OUTER JOIN 生産履歴 T ON S.設備=T.設備 AND T.状態='生産中' こっちで
260 :
259 :2010/07/07(水) 04:21:15 ID:???
>>259 の名前は258で...
TV見ながらやるとミスだらけだなorz
>>259 それは枝番違いが引っかかるから明確にNG。
俺も考えたけど
>>256 で正解と思う。
これの面倒なのは最新が生産中の時と完了の時とで前回ロットの取り方が違うことだよな
・・COALESCEでの回避は、こんなとこにロジック埋めこんでいいのか悩む。
IF文で数値以外っていう条件にしたいんだけど、どう指定するのが良いの?
IF文って?w
それは数字のみで構成された文字列かどうかの判断をしたいということ?
>>263 IF文知らないの?スレ違い感は否めないけど。
266 :
263 :2010/07/07(水) 21:03:04 ID:???
あースマンPL/SQLってここ駄目なの? 聞きたかったのは、たとえばだけどselectで取ってきたものを数値か文字列かで 結果を分岐させたいんだけど、条件をどうすれば数値か文字列か判断できるのか わからんかったんだわ。
>>264 俺は263じゃないが、ここSQLスレだしな
俺はSQLのIF文はしらない
>>265 ためして無いけど、COALESCE使えて真偽値返すDBなら動くような気はする
トリッキーだけどよく考えたコードだと思う
と思って良く見たら、ASの前に閉じかっこが1個ぬけてるな
まあ、なんにせよ、JOINしてるテーブルに別名つけて、セレクトリストに
カッコつきで単一行返す相関クエリ書けばできるんじゃないかと
COALESCE(ロット<T2.ロット,TRUE) のとこを ロット<COALESCE(T2.ロット,'DUMMY') とかすりゃどこでも動く カッコが足りないから動かない(キリッ じゃ勉強不足だぞ
269 :
256 :2010/07/07(水) 22:11:31 ID:???
ほんとだ、括弧が足りない...orz
COALESCEもCOALESCE(製造ロット<T2.製造ロット,TRUE)だったな。
>>267 ここのスレにいると、結構トリッキーなSQLでないと解けない場合が多い気がする。
COALESCEをこんな使い方したのも初めてだしw
頭の体操ってことで、結構好きなんだけどね。
>>268 ダミー文字列とか99999みたいな数値を使うのが嫌でCOALESCE(ロット<T2.ロット,TRUE)を思いついたが、
CASE文とかDB依存関数なんてのもありってことで。
SQLServerなら
>>268 のいう
ロット < COALESCE(T2.ロット,'DUMMY')が正解。TRUEなんてかけるの?
271 :
256 :2010/07/08(木) 00:05:22 ID:???
少なくともPostgreSQLなら書ける。 しかし、後ろのTRUEで引っかかるのか? 手前の条件の方がダメなのかと思ってたわ。 あえて括弧でくくって、COALESCE((ロット<T2.ロット),TRUE)でも無理なのかな? あとは、CASE文を試してみるとか、 CASE WHEN T2.ロット IS NULL THEN TRUE WHEN ロット<T2.ロット THEN TRUE ELSE FALSE END
>>266 正確に書かないからだよ
PL/SQLだってことすら書いてないからな
DBMSはORACLEで
selectで取ってきたものってのが
VARCHAR2型であったとして(仮定が多いな)
if replace(translate(data,'1234567890','0000000000'),'0') is null then
数値の処理
else
文字の処理
end if;
もっとスマートな方法があるかもしれんが
こんなところでいかが?
>>270-271 それが動く前提は、
>>267 で言う真偽値返すってとこだな
PostgreSQLは論理値データ型ってのがある。TRUEはこの型として判定されてるはず
>>266 ,272
オラクルでPL/SQLなら、TO_NUMBERしてエラー出るかどうか見ればいいんじゃね
まあ、PL/SQLはオラクル固有なんで、オラクル関係のスレで聞けってことかと
274 :
NAME IS NULL :2010/07/09(金) 00:54:55 ID:BIBrIfiz
・DBMS名とバージョン postgres sql ・テーブルデータ 2010/06/10,20:30 と格納されている、varchar型列 ・欲しい結果 [2010/06/10],20:30 とUPDATEするバッチを作成する。 ・説明 上記処理を実行するバッチ処理をフルスクラッチで作成します。 UPDATE文の書き方は判るのですが、どういう設計にしようか迷っています。 自分が最速で実装するなら、.net使って書くのですが、いかんせん仕事でして 今後の保守性を考えるとExcelマクロでSQLファイルを生成してデータバッチを 出力するようにした方がいいのでしょうか? 有識者に聞けということなんでしょうけど、いかんせん一人だけの仕事でして 実装方法で迷っています。
>>274 スレ違い。PostgreSQLスレで聞いても違うっぽい気がする。
保守性なんて、運用する会社や担当者のレベルで考え方違うだろうし、
そもそもなんで、時刻をvarcher型で入れてるの? って話になる。
素直にtimestamp型にして取り出すとき摘便に書式を変えればいい。
ちなみに、挙げられたデータと欲しい結果だけ見れば、UPDATE一文で済んで不変。
そこに仕事とか、保守性云々がどう絡むのか、うまくつかめません。
276 :
NAME IS NULL :2010/07/09(金) 16:16:58 ID:01dNrJEc
MySQL5を使用しています。 以下のようなテーブルがあります。 itemテーブル item_id name color 1 商品1 3 2 商品2 12 3 商品3 7 colorテーブル color_id name 1 赤 2 青 4 緑 8 黒 item.colorの部分は、ビット演算の概念を使って 3なら赤+青 12なら緑+黒 7なら赤+青+緑 の色を持っていることを意味します。 これを結合の段階で、以下のように色名を取得することは可能なのでしょうか? 1 商品1 赤 1 商品1 青 2 商品2 緑 2 商品2 黒 3 商品3 赤 3 商品3 青 3 商品3 緑
>>276 MySQL5で試せてないけど、ビット演算の概念を使って取り出せばいいと思うんだ。
select item_id, item.name, color.name
from item cross join color
where item.color & color_id = color_id
order by item_id, color_id;
278 :
276 :2010/07/09(金) 18:36:38 ID:01dNrJEc
>>277 結合にビット演算子を使うのは頭にありませんでした。
MySQL5で見事に取得できました。
ありがとうございました。
・DBMS名とバージョン Oracle Database 10g 10.2.0. SQLの実行計画で、 select count(1) from TBL_A とした場合はSORT (AGGREGATE)が表示されるのに、 select col_a, col_b from TBL_A などの場合にはSORT (AGGREGATE)が表示されないのはなぜでしょうか?
前者は集約関数を使っているから、後者は使っていないから むしろ何が疑問なのか分からない
>>279 Oracle10gのプランナがバカ! とは言わないけど、洗練度に欠けるのかな。
以下、勝手な推測だが、
集約関数の場合、GROUP BYと併用する場合が多くて、
GROUP BYの実行には一旦ソートしてからまとめるようになっている。
だから、最終的には必要ないのだが、集約関数を見つけた時点で、
ソートしちゃった! ってところかと。
SORT AGGREGATE と表示されても、実際は AGGREGATE しているだけで SORT はしてないと思う。 ちなみに GROUP BY の場合は SORT GROUP BY とか HASH GROUP BY とか表示される。
283 :
NAME IS NULL :2010/07/18(日) 10:55:54 ID:wToLq7/Q
MYSQL5で テーブルA create table A( id int , name_id int, word varchar(128) , primary key ( id ) , index ( name_id ) ) テーブルB create table B( id int, cid1 int, cid2 int , cid3 int , primary key ( id ) ) テーブルC create table C( id int, name varchar(128) , primary key ( id ) , unique ( name ) ) こんな感じのテーブルを作成しました。テーブルAのname_idはテーブルBのidと、テーブルCのidはテーブルBのcid1〜cid3と関連します。 テーブルAのname_id,wordとテーブルBのcd1〜cd3をテーブルCのnameで置き換えた値で取り出したいのですが、どう書けばいいでしょうか?
select A.name_id, A.word, C1.name, C2.name, C3.name from A inner join B on A.name_id = B.id inner join C as C1 on B.cid1 = C1.id inner join C as C2 on B.cid2 = C2.id inner join C as C3 on B.cid3 = C3.id ;
285 :
NAME IS NULL :2010/07/18(日) 11:14:27 ID:wToLq7/Q
>>284 おお。。。なるほど。そう書くんですね。ありがとうございます!
今まで正規化考えないでひとつのテーブルにまとめてたのを、
正規化勉強し始めてテーブル分けたものの簡単な取り出し方しかわかりませんでした。
いや正規化できてねぇしw
287 :
NAME IS NULL :2010/07/19(月) 01:05:28 ID:hKSUB/U7
えっ
288 :
NAME IS NULL :2010/07/19(月) 15:56:24 ID:CQO73cD2
えっ
なにそれこわい
同じテーブルの同じ項目でで内部結合や外部結合する必要性(意味)はありますか?
ある
>>292 んなことはスキーマ定義からだけでは判断できん。
cid1〜3がどういう意味かによって正規形と言える場合もあるし 言えない場合もある
C.id → B.id1 C.id → B.id2 C.id → B.id3 なんだから繰り返し項目。非正規化。
質問です。SQL内に同じ計算式が2回出るときにすっきりする方法ありませんか? 例えば、現在値/その最大値というカラムがあり、現在値を増やすようなケースで update TABLE set 現在値 = IF( 現在値+複雑な計算 => 最大値, 最大値, 現在値+複雑な計算 ) とすれば実現はできるのですが、この複雑な計算を2回書きたくないです(バグの元だし)。 尚、DBMSはMySQLです。 また、可読性よりはパフォーマンスを重視したいので、 上記よりもパフォーマンスで劣る方法しか無いようならあきらめます。
297 :
296 :2010/07/21(水) 00:04:41 ID:???
=> は >= に訂正。なんという素人ミス…。 なんかNVLとかIFNULL的な、SETMAX(複雑な計算, 最大値)とかいう関数があったら楽なんだけど。
>>295 一般的に正規化されていない可能性が高いが
特殊な要件がある可能性もあるんで、今でている条件だけでは判断できない
ということを、
>>293-294 が言ってくれてるんだが理解できないのか?
>>296 当然その複雑な計算ってのは行ごとに値が変わるんだよな
可読性もとめるならビュー作っとけばいいんじゃね
パフォーマンスは、計算列に対する実態を保持するようなビュー(またはテーブル)
作れるならともかく、普通のビューなら落ちると思うけど
299 :
296 :2010/07/21(水) 01:09:36 ID:???
行ごとに値が違います。さらに言うと最大値も固定じゃないので、 カラムの制約な何かで最大値を制御できたとしても(出来るのか知らんけど)そういうのもNG。 やっぱ選択肢の一つはビューですよね…。もっとお手軽なのないかな。
>>295 「繰り返しグループ」について正しく理解していないようだから詳しく。
属性の集まりが「繰り返しグループ」と呼ばれるのは一般的にその属性の
集まりがタプルではなく、単に何かの値の集合を意味する時。
この場合は「個々の属性値はatomic」という第一正規形の定義から間接的
に外れてしまうためこれは排除すべしとされている(歴史的にはCoddによる
元の第一正規形の定義に後付されたルール)。
例えばcid1〜cid3が「新郎、新婦、仲人」という「人名のタプル」だったり
「出発地、経由地、到着地」という「地名のタプル」だったりする場合は、
cid1〜cid3は繰り返しグループではなく、第一正規形を満たす。
cid1〜cid3が「趣味1、趣味2、趣味3」だと「趣味の集合」を意味している
「かもしれない」ので繰り返しグループ「かもしれない」。
それでも例えば趣味は常に正しく3種類入力し、その順序にも意味がある、
という仕様であればこれもまたタプルであって繰り返しグループではない。
要するにcid1〜cid3の属性名が似てるとか同じ外部キー制約を持っている
云々は正規形を判断する傍証にはなっても本質的には何の意味も無い。
実際にはcid1〜cid3が意味するところが解らないと第一正規形かどうかは
判断できないし、これはスキーマ定義からは読み取れない、ってことを
皆は言っている。ながなが。
「好きな趣味」「嫌いな趣味」「どっちでもない趣味」とかでいいだろ
>>301 が何を言いたいのかまったく理解できない…。
「実際の仕様は本人しかわからない」という話をしてるのに
「仕様はこうでいいだろ」ってどういう意味だ?
正規化はスレ違いってこともわからない馬鹿。
>>299 複雑な計算結果を保持するカラムを作り、その内容を
トリガで行が挿入/更新されるたびに計算してセットする
現在値を更新する処理のパフォーマンス的には有利かもしれない
(現在値の更新でトリガが走るなら作り方に注意がいる)
普通に行を挿入/更新するパフォーマンスは若干落ちる
後はこれぐらいしか思いつかん
つかこの質問にしても、正規化の話にしても
設計はスレ違いだよな、ここ
306 :
296 :2010/07/21(水) 15:45:07 ID:???
SQLレベルでの解決ができるならこのスレ、と思ったけど
設計レベルでになるとスレ違いになるので難しいところ。
>>305 用途ごとのクエリの頻度次第では
トリガは一考の価値ありですね。ありがとうございます。
CTE使えないの?
308 :
296 :2010/07/21(水) 22:52:44 ID:???
>>307 ググっても情報が少なめでよくわかりませんが
どうやらMySQLは非対応のようです。
良くわかんないけど、サブクエリでダメなら、RowIDで結合すれば?
311 :
NAME IS NULL :2010/07/24(土) 14:48:57 ID:QZVGATrx
すみません、教えてください。 テーブルAとテーブルBがあり、 テーブルAには、1〜1000万までの連番を格納しています。連番はキーです。 テーブルBの方には、その連番の開始値と連番数が格納されています。 開始値 | 連番数 --------------- 1001 | 100 ←1001から1100までの意味 5001 | 100 ←5001から5100までの意味 9001 | 100 ←9001から9100までの意味 テーブルBで指定されている連番に一致するレコードを テーブルAから取得するにはどのように書けばよいでしょうか。 上記の例だと300件取得する形になりますが、 テーブルBにも1万件程度のレコードがあります。 DBMSはOracle10gです。
キー >= 開始値 and キー < 開始値 + 連番数 でいいでしょ。
>>311 between使えという神の啓示のような例題だな
>>312-313 SQLすべて書いてやれば?
1000万行x1万行のJOINや1000万行xIN(1万行)は避けたいところ。
再帰SQLなら1万行+取得行分のインデックス検索だが、10gはconnect byか。
>>311 こんなんはどうだろうか?遅いかもしれんが
select * from A A1 where
exists (
select * from B where
exists (
select * from A A2 where
A2.key = A1.key
A2.key >= B.開始値 and
A2.key < B.開始値 + B.連番数)
)
)
あ、ちなみに俺は
>>312 でも
>>313 でもない
ベーシックなのから書けよ select A.* from A cross join B where A.キー between B.開始値 and B.開始値 + B.連番数 - 1 ;
317 :
314 :2010/07/24(土) 20:26:09 ID:???
>>315 EXISTSは2重にしなくとも
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A1.key BETWEEN 開始値 AND 開始値+連番数-1);
でいけると思う。
EXISTSでもインデックス検索じゃねーから、遅いでしょうね。
318 :
314 :2010/07/24(土) 20:28:17 ID:???
319 :
315 :2010/07/24(土) 20:31:45 ID:???
320 :
314 :2010/07/24(土) 20:50:47 ID:???
SELECT A.* FROM A LEFT JOIN B ON A.key BETWEEN B.開始値 AND B.開始値+B.連番数-1 WHERE B.開始値 IS NOT NULL; Oracleは持っていないので、通るのかどうかわからないが、 これなら、1000万行+1万行ですみそうな気がする。
>>313 BETWEENって
MIN <= Value < MAX
って可能なの?最小値、最大値にイコールが入ってるパターンしかダメなんじゃなかったっけ
>>320 わざわざOUTER JOINしてチェックする意味ある?
素直にINNER JOINで良い気がするんだが
OUTER JOINってINNER JOINより遅いイメージなんだがそんなことない?
323 :
314 :2010/07/24(土) 21:47:29 ID:???
>>322 Σ(゜д゜;)
なんか、Nested Loopを回避したくて、なんとなくINNER JOINを避けたけど、
いわれてみりゃ、そんな根拠ないなw
俺も頭の中、偏ってる。
CentOSのMySQLを5.0系から5.1.49にアップデートしました。 そして、service restart mysqldとしたところ、起動せず、ログに以下の記録が残っていました。 100726 18:54:05 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 100726 18:54:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 100726 18:54:50 [Note] Plugin 'InnoDB' is disabled. /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 100726 18:54:50 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 100726 18:54:50 [ERROR] /usr/libexec/mysqld: unknown option '--skip-bdb' 100726 18:54:50 [ERROR] Aborting 100726 18:54:50 [Note] /usr/libexec/mysqld: Shutdown complete 100726 18:54:50 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 書いてある通りに、mysql_upgradeをやってみたのですが、 mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect とでて、起動しませんでした。 どうすれば起動するようになりますか?
スレ違い。MySQLスレへGo
分かりました。 そっちで聞いてきます。
ID 名前 カテゴリID 数字 日付 1 たぬき 1 5 7月3日 2 たぬき 1 8 7月4日 3 かっぱ 2 3 8月5日 4 きつね 3 9 1月3日 5 きつね 3 5 9月3日 のようなテーブルで カテゴリIDごとでさらに名前ごとに数字の最小値を求めたいのですがどう書けばいいでしょうか? この場合だとID1、3,5が取り出せるようにしたいのですが。 MYSQL5.1を使っています。
>>328 すみません
>>4 を見て自分で試そうとしたのですが、
むずかしすぎてわかりませんでした。
あきらめろ。
そこをなんとか
>>329 まず最初に、「最小の数字」がすでに分かっている固定値(5とか3とか)だとするSQLを書く
↓
次に最小値を求めるSQLを書く
↓
やったね!
>>327 select A.ID from テーブル A
join (select カテゴリID,名前,min(数字) as 最大数字 from テーブル group by カテゴリID,名前 ) B
on A.カテゴリID=B.カテゴリID and A.名前=B.名前 and A.数字=B.最大数字
指定条件でダブりがあったりNULLがあったりした場合はどうなるかしらね
でんすらんく
品目 日付 ------------------------------- 酢だこ 7/20 酢だこ 6/20 まだこ 7/21 まだこ 7/1 まだこ 6/25 大だこ 7/1 いか 6/20 こんな感じのテーブルがあり、品目名に"だこ"を含むデータを出現頻度の高い順に取り出そうとしていますが、 select 品目,日付 from table where 品目 in (select 品目 from table 品目 like '%だこ%' group by 品目 order by count(品目) desc; で、やってみたのですが、遅すぎて使えませんでした。 何かいいやり方は無いでしょうか? お願い致します。
like は前方一致じゃないとインデックスを使えない。 「○○だこ」のパターンがあらかじめ全部分かっていれば where 品目 in ('酢だこ','まだこ','大だこ') などにすればインデックス(があれば)使ってくれるはず。
>>335 いまいち何がやりたいかわからんな
select 品目,count(*) as 件数 from table where 品目 like '%だこ%'
group by 品目 order by count(*) desc
とかじゃダメなのか?
個人的に気になったんで調べてみたけど 今どきはどのDBMSも無償オプションなり何なりで 全文検索機能提供してるのね。 日本語全文検索がどの程度使い物になるかは やってみないと分からんけど。
>>335 この手の検索を今だけ刹那的に行うのか、定型作業として頻繁に
行うのかわからないけど、後者であれば普通にカテゴリを管理した
マスタ表を用意するのが無難かと。
品目 カテゴリ
==========
酢だこ たこ
まだこ たこ
大だこ たこ
たこ たこ
いたこ ひと
いか いか
はまぐり かい
やきぐり くり
>>335 品目の重複が多いのなら、HAVINGが早いかも。
SELECT T1.* FROM Table AS T1 JOIN
(SELECT 品目,count(品目) AS cnt FROM Table GROUP BY 品目 HAVING 品目 like '%だこ%') AS T1
USING(品目)
ORDER BY cnt DESC;
>>340 2行目の(サブクエリ) AS T1 は AS T2の間違い。
スマソ。
mysqlです。 レコードの削除処理でlimitを使用した残りを全て削除するようなSQLはどのように記述すればいいでしょうか
EXCEPTでぐぐるといいことがあるかもしれんよ
MySQLでEXCEPT使えるようになったっけ?
>>335 のデータで最新日付3件を残して他を削除
DELETE FROM Table AS T1 WHERE 3< (SELECT count(*) FROM Table T2 WHERE T1.日付 < T2.日付);
でも、3件目の7/1が2件あるから、4件残ってしまう。
そもそもこれもMySQLで動くかどうかも不明。
345 :
342 :2010/07/28(水) 23:11:05 ID:???
346 :
NAME IS NULL :2010/07/29(木) 17:52:57 ID:VSFhFikc
はじめまして データベースの勉強中です(基本情報技術者試験) SQLの問題で 選択と射影の違いをよく問われます ・選択 条件に合った行を抽出 ・射影 特定の列を抽出 (例) 表 syain(syanid,syainmei,age) 例1 select * from syain where age >= 20 (syain表から、ageが20以上の行を抽出しているので選択) 例2 select syainmei from syain (syain表から、syainmeiの列のみ抽出しているので射影) ということですが 以下の例だと 例3 select syainmei from syain where age >= 20 syainmeiという特定の列を指定して さらに ageが20以上という条件もあります 参考書やネットで調べてみると where条件があれば「選択」という意見と 特定の列を指定していれば「射影」という意見に分かれているようです 例3の場合 選択と射影 どちらと見るべきなのでしょうか? アドバイスよろしくお願いします
>>346 射影は重複も省くからdistinctしなきゃいけないよ。
それをぬきにしても射影や選択は関係演算のプリミティブな単位だから、例3は射影と選択を使っているといえる。
349 :
NAME IS NULL :2010/07/30(金) 04:12:28 ID:9Np/J98F
>>346 選択(制限)や射影といった関係代数の演算は算数の足し算引き算
かけ算割り算と一緒で組み合わせて使うことができるんだよ〜
なので正解は「選択してから射影」だね。「射影してから選択」
ではないので要注意。
350 :
39 :2010/07/30(金) 07:10:30 ID:???
「選択」とか「射影」とか初めて聞いた。 勉強になりました。 by 開発技術者歴10年
射影って普通、高校の数学で習わないか
射精なら・・・
>>353 顔面蒼白…日本って想像以上にやばくなってんだな
355 :
346 :2010/07/31(土) 01:19:24 ID:A1610sXI
356 :
NAME IS NULL :2010/08/02(月) 17:12:02 ID:6clj2YAP
次のようなSELECTをするケースではどのようなSQLが妥当でしょうか? ・テーブルAとBをJOINしたい ・テーブルCとDをJOINしたい ・A+BとC+DはLEFT JOINしたい とりあえず次のような2ケースは思いつくのですが、 【2回LEFT JOINする】 SELECT * FROM A JOIN B USING( key_ab ) LEFT JOIN C USING( key_ac ) LEFT JOIN D USING( key_cd ) これだと理論的には1回でいいはずのLEFT JOINが2回になりパフォーマンスがもったいない気が。 【CDをサブクエリにする】 SELECT * FROM A JOIN B USING( key_ab ) LEFT JOIN ( SELECT * FROM C JOIN D USING( key_cd ) ) CD USING( key_ac ) これはこれでしなくてもいいサブクエリにしてるようでやはりパフォーマンスが。 他に方法はあるでしょうか。
357 :
356 :2010/08/02(月) 17:16:25 ID:???
情報不足でした、DBMSはMySQL5です。 標準SQLとして気になるところですが、さしあたってMySQLでできればいいです。
>>356 SELECT * FROM
A JOIN B USING(key_ab)
LEFT JOIN
(C JOIN D USING(key_cd)
USING(key_ac);
標準なのか、MySQLで動くのかも知らないけどね。
括弧ひとつ抜けてた (C JOIN D USING(key_cd)) 最初のも括弧で括った方が、後からわかりやすそう。 (A JOIN B USING(key_ab))
360 :
356 :2010/08/02(月) 18:25:55 ID:???
動いた!括弧でくくるだけの話だったとは…試したことあったと思ったんだけどなぁ。 ありがとうございました!
いやまて、動いた動かなかったレベルの話なのか? パフォーマンス的にはFROM句サブクエリはいいもんじゃないぞ。
362 :
356 :2010/08/02(月) 21:34:30 ID:???
>>361 え、FROM句サブクエリってのは
>>356 の後者のことであって
括弧でくくるのはサブクエリにはならないでしょ?
explainしてみた限りじゃ大丈夫そうですが。
test
364 :
NAME IS NULL :2010/08/11(水) 07:44:11 ID:7xoB/G7y
oracle 10gです。 商品コード|売上店舗|売上個数 というテーブルから、 店舗毎の売上をまとめた列を追加した以下のようなテーブルを作りたいです。 商品コード|店舗a売上個数|店舗b売上個数|店舗c売上個数| 現在は店舗毎の売上を集計したテーブルを別に作って、それを結合させて作っていますが、それ以外で単一のsqlで書く場合どうなるでしょうか? 簡単かと思ったら思いつきません。。 お願いします。
365 :
NAME IS NULL :2010/08/11(水) 07:48:19 ID:7xoB/G7y
↑テーブルを作りたい、ではなく下のように抽出したい、です。 すみません。
ある期間に、大阪府と京都府に転入して来た人の数を出したい時のSQLを、 select count(*), 都道府県, 日付 from 共通テーブル as t1 join (select 都道府県, 日付 from 共通テーブル where 日付 between 'YYYY-MM-DD' and 'YYYY-MM-DD' and 都道府県 like '%府') as t2 using (都道府県、日付) としてMYSQL5.0.88でやってみたのですが、1行しか出ません。 sortを掛けてdescを、別の1行が表示されます。 Q1) 複数行を表示するにはどうしたらいいのでしょうか? Q2) 他に何かいいSQL文はありるのでしょうか? ご助言お願いします。 ※実際は、日本語名と日本語データは使っていません。
>>364 >>5 >>366 共通テーブルの定義がどのようになっているか、イマイチなんだが。
行数をカウントすればいいのか?
SELECT 都道府県,日付,count(*) FROM 共通テーブル
WHERE 日付 BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND 都道府県 LIKE '%府'
GROUP BY 都道府県,日付;
369 :
NAME IS NULL :2010/08/12(木) 23:16:07 ID:UMUtuT0a
会社コード|部署コード|課コード|給料 というテーブルがあったとき、 GROUP BY 会社コード , 部署コード で部署毎の給料合計を出したいけど、 抽出結果の列には課コードも含めたい、 というときってどうしたら良いのでしょうか? イメージは↓の感じです。 A会社|B部署|C課|100万円 A会社|B部署|D課|100万円 A会社|E部署|F課|500万円 A会社|E部署|G課|500万円
371 :
NAME IS NULL :2010/08/12(木) 23:38:29 ID:UMUtuT0a
>>370 ありがとうございます。
テンプレは見たのですが、これが
>>4 に該当すると気づかずスルーしてしまいました。
これは自己結合というやつでしょうか。
GROUP BY したものと、それ以外の自分を結合する感じってことなのかな?
ちょっと書いたことないので、試しにやってみます。
>>369 そういうのはpartition byを使う。
あとdbmsとバージョンかけ。
>>5 のSUMの中をcaseじゃなくifを使って書こうと思って
SELECT KEY,
SUM(IF data = 'a' THEN 1 END) AS a,
SUM(CASE data WHEN 'b' THEN 1 END) AS b
FROM table
GROUP BY key
ORDER BY key
;
としてみたのですが、
「ORA-00907: 右カッコがありません。
」
とエラーが出て実行できませんでした。
どうすればifを使って書き換えられますか?
環境はOracle 11gです
374 :
NAME IS NULL :2010/08/17(火) 15:13:02 ID:S3HPeix8
はじめまして ・Access2000 下のようなテーブルがあったときに テーブルA 名前 科目 点数 ---------------------- 岡村 国語 51 岡村 数学 48 岡村 英語 39 徳田 国語 60 徳田 数学 30 徳田 英語 48 渡辺 国語 28 渡辺 数学 67 渡辺 英語 25 下のように各科目の最高点を名前つきで取得するにはどうすればいいのでしょうか? 結果(各科目の最高点) ---------------------- 徳田 国語 60 岡村 数学 48 徳田 英語 48 よろしくおねがいします。
378 :
374 :2010/08/17(火) 20:54:04 ID:???
できました ありがとうございました
>>373 なんで勝手に書き換えるんだよw
>>374 accessでも同じ
エイリアスの書き方が通らないかもだけど
380 :
373 :2010/08/18(水) 03:16:10 ID:???
すいません SQLにIFなんてないんですね 勉強のためにIF使って書いてみようと思ったのですが ありがとうございました
SQLServer2005で質問です 主キーをauto incrementにして INSERT時に連番が振られるようにしています。 このidをintではなくvarchar(10)で'0000000001'から 振られるようにしたいのですが、 id_str などのフィールドを宣言して id(int)で連番を振った後で、id_strを更新するしかないのでしょうか。
うん。
id_strを計算列として定義したら、いちいち更新しなくて済むよ。
こんな感じ CREATE TABLE TBL ( id int IDENTITY(1, 1) NOT NULL, id_str AS REPLACE(STR(id, 10, 0), ' ', '0'), col1 varchar(50), CONSTRAINT PK_TBL PRIMARY KEY (id) )
・DBMS名とバージョン SQL Server Compact 3.5 ・テーブルデータ 家計簿のようなデータです Cd1 Cd2 Item Cost ---+----+-----------+---------------- 1 0 食費 NULL 1 1 弁当 500 2 0 光熱費 NULL 2 1 水道料 1000 2 1 水道料 2000 2 2 ガス料 4000 ・欲しい結果 Cd1 Cd2 Item Sum ---+----+-----------+---------------- 1 0 食費 500 2 0 光熱費 7000 ・説明 Cd1が大分類、Cd2が中分類を表してます。 Cd2が0の場合、Itemは大分類の名称。それ以外の場合、中分類の名称が入ってます。 大分類ごとにItemと合計額が欲しいです。
>>386 SELECT 家1.Cd1, 家1.Cd2, 家1.item,
(SELECT SUM(家2.cost) FROM 家計簿 家2 WHERE 家2.Cd1 = 家1.Cd1) AS sum
FROM 家計簿 家1 WHERE 家1.Cd2 = '0'
389 :
NAME IS NULL :2010/08/23(月) 07:08:36 ID:7A4MqZEH
390 :
NAME IS NULL :2010/08/23(月) 15:13:15 ID:CCVEhEto
SQLServer 2005 EE 注文 顧客|注文番号 A 1 B 2 C 3 注文明細 注文番号|商品|時刻 1 ミルク 10:53 1 納豆 14:00 2 ミルク 10:55 2 豆腐 11:05 2 納豆 12:00 3 ミルク 11:03 3 納豆 9:50 これらのテーブルに対して、ある時刻の間にある商品を発注した 明細と同じ注文の明細一覧を出したいです。 10:50〜11:00の間の「ミルク」の場合 A 1 ミルク 10:53 A 1 納豆 14:00 B 2 ミルク 10:55 B 2 豆腐 11:05 B 2 納豆 12:00 とう出力を得たいです よろしくお願いします。
結果の例がおかしいけど、あまりにも初歩的な内容な気が・・・ JOIN と BETWEENでいけるよね?
>>39 10:50〜11:00にミルクが含まれている注文の注文明細の一覧なのですが、
JOINとBETWEENでいけますか?
あと、結果の例は合ってます。
まず指定の条件の明細を含む注文番号をもとめる。 select 注文番号, 商品, 時刻 from 注文明細 where 注文番号 in ( select distinct(注文番号) from 注文明細 wehere 商品='ミルク' between 時刻 '10:50' and '11:00' ) inでもjoinでも好きなほうで。注文テーブルとのjoinは省略した。
>>393 ありがとうございます
注文と明細を結びつける注文番号が2カラムからなるのですが、どうしたらいいでしょうか?
複合キーってこと?
>>395 複合キーって言うんでしょうか?
実際には、日付とセットじゃないとユニークにならないんです
>>390 select b.顧客,a.商品,a.時刻
from (select 注文番号,商品,時刻,
max(case when 時刻 between '10:50' and '11:00'
then 1 else 0 end) over(partition by 注文番号)
as flag
from 注文明細) a,注文 b
where a.flag = 1
and a.注文番号 = b.注文番号
max overは、この記事のを応用した。
http://codezine.jp/article/detail/3231
>>397 ありがとうございます
ちょうと今メンテ中で試せないので後で試してみます
399 :
NAME IS NULL :2010/08/24(火) 04:10:39 ID:4e1wvTf+
>>398 390はちょっとな。普通は393のように書くと思う…
390はレスポンス的に大丈夫なの?
(Enterprise Editionっていうくらいだから数千万件以上の大規模DB?
実行計画等で検証しないとさ…)
あと「日付とセットじゃないとユニークにならないんです」ってあるけど
注文明細テーブルに「注文明細番号」ってカラムを追加して
・注文書番号
・注文書明細番号
の2カラムを主キーにすればい…い…ん…じゃ…な…い…か?
400 :
NAME IS NULL :2010/08/24(火) 04:13:13 ID:4e1wvTf+
ごめんなさい。 「397はちょっとな」「397はレスポンス的に大丈夫なの?」 の間違いです。
401 :
NAME IS NULL :2010/08/24(火) 04:32:59 ID:4e1wvTf+
注文明細の件数が著しく大きくなった時397は危険な気が… このクエリのみに限定すれば、 (「注文書番号」、「注文書明細番号」)を非クラスタな主キーにして、 時刻をクラスタ化インデックスに設定して範囲検索を高速化させる とかの考慮を緻密にしていかないと大規模DBはマジフリーズ…
402 :
NAME IS NULL :2010/08/24(火) 04:34:45 ID:4e1wvTf+
「このクエリ」ってのは393だよ。 くどい?
>>399 >>397 まだ試せてないです。
するどいですね。もう億に乗っている件数です。このパターンの検索が一番多いので、パフォーマンス出ない
なら、テーブル構成も変えることも考えてます。
実は今は以下の検索をしてるんですが、遅いので他の解があればと質問してみました。もしくは、テンポラリ
テーブル or プログラムで吸収という最悪の事態も考えています。
SELECT * FROM 注文
INNER JOIN 注文明細
ON 注文.注文番号 = 注文明細.注文番号
WHERE EXISTS(SELECT * FROM 注文明細 AS 検索明細
WHERE 検索明細.商品 = 'ミルク' AND 検索明細.時刻 BETWEEN 10:50 AND 11:00)
>>397 正しくは、こうだぜ
select b.顧客,a.商品,a.時刻
from (select 注文番号,商品,時刻,
max(case when 商品 = 'ミルク'
and 時刻 between '10:50' and '11:00'
then 1 else 0 end)
over(partition by 注文番号) as flag
from 注文明細) a,注文 b
where a.flag = 1
and a.注文番号 = b.注文番号
inをjoinに変えるだけだろうからこんな感じでいいだろう select a.日付. a.注文番号, a.商品, a.時刻 from 注文明細 as a join ( select distinct 日付, 注文番号 from 注文明細 where 商品='ミルク' between 時刻 '10:50' and '11:00' ) as b on a.日付 = b.日付 and a.注文番号 = b.注文番号
406 :
NAME IS NULL :2010/08/24(火) 20:10:44 ID:vSbQzCaU
>>403 399です。以下は393のクエリ前提。
※397はありえない。唯一の絞込条件の時刻をcase変換→max→whereで=1?
1億件全件なめて下さいっていってんの?
インデックスはどうなってんの?
年月日指定の上での時刻範囲指定なの?
そうだったら(年月日,時刻)に非クラスタ化インデックスは必須。
※1億件想定なので、年月日指定で絞込度がかなり高い→非クラスタでよい。
年月日指定無しの時刻範囲指定だったら、
(商品,時刻)にクラスタ化インデックスを張れば
このクエリに関しては速くなる。
非クラスタだとBookmarkLookup回数が多すぎでレスポンス上がらない懸念。
現在のインデックスを教えて
>>403 このクエリちゃんと意図どおりに動く?なんか写し忘れてにない?
408 :
NAME IS NULL :2010/08/24(火) 21:36:50 ID:2QSEhmny
よろしくお願いいたします。 ・DBMS名とバージョン HiRDB Ver8 ・テーブルデータ <Aテーブル> A1 A2 A3 A4 A5 ------- ------- ------- ------- ---------- XXXXXXX XX1 ABC 3 2009/05/08 WWCWWCW WW2 CCB 1 2008/03/21 DDDDDDD DD1 JPN 5 2007/08/08 GGGGGGG GX9 SOX 2 1977/01/04 FFFFFFF USJ NPB 3 2001/09/11 <Bテーブル> B1 B2 B3 B4 B5 ------- ------- ------- ------- ---------- XXXXXXX XX1 ibicha oshimu 2002/07/05 XXXXXXX XX1 takeshi okada 2005/07/15 XXXXXXX XX1 kamo shu 1857/09/25 WWCWWCW WW2 wao wao 2008/10/22 DDDDDDD DD1 ui hhh 2006/06/30 DDDDDDD DD1 jojoj x5xx 1999/09/09 DDDDDDD DD1 momo hara 2005/03/07 DDDDDDD DD1 itai u- 2003/12/22 DDDDDDD DD1 koma nogoal 2007/04/26 GGGGGGG GX9 dame record 2009/11/14 FFFFFFF USJ iki tai 1995/08/15 FFFFFFF USJ sst ebuspi 2004/01/05 FFFFFFF USJ bb pp 2009/08/23 ・欲しい結果 A1 A2 B1 B2 A4 B1andB2COUNT ------- ------ ------- ------- ------- ------------ GGGGGGG GX9 GGGGGGG GX9 2 1 ・説明 SQLで違うテーブルの値を比較して値に差があるレコードを抽出したいのですがヒントをいただけないでしょうか。 A1とB1は、A2とB2は同じデータが基本です。 Aテーブルの「A4」カラムにはBテーブルのレコード数を持っています。 しかし、よくよく見るとAテーブルの「GGGGGGG」のA4には「2」のはずなのにBテーブルには実際にレコードは1つしかありません。 こういう状態になってしまっているのを割り出したいのですがどのようにA4とBテーブルで数があってないものを抽出できるでしょうか。 よろしくお願いいたします。
B1でGroup ByしてCount()した結果とA4を比較すればいいだけだろ。
410 :
NAME IS NULL :2010/08/24(火) 21:40:05 ID:2QSEhmny
すみません 書体がずれて見栄えがすごく悪い・・・ Aテーブルの例は XXXXXXXがA1カラム XX1がA2カラム ABCがA3カラム 3がA4カラム 2009/05/08がA5カラムです
411 :
NAME IS NULL :2010/08/24(火) 21:43:22 ID:2QSEhmny
>409 申し訳ないです。 group by B1, B2 とcountした結果とA4の比較方法がわからなくて困っています。 JOINとかの使い方がいまいち分かりづらくて・・・ ごめんなさい
少しは自分で考えろ。
413 :
NAME IS NULL :2010/08/24(火) 21:53:31 ID:2QSEhmny
考えましたよ(;_+)そんなきつい言い方竹刀でください・・・ SELECT A.* FROM A INNER JOIN (SELECT B1,B2,COUNT(*) CNT FROM B GROUP BY B1,B2) B ON A.A1=B.B1 AND A.A2=B.B2 AND A.A4 <> B.CNT; なんてやってみたのですがJOINの後が無効な識別子エラーが出ます。 JOINの規格が違うのか・・・
HiRDB! このスレに答えられるやついるのか?
415 :
414 :2010/08/24(火) 22:41:36 ID:???
あ、ちなみに
>>413 のSQLは標準SQLとしては別におかしくはない。
ちょっと引っかかるのはテーブル名と同じ相関名って使えたっけな?ってくらい。
(サブクエリの括弧の後の B のとこね。)
あとはHiRDBでどういうSQLなら使えるのかってことだけど・・・
416 :
NAME IS NULL :2010/08/24(火) 23:35:48 ID:2QSEhmny
あっ。。。相関名同じのはまずかったすね。。。でも変えてみても 識別子がおかしいと怒られちゃいました。。。 エラー変わらずでス(汗) HiRDBなんてマイナーすぎなのかなぁ HiRDBの準拠しているものを調べてみたのですがどこにも出ていないようで見つけられてはいないです。 近似したRDBってあるのかも???
MySQL Ver5以降で質問です。 まずテーブルの説明をします。 user_dataテーブル user_id, level friendテーブル user_id, friend_user_id という2つのテーブルがあります。 friendテーブルは 1, 2 というレコードであれば 「user_id=2は、user_id=1の友達である」 という意味となり、必ずそれぞれを入れ替えた「2, 1」というレコードも存在します。 user_dataテーブルから 「levelが4で、かつあなたの友人ではないユーザーのレコードを全て取得する」 ということをやりたいのですが、可能でしょうか。 「levelが4で、かつあなたの友人『である』ユーザー〜」であれば(あなたのIDは1とします) SELECT * FROM user_data INNER JOIN friend ON user_data.user_id = friend.user_id AND 1 = friend.friend_user_id; でOKかと思います。 この逆がやりたいのですが… サブクエリーを使って、NOT IN を使うなどすれば不可能では無さそうですが、1の友達が1000人くらいいるときつそうです。
>>417 select *
from user_data T1
where T1.level = 4
and not exists (
select *
from friend T2
where T1.user_id = T2._user_id
and T2.friend_user_id = 1
)
;
>>413 INNER JOINのキーワードを使わない、昔ながらのJOINにすれば
いけるんじゃなかろうか。
SELECT A.*
FROM A, (SELECT B1,B2,COUNT(*) CNT FROM B GROUP BY B1,B2) BB
WHERE A.A1=BB.B1
AND A.A2=BB.B2
AND A.A4 <> BB.CNT;
>>417 AND 1 <> friend.friend_user_id AND level=4
レベルを忘れていそうだから足しといた。
AND 1 <> friend.user_id も必要だな。
422 :
NAME IS NULL :2010/08/25(水) 21:43:00 ID:VtTC/Lir
>>418 ありがとうございます
EXISTSというのは初めて知りましたが、望んだとおりの結果が得られました
勉強しておきます
ちょっとした質問なんですが SQLで落ちた過去ログをdatで自動的に保存できたりしますか? もし出来たとしたらどういった構文を使えばよいのでしょうか?
日本語でOK
SYBASEのSQLについてお聞きしたいです。 現在大量なデータの入った複数のテーブルを INDEXを持った項目でJOINし、DELETを行う SQLを作成しています。 このとき、発行するSQLがINDEXを使用するかを SQL実行前に確認できるすべがあれば教えてください。
たぶんない。 スレ違いだから、二度と来んな。
>>427 SYBASEよく知らないしバージョンとか書いてないからなんとも言えんが
実行計画ぐらい取れるとおもうんだが
まあ、SYBASE 実行計画 でググって二度と来るな
>>428、429 了解しました。 ありがとうございます。
ぶっちゃけここにいる人ってかじった程度の知識の人ばっかだよね、笑えるw
2chだし。
SQLのインデントできないし、過去ログ落ちしたら見れなくなるし 普通は、OTNとかMSDNに行くんじゃね
>かじった程度の知識の人ばっかだよね、笑えるw 勘違いしているようだから、教えよう。 教える人は、SQL勉強中の人。 教えることにより、知識を得ることができる。 マスターした人は、常駐しない。得るものがないから。 よって、俺もここに2度と来ないだろう。
その「かじった程度の人」が得意げに書き込む回答の間違いを 突っ込んでやろうと虎視眈々と狙っているベテランも結構いるぞ。 自分からはあんまり回答しないけどな。
何この不毛な雑談
以下のようにSQL定義をしたのですが 最後の部分でAS付近に〜と言うエラーが出てしまい コンパイルが出来ませんでした。 何が間違っているのか良く分かりません・・・ お力お貸しください・・・ SQL = "SELECT メインテーブル.*," ----------------------------------------------------------------- SQL = SQL & " (SELECT A.ABC AS テスト1 " SQL = SQL & " FROM テーブルA A " SQL = SQL & " WHERE A.ナンバー = メインテーブル.ナンバー" SQL = SQL & " GROUP BY A.ナンバー) AS テスト1," ----------------------------------------------------------------- 上記の---の中と同じようにその他に4つ定義 今まで4つだけ定義してたのですが、今回改修作業があり同じように 1つ追加することで最初に書いたエラーが出るようになってしまいました。 SQL = SQL & "FROM メインテーブル LEFT OUTER JOIN " SQL = SQL & " マスタ ON " SQL = SQL & " A.顧客ID = マスタ.顧客ID "
意味不明だし勝手に省略したらわかるもんもわからんだろ。 まあエスパーレスするとFROMの前に「AS テスト1,」のカンマが残って文法エラーとかだろ。
>>434 > マスターした人は、常駐しない。得るものがないから。
> よって、俺もここに2度と来ないだろう。
マスターしたんだ。
すごいなー。
>>437 最低限、使ってるDBMSぐらいかけ
追加したってなら追加したSQLも書け
何が間違ってるか?お前が追加した結果SQLが間違ってるに決まってるだろう
>>438 1個追加したってんだから、どっちかって言うと追加する直前にカンマ付加してないだけだと予想
どうでも良いけどこのSQL、文法チェックは通っても実行時のデータ次第で
実行時エラーになる可能性あるのは判ってるんだろうかね
>>437 もともと定義されていた4つのうち、上3つは最後にカンマがついていて、
4番目のものにはカンマがなかったはず。
それにしたがって追加するとよいよ。
>>438 意味不明って
いきなりコンパイルができませんでしたとか意味わからんダロw
ひとつのテーブルで各レコードは他のレコードと1:Nの親子関係にあります コード 親コード データ 1 1 A 2 1 B 3 2 C 4 3 D 5 1 E 6 2 F まあこんな感じで1以外は全部親を1つ持ち親はひとつ以上の子を持ちます(子が無い親も居ますが) これである親の子レコード以下を下記のような感じで全部抽出したいのですが SQLだけで出来ますでしょうか コード=2のときの抽出結果 コード データ 世代 3 C 1 4 D 2 6 F 1 世代は2から何世代下にいるかです、子なら1孫なら2…ということです また無理ならこうゆうことを可能にするデータ構造を教えてください DBはFireBird1.5を想定していますがフリーのDBなら何でも良いです
>>443 PostgreSQLなrWITH RECURSIVEかconnevt_by関数で出来る。
WITH RECURSIVE Temp(code,data,tree) AS
(SELECT code,data,1 FROM Table1 WHERE pcode=2
UNION ALL
SELECT T1.code,T1.data,Temp.tree+1 FROM Table1 AS T1,Temp WHERE T1.pcode=Temp.code)
SELECT * FROM Temp;
再帰クエリが使えないのなら、入れ子集合モデルってのがあるにはあるがな。
pl/sql カーソルFORループを宣言して 該当するレコードが1つもなかったとき NO_DATA_FOUND で拾えますか?
スレ違い
の純情
三分の一の
451 :
NAME IS NULL :2010/09/23(木) 17:04:17 ID:qAHVmbyC
最近になってDBの勉強を始めたのですが詰ってしまったのでちょっと質問させてください。 使っているのは、derbyです、バージョンはij起動時に10.5と表示されています ・テーブルデータ 受注表 ------------------- 顧客コード | 受注個数 ------------------- 001 | 100 002 | 200 003 | 100 001 | 300 003 | 400 ・欲しい結果 結果 --------------------------------- 顧客コード | 個人平均値 | 全体平均値 --------------------------------- 001 |200 |240 002 |200 |240 003 |250 |240 ・説明 SELECT 顧客コード, AVG(受注個数) AS 個人平均値, (SELECT AVG(受注個数) FROM 受注表) AS 全体平均値 FROM 受注表 GROUP BY 顧客コード; 上記のSQLだとエラーが出て動かない(エラーメッセージ失念してしまいました) どこがどう悪いのか現在の自分では分からなくてどうしようもない状態です。
SELECT 顧客コード, AVG(受注個数) AS 個人平均値, 全体平均値 FROM 受注表, (SELECT AVG(受注個数) AS 全体平均値 FROM 受注表) GROUP BY 顧客コード;
453 :
451 :2010/09/23(木) 19:38:41 ID:???
>>452 ありがとうございます、今試す環境がないので明日確認してみます。
それにしても、SQLって色んな書き方あって一筋縄では行かないのね・・・・
大変だけど楽しいですね
454 :
451 :2010/09/24(金) 23:33:49 ID:???
今日試してきたが452では動かなかった
SELECT
顧客コード,
AVG(受注個数) AS 個人平均値,
TOTAL_AVG.全体平均値
FROM
受注表,
(SELECT AVG(受注個数) AS 全体平均値 FROM 受注表) AS TOTAL_AVG
GROUP BY
顧客コード;
でやっと動いてくれました。
何はともあれ、
>>452 のお陰で突破できたよ、本当にありがとう
質問です。 where句が膨大な数の条件式による論理和(OR)で構成されているselect文において、 得られた各レコードがどの条件式によるものかを判断したい場合どのようにするべきですか? 例えば下のようなテーブル(t)に対して id name sex age 0 tanaka male 25 1 yamada female 18 2 suzuki male 16 select name from t where age < 20 or sex = 'male'; を発行した場合すべてのnameが得られますが、それぞれのレコードがどの条件式によって得られたものかを判断したいと考えています。
個々の条件式で得られた結果との差分を取ればいいんじゃね?
プログラム上で分岐させるために判断するなら条件ごとに別SQLにすれば良いじゃん
UNION ALL 使えば〜〜
なぜそんなことをしたいのか分かんないと適切な答えができないな こんなんでもいいんか? select name, case when age < 20 then 1 end as 条件1, case when sex = 'male' then 1 end as 条件2 from t where age < 20 or sex = 'male' ;
今回1回だけならなんとでもなるだろうけど、今後もそれをちょくちょく調べるなら、 ORを全部ばらしてUNIONに書き換えておくほうがいいだろうねぇ。
461 :
NAME IS NULL :2010/09/27(月) 00:08:38 ID:HpHUNuXu
じゃなかった、質問しに来たんだ。 PKが二つあるテーブルで、WHERE句のサブクエリで (hoge, moge) IN (SELECT homo, momo FROM xxx WHERE xxx.x1 = yyy.x2) をやりたいのだけど、↑の記法が出来ない場合(H2Database)に一般的な方法ってどんなんがあります? (hoge || moge) IN ・・がすぐ思いついたけど、ちょっとそりゃパフォーマンスわるいでそ!って感じなので。
普通EXISTSだろ。
俺なら普通にjoinしてからhoge=homo and moge=momoだな from句以外にサブクエリ書くのはなんか格好悪い(exists除く)
おうふ
たしかにそのとおりでござるな。
がんばって書き換えてみよう。
>>463 そもそも実際はこのSQLはSELECT句にあって入れ子になってたりするので
FROM句に移動するのはちょっと難しいというかパフォーマンスかなり悪くなるようで厳しかったりします。
EXISTSかサブクエリの外で内部結合
テーブルを作るとき、キーにしにくいデータの場合は オートナンバーを使ったほうがいいでしょうか? それてもオートナンバーのような番号を自分で作って キーにしたほうがいいでしょうか? データは削除や追加を頻繁に行う可能性があります。
>>466 キーにしにくいデータっていうのは
きかない表現だけど、どういうこと
ですか?
タイムスタンプを入れないとユニーク(主キー)にならない系かの??
履歴とかログの類ならそうだね 普通にシーケンスやBIGSERIALを使えばおk
追加、削除が頻繁に起こるってことはログ関連じゃないだろう。 代替キー(サロゲートキー)は複合キーをまとめるために使うこともあるから具体的に例を出してくれ。
ぶっちゃけると予定表です。 重複というか交差してはいけないものが開始日時と終了日時くらいで 他の項目は重複するのが前提になっています。 逆にこういう設計がいいってのがあれば教えていただきたい。
予定表であれば予定表に書き込む予定イベント毎にオートナンバー 等でユニークなIDを振った方が良いような。 >重複というか交差してはいけないものが開始日時と終了日時くらい これが仕様として本当なのかもじっくり検討した方が良いと思う。
・使用FB derby 10.5 ・テーブルデータ 受注表 ---------------- 顧客名 | 受注個数 ---------------- あああ | 100 いいい | 200 ううう | 100 えええ | 300 ・欲しい結果 結果 ------ 顧客名 ------ いいい えええ ・説明 受注個数150以上の顧客名を取得 これを、ストアドプロシージャ使ってやりたいがストアドの作成、使用方法が分かりません。 手元にORACLEの資料がありとりあえず試してみるもderbyでは動作しなく、ネットを探し回ったのですが 手掛かりが見つからず手詰まり状態です。 分かる方大変お手数ですが、助けてください。 また、ココ読んどけ見たいなサイトあったら教えてください。
まずDBをOracleにします
>>472 やっぱりオートナンバー等を使ったほうがいいんですね。
その方向で検討して次のような疑問が出ました。
1、IDENTITYを使った場合、自動で割り振られた値をすぐに取得
する方法はあるでしょうか?
2、自分で割り振る場合
select MAX(ID) + 1 from table
とするとデータが無い場合NULLが帰ってくるようですが
SQL内でNULLであれば特定の値を返すようにするにはどのように
書けばいいでしょうか?
(出来るようなら許容最大値の検出も考えています)
3、頻繁な追加と削除が考えられるので最終手段で番号の
再割り当てを用意したいのですがオートナンバー、もしくは自分で
割り振った番号を連番にするのにいい書き方はあるでしょうか。
>これが仕様として本当なのかもじっくり検討した方が良いと思う。
これは具体的にどのような問題が考えられるのでしょうか。
まだ検討中ですが、予定表としてそれでいいのかという事でしたら
そういう使用を考えています。
あ。スレ間違えた。SQL Server スレと勘違い。ごめんなさい・・・。
1はDBによる。先にナンバーを予約してそれを使ってINSERtするケースもある。 2はCOALESCEを使う、、でいいのかな? 3は運用によるんじゃない?別テーブルでオートナンバーと連番管理してもいいし
>>475 1.sqlserverならoutput句が便利。identityじゃなくても使える。
2.データが無い時ようにunion allでselect 1 を加える。許容最大値はsqlでやることじゃない。
3.再割り当てをする必要性は?オートナンバーは表にでない番号だから飛び番号でかまわないと思う。
472ではないけど予定がかぶっちゃいけないっておかしくね?
「予定」の定義にもよるだろうけどさ。
480 :
475 :2010/10/05(火) 16:05:47 ID:???
>>476-479 回答ありがとうございます。
1について
@@IDENTITY があったんですね。変数っぽいのは関数内
だけかと思ってました。
outputは色々応用出来そうなのでoutputで検討しています
2について
COALESCEのSQL Server用はISNULLのようでしたので
こちらを使わせていただきます。
unionは使ったこと無かったのですがこんな感じでしょうか?
select MAX(ID) + 1 from (select ID from table union all select 1)
3について
確かに表に出さなくて連番も気にしていなかったのですが、
例えばintの最大値に近づいてきた時に連番に再割り当てして
新規に登録できる番号を増やす手段を用意しておいたほうが
いいのではと考えていました。
ちなみにこのオートナンバーはそのテーブルのみで使用し
他のテーブルには影響しません。
また疑問が出たら書かせてもらいます。
ありがとうございました。
intなら2,147,483,647、bigintなら9,223,372,036,854,775,807まで連番に使える。 うちの使い方じゃ困ったことはないな。
intが枯渇する場面には割と出くわすとおもう。 bigintはまだぶつかったことないなw
unsignedが指定できればさらに倍。
はらたいらさんに3000点
postgresqlです。副問合せの結果を2つの列の条件にするには どうしたらよいでしょうか? SELECT * FROM TABLE_A WHERE [TABLE_Aの列1または列2] IN (SELECT ID FROM TABLE_B WHERE NAME LIKE '%あ')
WHERE (SELECT ID FROM TABLE_B WHERE NAME LIKE '%あ') IN (TABLE_A.列1, TABLE_A.列2)
updateのwhere in句でワイルカードを使いたいのですが、 mysqlで、 UPDATE table SET col='新しいデータ' where col in ('001古%', '003古%'.) としたのですが、 上手く出来ませんでした。 どうしたら出来るのでしょうか。
where col like '001古%' or col like '003古%'
SQLite3を使用しています。 select distinct(hoge) でhogeのユニークな値一覧が取得できますが、 この結果の数を得る方法はないでしょうか? よろしくお願いします。
491 :
NAME IS NULL :2010/10/12(火) 09:30:56 ID:HeSOKsGB
SQLiteは使ってないから知らんけど select count(distinct(stock_code)) は?
492 :
491 :2010/10/12(火) 09:31:44 ID:HeSOKsGB
ごめん 中のstock_codeは忘れてくれ w
group byで項目を括って、そのレコード数をcount()で出しています。 select 項目,count(項目) from table group by 項目; この時、count()の最大数のものが、幾つか出したいのですが、 maxなどを試しても上手くいきませんでした。 どうすれば、最大数だけを出せますか。
日本語でOK
having とかの話では?
項目の値の集計値でなら、havingを使った事があるのですが、 この場合は、よく分かりません。 どうすればいいのか、教えて頂けないでしょうか。
何がしたいのか、きちんと日本語で説明するなり、 例を挙げるなりしろ、ってことですよ。
もしかして1位タイが何組あるかってこと?
499 :
490 :2010/10/12(火) 14:44:37 ID:???
>>491 それだと、何故か
near "distinct": syntax error
が出てしまいます。
No-色 レコード1 001,赤 レコード2 002,赤 レコード3 003,青 レコード4 004,青 レコード5, 005,赤 レコード6, 006,黒 このデータで、 『select 色,count(色 from テーブル group by 色 』とした時 最大レコード数(この場合、赤の5)を取得したいのですが・・・
赤の5ってことは、countが最大のものの中でレコード番号が最大のものってこと?
502 :
NAME IS NULL :2010/10/12(火) 22:33:15 ID:8RPVSXuH
元データの日付(スケジュールとかログの日付)を全件検索するとします。 その時、「その時刻より後の18時」を取得(生成)するうまい方法はあるでしょうか。 言い換えると、日付の繰り上がりに対して小技があったら教えてください。 ターゲットデータベースはH2Databaseですが、「Oracleならこんな方法が(ry」とかでも 勉強になりますのでお願いします。 元データ ------------------- 2010-10-01 01:00:00 2010-10-03 17:00:00 2010-10-04 20:00:00 ------------------- 欲しいデータ ------------------- 2010-10-01 01:00:00 2010-10-01 18:00:00 2010-10-03 17:00:00 2010-10-03 18:00:00 2010-10-04 20:00:00 2010-10-05 18:00:00 -------------------
時刻だけ取り出して、18時より小さかったら18時に。大きかったら +1日して18時にすればいい。
とりあえず6時間足す → 日付単位にtrunc → 18時間足す でいいんじゃね?
>>502 原理的には、時刻を取り出し、
(対象時刻 - 元データ時刻 + 24時間)/ 24時間の剰余を計算し、元データに足す。
例示されたデータの場合
mod((18時 - 01時 + 24時), 24時) = +17時間
mod((18時 - 17時 + 24時), 24時) = +01時間
mod((18時 - 20時 + 24時), 24時) = +22時間
ただし、時刻間の剰余を求めることはできなさそうなので、時刻 <-> 数値に変換する関数を用意する必要はありそう。
>>503-505 ありがとう。大変参考になりました。やはり基本は腕力演算ですかね・・。
なんとか効率よい方法を探ってみます。
日付に+18時して元データと比較の結果で、+1日するかしないか決めればいい
>>508 Q1〜Q3合ってる。Q4 150。
宿題は自分でやろう。これくらいのテーブル自分でCREATEして確認すればいいじゃん。大学生かい。
ありがとうございます どうでもいい話でしょうがこういった知識はほぼ0に等しく 1からググって解いていったのですがQ4,150を入力しても正解にならなかったので 詳しい知人もいませんしこちらで質問させてもらいました 問題側がおかしかったんですね
ググって解いて身に付くもんなのか?
512 :
NAME IS NULL :2010/10/28(木) 20:37:12 ID:q6JO7OhN
いまどきのオプティマイザなら、 ・WHERE hoge = 999 ・WHERE hoge IN ( 999 ) -- INを使ってるくせに値が1個 は同じ速度で検索してくれますか? ちなみにMySql5です。
知らん。実行計画見れ
514 :
NAME IS NULL :2010/11/02(火) 04:49:23 ID:SseX3HDC
「10機の赤い飛行機」「赤い飛行機」「白い飛行機」「飛行機が黄色い」「飛行機の音」という5項目があるとして 前者4つは「飛行機」というキーワードで検索できるようにしたいです LIKE検索だと5つめもかかってしまうのですが こういう場合は検索用のテーブルを作るべきなのでしょうか? あと「10機の赤い飛行機」の他にも「●機の赤い飛行機」も同様に「飛行機」というキーワードにしたいのですが その場合検索用テーブルにいちいち1機からx機まで書くわけにもいかないですが こういう場合はどうしたらいいのでしょうか?
like '%飛行機'
>>515 だと4つめがひっかからない
>>514 は主語(?)としての「飛行機」を検索したいってことなのかな?
だとしたら自然言語処理をストアドで組むしかないんじゃないだろうか
SQLの出る幕じゃないな。
「飛行機が黄色い」とか「飛行機が黄色い飛行場」とかは? SQLじゃ厳しいし、アプリ内で単純に形態素解析ライブラリ使うとかだけじゃ解決しないねぇ。
519 :
NAME IS NULL :2010/11/02(火) 07:47:00 ID:0+zwsUdT
LIKE '%い飛行機'
520 :
514 :2010/11/02(火) 08:53:15 ID:???
みなさんレスありがとうございます 簡単にできるのかな?と思ってたら考えが甘かったようですね よくわからないことだらけでてんぱってますが なんとかアプリ側で実装するよう頑張ってみます
複数のLIKEをAND/OR/NOTでつないで見る程度じゃだめなのかな
別途キーワードだけを格納するテーブル(かカラム)を用意するのが簡単じゃないかな まあ、キーワードをどうやって選定するかって問題は残るが
523 :
490 :2010/11/02(火) 14:40:15 ID:???
SQLite2を使用していたようです。。 3に変更したところ、count(distinct(hoge))が使えました。 一応報告です。
ハハハ 和む
~ '[^音]*飛行機[^音]*';
>>514 の言う「前者4つ」と「5つめ」は何が違うのかがわからない
528 :
NAME IS NULL :2010/11/03(水) 03:51:40 ID:S1vOLRbk
Webアプリケーションで使う仮登録テーブルと本登録テーブルがあります 仮登録テーブルは誰でもINSERTできるようになっていて 仮登録テーブルに内容があった場合 本登録するスクリプトを数時間おきに叩くようなシステムになっています ここで仮登録テーブルは複数人がINSERTのみを行う(DELETEやUPDATEは行わない) 本登録テーブルはスクリプトが数時間おきに仮登録テーブルの内容をINSERTをする 本登録テーブルの内容に不備があった場合管理スクリプトからDELETEやUPDATEを行えることになっています こういう場合どちらともトランザクション処理は必要でしょうか?
529 :
528 :2010/11/03(水) 03:56:34 ID:???
すみません一応補足しておきます 仮登録テーブルは本登録テーブルに内容をうつした後 仮登録テーブルの内容はすべてDELETEされます
それ、SQLの質問と違うやん。
ACCESS SQLで以下のような処理はできるでしょうか? (VBAの機能等を含めないSQLの処理結果のみで) 【テーブル】 Tくだもの(名前, 名前) Tアンケート(名前, 評価) ←評価には1〜5の数値が入る 【結果】 みかん, 1, 3, 5, 3, 4, ... (以降アンケートの数だけ続く)
>>529 普通に考えて、本登録するスクリプトは1トランザクションで処理すべきだろう
微妙にトランザクションを勘違いしてる気がするんだが
お前の言うどちらともの、どちらって、何と何を指してるんだ?
>>528 トランザクションは一連の処理をひとまとまりで不可分なものとしたいときに使う。
例えば一連の処理が終わるまで対象データを他のトランザクションから
変更できなくしたいとか、途中で処理が失敗したらまとめてROLLBACKしたいとか。
535 :
528 :2010/11/03(水) 17:45:10 ID:???
レスありがとうございます トランザクションを微妙に勘違いしてるというのはそうなのかもしれません 処理をひとまとまりというのはなんとなく理解しているのですが トランザクション処理とデッドロックのテーマをよく取り扱っているので (副次的効果で?)更新中に他の人は更新できないようにロックする処理みたいな認識でした 仮登録テーブルから本登録テーブルにデータを移行して仮登録テーブルからデータを削除する というのが1トランザクションだと思うのですが この間に他者が仮登録テーブルに登録できないようにするにはトランザクションを使うのかなと思いました データベースはMySQLを使っていてトランザクション処理はInnoDBにする必要があるみたいなので どちらのテーブル(あるいは両方)をInnoDBにする必要があるのか悩んで質問しました
スレ違いだからさっさと消えてなくなれよ
こんな過疎スレでスレ違いがどうのわめくやつって一体・・・
538 :
NAME IS NULL :2010/11/03(水) 18:09:14 ID:3Da48fOF
過疎であることとスレ違いが許容されることの関係を解説希望
過疎スレ自治お疲れ様です^^;
>>532 ありがとうございます
どうやらこのスレでも過去似たような質問が何度かあったようで
MySQLだとGROUP_CONCATというのがあって簡単にできるらしいので
エクスポートして試してみます
Accessならクロス集計で何とかならん?
H2Databaseにもあってすげー助かった>GROUP_CONCAT 他DBにも採用されてるのかな。
543 :
NAME IS NULL :2010/11/04(木) 03:15:57 ID:T5HL3QI9
NULLの扱いが難しすぎるんだけど、 なんかいい方法ないの? (1) trim(' ') = trim('a') → false(null) (2) trim(' ') = trim(' ') → false(null) (3) null = trim(' ') → false(null) 少なくとも(2)(3)はtrueで評価してくれないと、 バグの温床になりかねん。
null は = で比較できない。
DBはなんだ。Oracleか?
>>543 DBの種類も書かんといきなり関数書かれてもな...
トリム結果が空文字列ならNULL返すって前提か?
NULLと何かの比較が真になるようではNULLの意味がないんだがな
扱いが難しいのはまあしょうがない。ちゃんと理解して扱えとしか
1と2は空文字列とNULLを区別しないDBMSが悪い(たぶんORACLEなんだろうけど)
3は真でも偽でもない。falseとNULLは違うってことから理解しなおせ
(1と2も結果的にNULL=NULLの比較だから結果は不定だがな)
3値論理だからな ところで TRUE:真,FALSE:偽だけどNULLを感じ文字で表すなら何が適当なのだろうか 不(定)?未(定義)?
虚
仏教的には「空」だな。 まあ仏教は関係ないけど。
「未知(unknown)」の場合と「適用不能(not applicable)」の場合があるから 漢字一字じゃ無理でしょ
それを言ったら、それを"NULL"と表していること自体に無理があるわけで。 アルファベット4文字で表すのは可能だけど漢字1文字は無理、ってことはないだろう。
T、F、Nの3値でいいでしょ。
無
もう禅の話ですか
膳々
556 :
NAME IS NULL :2010/11/05(金) 15:44:09 ID:nrM4M2fD
sqlite3ですの id 1 name 伊藤 age 23 id 2 name 田中 age 24 id 3 name 山田 age 33 id 4 name 桜庭 age 43 id 5 name 中村 age 23 を「age」の数値が多い順にソートしたいんですが良い方法ありますか? あと「伊藤」と「中村」は「age」が一緒なんですけど その場合はどうなりますか?
sqliteを俺は知らんけど、order by 使えないの?
TRUE:真,FALSE:偽,NULL:ぽ でいいよ
559 :
NAME IS NULL :2010/11/05(金) 18:31:06 ID:ElB5Tk2d
sqlitemanager使ってるんですが 調子わるい 文字コードいじりやすいやつで他にいいソフトありますか?
Python+wxあたりで簡単に自作できそうだけどな
NULLは無だと思ってました
検索したい文字列より検索キーワードが多いけどだいたいあってるからHITさせたいみたいなことは可能ですか? 例えば「にしむらひろゆき」という文字列にたいしてLIKEで'%ひろゆき%'はHITしますが、 「2ちゃんねる元管理人にしむらひろゆきさん」というキーワードで この「にしむらひろゆき」にSQLだけでHITさせることは可能でしょうか?
where '2ちゃんねる元管理人にしむらひろゆきさん' like '%' || col_name || '%' でいけるDBMSならいける
564 :
562 :2010/11/05(金) 21:10:30 ID:???
>>563 ありがとうございます
試してみたところ無理だったのでMySQLではだめなようですね
結構無茶ぶりのつもりでしたができるDBMSもあるということに感動しました
MySQLでもできたよ ||じゃなくconcatを使う。あんまMySQL詳しくないけど||は効かないんじゃね wordというカラムに'ひろゆき', '管理人', 'aaaaaaa'ってのを入れて select * from word_table where '2ちゃんねる元管理人にしむらひろゆきさん' like concat('%', word, '%'); で二行引っ掛かる。
Olacleで、以下の<Table1>と<Table2>から<Table3>を作り出すSQLは どのように記述できますでしょうか?ご教示願います。 <Table1> 製品コード 部品コード XXXX A YYYY B ZZZZ C1 <Table2> 部品コード 部品 A A1 A A2 B B1 B B2 <Table3> 製品コード 部品 XXXX A1 XXXX A2 YYYY B1 YYYY B2 ZZZZ C1
文字が詰まって見えるので再投稿します。 Olacleで、以下の<Table1>と<Table2>から<Table3>を作り出すSQLは どのように記述できますでしょうか?ご教示願います。 <Table1> 製品コード 部品コード XXXX A YYYY B ZZZZ C1 <Table2> 部品コード 部品 A A1 A A2 B B1 B B2 <Table3> 製品コード 部品 XXXX A1 XXXX A2 YYYY B1 YYYY B2 ZZZZ C1
Left Join
select T1.製品コード, coalesce(T2.部品, T1.部品コード) as 部品 from Table1 as T1 left outer join Table2 as T2 on T1.部品コード = T2.部品コード ;
>>569 実行できました! ありがとうございます。
SQLite3 です。 a-1 b-2 a-2 b-10 a-10 b-9 が a-1 a-2 a-10 b-2 b-9 b-10 になるようにソートする方法はありますか? ハイフンの後ろは必ず数値なんですが、 a や b の部分の文字数は不定です。
572 :
NAME IS NULL :2010/11/06(土) 17:09:36 ID:shC/G7aL
「複数のcnt[カラム]ごと」のところが言ってることがいまいちわからないが・・・ 検索履歴を保持したいなら検索履歴テーブルでも別に作って貯めていく方が普通じゃね。 データのあるテーブルにカラムを追加する実装はない。 検索履歴テーブルへのINSERTは、SELECTの直前での履歴INSERTの作り込みか、 あるいは(sqlite3で出来るかは知らないが)、トリガーを使う。
>>572 こういうこと?
SELECT COUNT(col1), COUNT(cnt2) FROM Table GROUP BY col1,col2
576 :
562 :2010/11/06(土) 23:23:16 ID:???
>>565 遅レスですがありがとう!
これでだいぶ実装が楽になってがんがん先にすすめます
ほんとうれしいわー
rtrim(x,y)なんてあるのか
>>574 はい、カラムを分けずに実現したいです。。
URL が不明ですが、SQLite で正規表現が使えるんですか?
>>577 ltrim(ltrim(ltrim(ltrim(col,'a'),'b'),'c'), ... '-') とかでしょうか。
でもこれだと abab-1 などの時、最初の ab しか消えませんよね。
あと TEXT 型なので、単純にハイフンの右側でソートするだけでは
'10' < '2' なので上手くいきません。。
やはり無理がありますよね。ありがとうございました。
リンク先をよく読んだわけじゃないから間違ってるかもしれんが 「ltrim(X,Y)」は「Y」にあらわれる文字の一部または全てを左端から除いた文字列を返すらしいし 「abs」を使えば文字列を数値化できるから(詳細はリンク先参照) abs(ltrim(col, 'abc-')) でいけるんじゃないか?
>>580 trim の使い方を勘違いしてました。。
abs で数値化も勉強になります。
ハイフンの左側には数字も出現するので、最終的にこんな order by 句になりました。これで勝つる。
rtrim(col, '0123456789'), abs(ltrim(lower(col), 'abcdefghijklmnopqrstuvwxyz0123456789'))
582 :
NAME IS NULL :2010/11/08(月) 06:51:16 ID:h0nca0n6
すいません、全フィールドに(2語の検索語でANDもしくはORで)検索をかけたい場合 どうかくのでしょうか?mysql でPHP で書いています。 (2語をANDで検索したい場合) SELECT * FROM table WHERE name LIKE '%$word1%' AND name LIKE '%$word2%' SELECT * FROM table WHERE address LIKE '%$word1%' AND address LIKE '%$word2%' SELECT * FROM table WHERE tel LIKE '%$word1%' AND tel LIKE '%$word2%' ・ ・ みたいに全フィールド分並べるとダブりがあるし SELECT * FROM table WHERE * LIKE '%$word1%' AND * LIKE '%$word2%' とやったら怒られました
UNION
584 :
NAME IS NULL :2010/11/08(月) 07:15:23 ID:h0nca0n6
おお、早朝から早速どうもです、ありがとうございます UNIONで上手くいってるようです やっぱ全部のフィールド分は書かないとダメなんですね
WHERE (a AND b) OR (c AND d) OR ... みたいな書き方はできないの?
586 :
NAME IS NULL :2010/11/08(月) 19:44:49 ID:cV+BOk7+
UNIONのが早いからUNIONにする
下のようなカラムがあるのですが、value1〜3に1回でも出てくる値を 重複なく取得したいのですが、うまくSQLで書けるものでしょうか。 key, value1, valu2, value3(value1〜3は任意の文字列) たとえば、3個のレコード 1, AAA, BBB, CCC 2, DDD, AAA, CCC 3, EEE, FFF, AAA があった場合、AAA, BBB, CCC, DDD, EEE, FFFを出力したいです。
>>587 SELECT value1 FROM Table
UNION
SELECT value2 FROM Table
UNION
SELECT value3 FROM Table;
>>588 ありがとうございます。
UNIONは個人的に馴染みがなく、忘れていました。
確かに、ほとんどUNIONの定義そのままですね・・
590 :
NAME IS NULL :2010/11/09(火) 18:04:38 ID:is9svjYB
似たような質問なんですが すべてのカラムに対してそれぞれではなく、複数まとめて検索したい場合は どうするのでしょうか? イメージ的には複数のフィールドを取り出してくっつけて1文にして それにXXX and YYY で検索する感じです 実はすごく簡単なような気もするんですが 全部取り出してくっつけたものを、この検索のためのフィールドに入れておく くらいしか思いつきません name address tel ・・・・ ------------------------------ itouYYY XXXZZZ 0123-4567 itouZZZ YYYYFF 1234-0123 hanaXXX LLLZZZ XXXX-XXXX で、(itouYYY XXXZZZ 0123-4567 …)が欲しい
591 :
NAME IS NULL :2010/11/09(火) 20:35:00 ID:hV+oLYCU
>>590 何も考えずに項目毎にandで結べばいいだろ
where 項目A = 'xxx'
and 項目B = 'yyy'
and 項目C = 'zzz'
それともこういう話か
(項目A,項目B,項目C) = ((値1,値2,値3),(値4,値5,値6))
そいうのができるDBもあるぞ
592 :
NAME IS NULL :2010/11/09(火) 21:13:57 ID:is9svjYB
ええと、たとえば名前の苗字と住所の一部が分かってるときとかに検索したい時に 神奈川在住の千葉さんだとして 名前から「千葉」、住所から「神奈川」とやらなくても 複数のフィールドに「千葉、神奈川」がある人を見つけたいんです 全文検索みたいな感じでしょうか この例だと、千葉と神奈川をひっくり返して試せばいいだけですが 本当は項目がもっと多いので
SQLに向いてない。
>>592 複数のフィールドを連結して、正規表現で検索とか。
時間はかかるだろうけどね。
595 :
NAME IS NULL :2010/11/09(火) 23:24:35 ID:is9svjYB
うーん、してみると 取り出して1文にしてstrstrしちゃったほうが早いすかね?
・SQL Server 2008 行 | 品名 | 個数 --+-----+---- 1 | 商品1 | 2 2 | 商品2 | 1 3 | 商品3 | 1 4 | 商品4 | 5 上記のようなテーブルがあるんだけど、下記のように個数の累計を取得したいです 行 | 品名 | 個数 | 累計 --+-----+---- +----- 1 | 商品1 | 2 | 2 2 | 商品2 | 1 | 3 3 | 商品3 | 1 | 4 4 | 商品4 | 5 | 9 SUM使ってCASEでわけて合計すればよさげだけどうまく書けません 教えてください
自分と同じか、小さい行の個数をSumすればいいな。
598 :
NAME IS NULL :2010/11/10(水) 22:34:41 ID:tSI0e5gG
_
>>597 やりたいことはそのとおりわかってるんだけど、どう書けばいいかわからないのです
考えてたのは
1.
SUM(CASE WHEN 行<=行 THEN 個数 )ELSE 0 END
2.
SELECT *
FROM
(
SELECT *,
累計=SUM(個数)
FROM Table
WHERE 行<=行
) X
難しいです
select 行, 品名, 個数, (select sum(個数) from table where 行 <= t2.行) as 累計 from table t2 とかじゃダメ?
600 :
596 :2010/11/10(水) 23:09:51 ID:???
>>599 自分の行とテーブル全体の行を比べるにはそういう書き方があるんですね
勉強になります
いま実行環境がないのですが、明日確認できればまた御礼にきます
間違ってたらお礼言わない気かよw
602 :
596 :2010/11/13(土) 07:14:10 ID:???
遅くなりましたがうまくできました みなさんありがとうございます
サーバにSQLiteファイルアップロード ローカルでSQLiteファイル更新(INSERTでレコード追加) としたとき、SQLファイルはリジュームアップロード可能でしょうか? それとも一からアップロードし直すことになるのでしょうか?
スレ違い。
605 :
NAME IS NULL :2010/11/14(日) 20:17:14 ID:W9EfAQ77
ありがとうございます。移動します。
MySQLServer5.1(WinXP)です。 下記のような3種類のテーブルがあります。 これを集計して、指定したユーザーの指定月のアクセス日時と売上げ金額を表にしたいと思っています。 こんな感じに。 +--+--------+----------+-------+-----+ | id | username| date | access | sales | +--+--------+----------+-------+-----+ | 1 | admin | 2010-11-13 | 2 | 5000 | | 1 | admin | 2010-11-14 | 2 | 5000 | | 1 | admin | 2010-11-15 | 1 | 20000 | +--+--------+----------+-------+-----+ SELECT u.id AS id, u.username, DATE(a.created_at) AS date, COUNT(*) AS access, SUM(s.amount) AS sales FROM sf_guard_user u INNER JOIN sales s ON u.username = s.user_id LEFT JOIN access_log a ON u.username = a.user_id WHERE (u.username = 'admin' AND a.created_at > '2010-11-01 00:00:00' AND a.created_at < '2010-11-30 23:59:59') GROUP BY date ORDER BY a.created_at; とやってみたのですがダメでした;; ■ユーザー情報テーブル mysql> select id,username from user; +--+---------+ | id | username | +--+---------+ | 1 | admin | +--+---------+ ■アクセスログテーブル mysql> select * from access_log; +--+-------+--------+------------------+ | id | user_id | ip | created_at | +--+-------+--------+------------------+ | 1 | admin | 127.0.0.1 | 2010-11-13 21:56:54 | | 2 | admin | 127.0.0.1 | 2010-11-13 21:56:54 | | 3 | admin | 127.0.0.1 | 2010-11-14 21:56:54 | | 4 | admin | 127.0.0.1 | 2010-11-14 21:56:54 | | 5 | admin | 127.0.0.1 | 2010-11-15 21:56:54 | +--+-------+--------+------------------+ ■売上げ金額テーブル mysql> select * from sales; +--+-------+------+------------------+ | id | user_id | amount| created_at | +--+-------+------+------------------+ | 1 | admin | 5000 | 2010-11-13 21:56:54 | | 2 | admin | 5000 | 2010-11-14 21:56:54 | | 3 | admin | 5000 | 2010-11-15 21:56:54 | | 4 | admin | 5000 | 2010-11-15 21:56:54 | +--+-------+------+------------------+
GROUP BY u.id, u.username, DATE(a.created_at) にすればいいだけじゃないの?
u.id, DATE(a.created_at) でいいとおもう
ごめん追記。 アクセス、売り上げの無い日付があったときにどう表示するかでも変わってくるので 「ダメでした」の内容をお願いします。
611 :
607 :2010/11/15(月) 16:26:25 ID:S1UCx8kr
ダメだったときの結果は以下のような感じです。 書いてなかったですが、アクセス数や売上げの無い日もありえます。 無い場合はnullでも0でも構いません。 +---+--------+------------+-------+-------+ | id|username| date | access| sales | +---+--------+------------+-------+-------+ | 1 | admin | 2010-11-13 | 10 | 50000 | | 1 | admin | 2010-11-14 | 10 | 50000 | | 1 | admin | 2010-11-15 | 5 | 25000 | +---+--------+------------+-------+-------+
アクセスログの結合条件に日時も加えないとダメだろう LEFT JOIN access_log a ON u.username = a.user_id AND s.created_at = a.created_at
と思ったけどやっぱ別々に集計しなきゃダメだな SELECT u.id, u.username, s.date, a.access, s.sales FROM sf_guard_user u INNER JOIN (SELECT user_id, DATE(created_at) AS date, SUM(amount) AS sales FROM sales GROUP BY user_id, DATE(created_at)) s ON u.username = s.user_id LEFT JOIN (SELECT user_id, DATE(created_at) AS date, count(*) AS access FROM access_log GROUP BY user_id, DATE(created_at)) a ON u.username = a.user_id AND s.date = a.date WHERE u.username = 'admin' AND s.date > '2010-11-01 00:00:00' AND s.date < '2010-12-01 00:00:00' ORDER BY s.date ;
ああ、ほんとだ。 別々に集計しないとだめね。 適当なレスしてすまんかった。 結合は全部left outerがいいとおもう。 売り上げないけどアクセスあった日、またその逆を出す為に。
あ、日付テーブルのようなものがいるのでは。
616 :
607 :2010/11/15(月) 20:31:28 ID:S1UCx8kr
うわぁ。。。こんな複雑なSQL、自分じゃ思いつかないです>< でもエラーになっちゃいました。。。 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEC T u.id, u.username, s.date, a.access, s.sa' at line 1
617 :
607 :2010/11/15(月) 20:43:06 ID:S1UCx8kr
あ、全角スペースが原因だったみたいです。。 半角に直してもまだエラーでちゃいますけど。。 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELE CT user_id, DATE(created_at) AS date, SUM(amount) AS sales FROM sales GROUP' at line 8
618 :
607 :2010/11/15(月) 20:47:41 ID:S1UCx8kr
LEFT OUTER って、LEFT OUTER JOINのことだったんですね^^; 修正したらうまくいきました!! 感謝です。
ダラダラと長いだけで複雑なわけじゃないから頑張って嫁としか
くだ質で申し訳ない SQLite3で、textの1カラムしかないレコードがずらずらと続いているテーブル のn行目からm件のレコードを取得するにはどうSQLに書き下せばいいんでしょうか? select * from table limit (n+m); で目的範囲の末までは取得できるのですが、で きればDBの処理で最初のnレコードも省きたいです。
つ offset てかそんなテーブル、RDBMSというものの意味を全く分かってないな order byをつけないと順序は保証されないよ
使い道が無いわけでは無いと思うけどね。 キーワード一覧とか。
いや、IDもタイムスタンプもないんじゃダメだろ・・。
そうかねぇ。 select * from access_log where exists( select * from ignore_ip_addresses where access_log.client_ip = ignore_ip_addresses ) とか。無視リストにIDやタイムスタンプがあったらどうなるの?
なんか既視感が、とおもったら、
>>565 がそんな感じにもなれる、けど、IDとタイムスタンプがあるとどうなるの?
mysql 5.0 (windows 7)です 下記のようなテーブルで amountの合計の最も多いidを取得するsql文がわかりません… select id, sum(amount) as "total" from orders group by id order by total desc limit 1; とすれば一応取得できたのですが、別の方法はないでしょうか 初歩的な質問ですみません… +------+------+--------+ | id | name | amount | +------+------+--------+ | 1 | C1 | 3 | | 1 | C2 | 10 | | 2 | C2 | 5 | | 2 | C3 | 10 | | 3 | C3 | 2 | +------+------+--------+
>>627 動いてるならそれでいいと思うが、別の方法というなら
mysqlで動くかどうかわからんが
select id from orders group by id having sum(amount)=(
select max(total) from (select sum(amount) as total from orders group by id) t
)
みたいなSQLを書いてみた
>>628 ありがとうございます。無事動きました
私もSQLをもう少し勉強します。
SQLSERVER2005を使用しています。 次のようなストアドを作成しています。 procedure A @No int delete from TABLE where No = @No insert into TABLE values (@No, a, b, c) ※TABLEのPKEYは@No @Noに同一値が指定されるケースもあります。 ここでprocedureAがマルチで短時間に何回も実行される場合、タイミングによってはinsertでキー重複が発生します。 procedureの実行中はテーブルロックをかけたいのですが、何か良い方法はありますか?
トランザクションかければいいだけじゃね?
>>630 微妙にスレ違いな気もするけど
テーブルロックかけたければかければ良いんじゃね
やり方はMSDN読むなりググルなりすればわかるでしょ
つかまずトランザクションと分離レベルについて勉強したほうがいいんじゃないかと
633 :
NAME IS NULL :2010/11/21(日) 05:33:51 ID:RG9/i762
その様子だと、分離レベルはデフォルトのREAD_COMMITTED? だったら delete from TABLE where No = @No insert into TABLE values (@No, a, b, c) にトランザクションかければOK
mysql5.1.46 version_noというカラムがあり,そこには 例えば 95/98/2000/〜 2000/2002/〜 といった文字列が格納されています。 このカラムの文字列を見て '95'から始まるときは1 '97'から始まるときは2 '98'から始まるときは3 '2000'から始まるときは4 '2002'から始まるときは5 '2003'から始まるときは6 '2007'から始まるときは7 '2010'から始まるときは8 という数値をverというカラムにセットしたくて update datatbl case left(version_no,locate('/',version_no) -1) when '95' set ver = 1 when '97' set ver = 2 when '98' set ver = 3 when '2000' set ver = 4 when '2002' set ver = 5 when '2003' set ver = 6 when '2007' set ver = 7 when '2010' set ver = 8; というのを作ったんですが,エラーになっています select id,version_no from datatbl where left(version_no,locate('/',version_no) -1) in ('95','98','97','2000'); は動くのですが、updateの時はどう書けばよろしいのでしょうか?
宜しくお願いします。 ・DBMS名とバージョン Oracle10g ・テーブルデータ SALES +------------+------+ | 日時 | 数量 | +------------+------+ | 2010/03/01 | 1 | | 2010/03/02 | 1 | | 2010/03/23 | 2 | | 2010/03/24 | 1 | | 2010/04/05 | 5 | | 2010/04/05 | 2 | +------------+------+ ・欲しい結果 テーブルから週毎(月曜基準)に合算した数量を取得したい。 但しデータがない週も0で表示したい。 ・説明 週毎に合算した数量は以下のSQLで取得できましたが、 データがない週も表示する方法がわかりません。 SELECT NEXT_DAY(TO_DATE(日時,'yyyy/mm/dd'),2)-7 AS 日時, SUM(数量) FROM SALES GROUP BY NEXT_DAY(TO_DATE(日時,'yyyy/mm/dd'),2)-7; 宜しくお願い致します。
>>634 SET ver = CASE case left(version_no,locate('/',version_no) -1) WHEN '95' THEN 1 WHEN '97' THEN 2 ...
とすべきじゃないかな。
>>635 無い週を表示するには、別途カレンダーテーブルか、数列を生成するテーブル関数を用いて
結合させないと無理。
すまんCACEが被った SET ver = CASE left(version_no,locate('/',version_no) -1) WHEN '95' THEN 1 WHEN '97' THEN 2 ...
>>636 ありがとうございます。
update vba
set ver =
case
when left(version_no,locate('/',version_no) -1) = '95'
then 1
when left(version_no,locate('/',version_no) -1) = '97'
then 2
when left(version_no,locate('/',version_no) -1) = '98'
then 3
when left(version_no,locate('/',version_no) -1) = '2000'
then 4
when left(version_no,locate('/',version_no) -1) = '2002'
then 5
when left(version_no,locate('/',version_no) -1) = '2003'
then 6
when left(version_no,locate('/',version_no) -1) = '2007'
then 7
when left(version_no,locate('/',version_no) -1) = '2010'
then 8
else
0 end;
で出来ました
>>636 ありがとうございました。
PGMで制御する事にします。
>>640 失礼しました。
過去ログにて非推奨を確認しましたので、
SQL後のPRGで制御する事にします。
ありがとうございました。
+---+------+----+ | id|parent|name| +---+------+----+ | 1 | NULL |taro| | 2 | 1 |jiro| | 3 | 1 |sabu| | 4 | 2 |siro| | 5 | 3 |hana| +---+------+----+ 上記のようなユーザーID(id)とその親情報(parent)のテーブルで、 指定したIDの子と孫の一覧を取得するにはどうすればいいですか? 1の場合、jiro、sabu、siroを取得したいです。 子と孫の総数だけでもかまいません。
すいません、hanaも孫でした。。。
SELECT * FROM <table> WHERE parent=<id> -- 自分を親に持つ=子 UNION SELECT * FROM <table> WHERE parent=(SELECT id FROM <table> WHERE parent=<id>) -- 子を親に持つ=孫
>>642 孫までで良いなら、子の検索と孫の検索をunion allすればいいんじゃね
単純にnameだけ一覧するならたぶんこんなSQLで行けるかと
select name from テーブル where parent=1
union all
select name from テーブル where parent in (
select id from テーブル where parent=1)
孫の子も、その子も、その子の子も...っていうなら、
再帰SQL使える環境じゃないとちょっと厳しいかな
646 :
645 :2010/11/27(土) 06:53:38 ID:???
かぶったw
悔しいから
>>644 のダメ出ししとこう
UNIONだと(別IDで)同一のnameがいた場合に件数が減るからunion allじゃないとダメ
孫の検索条件のparent=は、カッコ内のサブクエリが複数行返すとエラーになるからinじゃないとダメ
647 :
645 :2010/11/27(土) 06:59:58 ID:???
あ、
>>644 は*でselectしてるからUNIONの件は忘れてくれ
union allすべきとこでついついunionしちまうことが結構多いのは俺だけ?
648 :
NAME IS NULL :2010/11/28(日) 11:28:17 ID:TZKM21JF
649 :
NAME IS NULL :2010/11/30(火) 18:12:53 ID:OHRNMayz
・DBMS名とバージョン mysql5 ・テーブルデータ TABLE名 A1234 A1235 ・欲しい結果 INTEGER 1234を入力した時に SELECT * FROM A1234 の結果が INTEGER 1235を入力した時に SELECT * FROM A1235 の結果が 返る関数 よろしくお願いします。
650 :
NAME IS NULL :2010/11/30(火) 23:39:43 ID:lM4VOFvr
SQLite3で 1 | aaa 2 | bbb 3 | ccc 4 | ddd 5 | eee というテーブルから「3 ccc」を削除した時に自動で 1 | aaa 2 | bbb 3 | ddd 4 | eee みたいに修正する方法ってありますか? 他のDBMSでの方法でも構わないので宜しくお願いします。
数値nが連番かつ制約が無いと仮定して UPDATE table SET n=n-1 WHERE n>3;
652 :
NAME IS NULL :2010/12/01(水) 04:34:16 ID:Ax/gL4gh
・DBMS名とバージョン mysql5 ・テーブルデータ TABLE名 A1234 A1235 ・欲しい結果 INTEGER 1234を入力した時に SELECT * FROM A1234 の結果が INTEGER 1235を入力した時に SELECT * FROM A1235 の結果が 返るストアドプロシージャです。 用意された関数ではなくテストアドプロシージャです。 よろしくお願いします。
655 :
NAME IS NULL :2010/12/01(水) 19:33:34 ID:0WLtPE6T
DB設計はこのスレじゃダメですか? PHP+MySQLなんですが、できるだけDBで処理しようと思っています。 お買い上げに応じて、n%分を顧客にポイントを付与しています。 これにはキャンペーンが存在して、水曜と日曜はポイント5倍、 7の付く日(7日、17日、27日)もポイント5倍、1日はポイント10倍などあります。 1日が水曜日の場合は、倍率の大きい、ポイント10倍が適用されます。 また、たとえば、クリスマス期間12月01日から12月26日まではポイント「さらに」1.5倍、三が日は一律10倍などもあります。 こんな感じで、キャンペーンの曜日や期間、パーセンテージを自由に設定できるようにしたいです。 「今日はポイント何%?」というのがすぐに取得できるような形が理想です。 よろしくお願いします。
656 :
655 :2010/12/01(水) 19:43:16 ID:0WLtPE6T
現状、 ・日別キャンペーンテーブル ・曜日別キャンペーンテーブル ・期間キャンペーンテーブル とバラバラに作って、PHPで処理しています。 1か月分のパーセンテージ一覧を作るのに、1日ごとにSQLを複数実行して かなり重いので、その辺も簡略化できる設計だと助かります。
657 :
655 :2010/12/01(水) 20:21:55 ID:0WLtPE6T
自己レスだけど、こうゆう場合は カレンダーテーブル(日付、計算済み%をカラムに持つ)を作ってあげて、 各種キャンペーンテーブルを更新するたびに1か月分とかカレンダーテーブルを更新する設計がいいんだろうか? それだと、売り上げの度に%を取得するのが1回のSQLで済む。 ただし、時間帯別のキャンペーンが作れない(作りにくい)のと、設定忘れなどで、昔の期間にキャンペーンを設定するのが面倒な点が変わらない。。。
ビュー作れ
659 :
655 :2010/12/01(水) 20:46:18 ID:0WLtPE6T
どのようなビューでしょう??
曜日があるからビューはかなりムリゲーだったわ・・。
1,2,3-31の日付だけ持つダミーテーブルを作って指定月の一ヶ月の一覧を取得するSQLをまず作る。
(SQLは多少メンドクサイが)単純な計算なのでこのまま使っても重いとは思えないが、
もし重ければ
>>657 の通り、月初なりキャンペーンテーブル更新時なりに別テーブルにinsert 〜 select すりゃおk
キャンペーンごとのテーブルが必要な理由が分からない カレンダーだけ作ってバッチ更新でいいだろ
テーブル設計はスレ違いです。
>>661 そのバッチは何テーブルを見て更新するつもりだ?
>>663 そんな変なキャンペーンルールなんかバッチ側にもてばいい
>>665 例えばドトールのような不定期なキャンペーンを展開してるシステムとかどうするんだよ間抜け
毎月バッチリリースすんのかよw
水曜日のパーセンテージ変えようぜってやったら指定日以降の水曜日だけ変わってほしいよねきっと。 今曜日キャンペーンテーブルに期間もたせてるの?ないなら持たせないといけないんだよねぇ。 カレンダー的なメンテナンス画面作るのが楽だと思うなぁ。
668 :
655 :2010/12/02(木) 00:26:41 ID:u5kOJKQ0
曜日キャンペーンに期間は必要ですねぇ。 いまはまだ付けてないです。 参考にさせていただきます。 それでは、スレ違いだったようなので、別スレにて再度、投稿してきます。
MySql 5.1 です。 select句の subquery で、カラムを2つ返したいのですが、 これは不可能なのでしょうか? 何か良い方法はないでしょうか? 同じ問い合わせを、2回書くのは なしということでお願いします。 こんな感じで実現できたらベストです。 select a.name , b.date , ( select c.foo_id, c.bar_id from c where a.cond = c.cond and b.date > c.date order by c.date desc limit 1 ) from a, b where a.id = b.id
select a.name , b.date , max(c.date) from a join b using (id) left join c on (a.cond = c.cond and b.date > c.date) group by a.id, a.name, b.date かなぁ
あああごめんぜんぜんちがうね!
MySQLで動くかどうかわからん(他のDBMSでも試していないけど) SELECT a.name,b.date,c.foo_id,c.bar_id FROM a JOIN b ON a.id=b.id JOIN c ON a.cond=c.cond AND c.date = (SELECT max(date) FROM c WHERE a.cond=c.cond AND b.date > c.date);
673 :
NAME IS NULL :2010/12/02(木) 15:41:00 ID:6RsQNc/l
【質問テンプレ】 ・DBMS名とバージョン :SQLyog trial -MySQL GUI v8.71 ・欲しい結果 :正常な文字 ・説明:DBの編集をしていたのですが、DB内に「・ト」「・・ツ」「・B」と言った化けた文字が出てしまい、 まともに何が書いてあるか分かりません。ここに貼ると形が変わってしまいますが、黒塗りの「?」のような文字だったり、♀だったりします。 おそらくlatin1の文字だと思うのですが、 このように化けてしまった文字を編集する方法はありますでしょうか?数値の欄なので、これらの文字は 何かしらの数字を表していると思います。
674 :
NAME IS NULL :2010/12/02(木) 16:36:56 ID:O5ao1X4S
>>653 実際はAXXXXなので1万テーブルあるんですよ。
それを指定したいので、分岐だと厳しいです。
・DBMS名とバージョン
mysql5
・テーブルデータ
TABLE名 A1234 A1235
・欲しい結果
INTEGER 1234を入力した時に
SELECT * FROM A1234 の結果が
INTEGER 1235を入力した時に
SELECT * FROM A1235 の結果が
返るストアドプロシージャです。
677 :
673 :2010/12/02(木) 17:27:36 ID:???
失礼いたしました。
678 :
NAME IS NULL :2010/12/02(木) 22:51:37 ID:O5ao1X4S
>>675 おおー
欲しかったのはこれです。
ありがとうございます。
679 :
NAME IS NULL :2010/12/04(土) 13:11:35 ID:USyUL0Zn
mysql 5です。 ストアドプロシージャ aaaのなかで変数@bbbを定義すると @bbbが同じデータベース内でselectで呼び出せてしまうんですが これって同時にaaaの呼び出しが別のところからかかると 結果が相互干渉してしまうって事でしょうか?
知らん。スレ違い。
・DBMS名とバージョン SQLiteまたはMySQL ・テーブルデータ なし ・欲しい結果 クエリを解析して、結果に表示されるはずのカラムの一覧を取得したい ・説明 データが無いときでもカラムの一覧を取得したい。 データがあれば先頭にカラム名を出したりすることは可能だけど、 データが無いときはカラム名を取得することが出来ないので。
> データがあれば先頭にカラム名を出したりすることは可能 どうやって?
普通にINFORMATION_SCHEMAでも使え。
csvに出したり 結果リストの連想配列のキーだったり
それはSQLで出してるわけじゃない。
>>684 スキーマからじゃなくてクエリから取りたい
>>686 そうなんだよ。それをどうにかして取得したい。
無理。
>>687 取れないことをわかってて取りたいって質問するのはなんなんだ?
スレ違いだな。
ここにいる連中には聞いても無駄ってことだな
はい。無駄です。
SQLの話じゃないね。 1行だけ取得するクエリ作っとけば?
694 :
NAME IS NULL :2010/12/05(日) 09:56:40 ID:MMVSExqC
データがないと判った時点で desc テーブル名で 情報を拾うというのは駄目なん?
ダミーテーブル作ってOUTER JOINするとか create table table1 (a number, b number, c number); insert into table1 values (0,0,0); insert into table1 values (1,1,1); insert into table1 values (1,2,2); create table dummy1 (x number); insert into dummy1 values (0); select t1.* from (select * from table1 where a = 0) t1 right outer join dummy1 on 1 = 1; select t1.* from (select * from table1 where a = 1) t1 right outer join dummy1 on 1 = 1; select t1.* from (select * from table1 where a = 2) t1 right outer join dummy1 on 1 = 1;
初歩的な質問ですみません。どうしても動かないので腹が立って質問します。 取引先テーブルから会社名の最後に株式会社とつく会社を抜き出すときはこれでいいんですよね。どうしても動きません。 使ってるソフトはhsqldbです SELECT 会社名 FROM 取引先テーブル WHERE 会社名 LIKE '%株式会社'
697 :
669 :2010/12/07(火) 01:32:20 ID:???
>>672 遅くなりましたが、大体そんな感じで出来ました。
アリガdです。
>>696 エラーメッセージを貼らないとか腹が立ちます。
699 :
NAME IS NULL :2010/12/07(火) 06:55:47 ID:HbkP7Lwq
0-hitかも
文字コードが合ってるか、とか。
>>696 正解とはいえませんが、これでどうなるか確かめてください。
SELECT 会社名
FROM 取引テーブル
WHERE 会社名 LIKE '%株式会社%' AND 会社名 NOT LIKE '株式会社%'
最後にセミコロン
>>698 エラーは出ませんでしたが、結果も出ませんでした。上に「会社名」とだけ出て、抽出されるはずの株式会社で終わる会社名は出ませんでした。
今日学校で聞いた話では「3番はなんか動かないのでいいや」とのことでした。
>>701 おお、出ました。ありがとうございます。また分からないことがあったときはよろしくお願いします。
>>701 アプリ上で「株式会社A」→「A株式会社」にしてるってこと?
「株式会社」の後ろに改行コード等が紛れているんじゃね?
>>704 いや、株式会社の後にゴミが入っているのではないか。
正解でないとしたのは、
「日米起業株式会社東京支店」のようなケースがあるから。
試しに WHERE trim(会社名) LIKE '%株式会社' とかやってみたらどうかな。
スレがないので、ここで質問します。postgreSOLのデータベース定義で、 ENCODING = 'UTF8' LC_COLLATE = 'Japanese, Japan' LC_CTYPE = 'Japanese, Japan' としてますが、pgAdminIII の各ペインに表示される日本語部分が、 ”□□□□□□□□□□□□□□” のようになり、これらの四角形をカーソル反転させて他のアプリに貼り付けると ”全地公団コード” などと正常に表示されます。原因と解決策が分かりましたら、ご教授を。。。。
Oracleのソートだと数字→英字になっていまいますが これをSQLで逆にソートする方法はありますか。
なんとなくこんなくだらないこと考えた select * from TableName order by regexp_replace(regexp_replace(COL_NAME, '[A-Za-z]', 'A'), '[0-9]', 'z'), COL_NAME ;
数字毎、英字毎をどうやってソートするかで結構変わるな
H2DBとHibernate使ってます HtmlのTextAreaからのテキスト入力をそのまま挿入すると インジェクトされると思うのですが、良い対策方について意見ください。 現在考えているのは、 DBへのInsertの前にテキストだけAES暗号化、 主キーでDBから出した後に複合化することです。
そのまま挿入してもインジェクションは起こらないよ。 そのままクエリ作成に利用したらインジェクションが起こる。
クエリ作成するさいの話じゃないの?
SQLのサニタイジングを楽にやりたいとか?
>>715 すみません。インジェクトとかインジェクションと
いうのはどういうことですか?
この機会に教えてください。
スレ違い
暗号化しようが、入れたモノをそのまま出したら、XSSの餌食。
select * from 商品一覧 where 商品名 = 'フォームの入力値'; となっていた場合、入力値に、'; select * from 顧客情報; // とすると select * from 商品一覧 where 商品名 = ''; select * from 顧客情報; // となり、商品取得に加えて、顧客情報の一覧が取得できる(だけでなく、任意のSQLを発行できる。) 詳しくはWeb制作板、、とかなのかな。適当にぐぐればいろいろでてくるよ。
ああ、 select * from 商品一覧 where 商品名 = ''; select * from 顧客情報; //'; こうね。 //じゃなくて--の気もするけど、深く気にしないでw
それはホスト言語でSQLや入力値等をどのように扱ってるかによる まったくのスレ違いなのでさっさとお引き取り下さい
724 :
714 :2010/12/15(水) 17:39:28 ID:???
>>720 htmlの無害化はレイヤー分けるでしょ。
>>718 挿れちゃうぞー☆
>>722 最後の// or -- は何を表してるの?
' " ; % の他にもORとかUNIONとかキリがないから暗号化。
ただ偶然攻撃文になる可能性もある。
なるほどね。
バインド変数が使えるような構成にすれば、 それでいいと思うけど。暗号化???
せめて暗号化じゃなくてBase64にしとけ
>>727 そもそも、Hibernateがそういう仕組みを提供していると思う。
730 :
NAME IS NULL :2010/12/17(金) 02:19:10 ID:nrWKTwgD
・DBMS名とバージョン MySQL 5.1 ・テーブルデータ 記録ID(通し番号)、ユーザID、日時、金額、購入品目ID ・欲しい結果 購入品目ID、日時(最新の1件)、金額(〃) //ただしユーザID=A ・説明 ユーザID=Aの、購入品目IDごとに、最新の日時と金額を表示させたいです。 (購入品目IDが1〜10まであったら、10行の結果が返ってくるような) どこに副問合せを使っていいか分からず詰まりました。どなたかお願いします。
select 購入品目ID, 日時, 金額 from テーブル a where not exists(select * from テーブル b where a.ユーザID = b.ユーザID and a.購入品目ID = b.購入品目ID and a.日時 < b.日時) and ユーザID = A こんなんでできないかな。間違えてたらごめんね。
>>731 レスありがとうございます。
書いて頂いたSQL、意味は何となく分かりました。
しかし、今回はテーブルa内で問合せが完結するので、(テーブルa内の最新のものを表示する)
テーブルbと書いて頂いた所はどのようにすべきでしょうか?
私の知識不足で度々申し訳ありませんが、よろしければご教示お願いします。
>>732 ん、同じテーブルに対して問い合わせをするよ
テーブルの各行について「自分のテーブルの中にそれよりも新しいレコードがないような」レコードを抽出する
相関サブクエリというキーワードで検索するといいかも。
>>733 ありがとうございます、できました。
テーブルに相関名をつけて比較している、と理解しました。
勉強になりました。真夜中にありがとうございました。
ためしてないけど、havingじゃだめなのか? select ユーザID,購入品目ID,日時,sum(金額) from テーブル group by ユーザID,購入品目ID,日時 having 日時=max(日時) こんな感じでいけそうな気がする 最新の日時が同じデータあったら2行だせってならもうひと工夫いるけど
group byに日時入ってんのにmax(日時)って意味ないでしょ。
>>736 じゃあこうか
select ユーザID,購入品目ID,日時,sum(金額) from テーブル a
group by ユーザID,購入品目ID,日時
having 日時=(
select max(日時) from テーブル where ユーザID=a.ユーザID and 購入品目ID=a.購入品目ID)
結局相関サブクエリ必要なのかorz
>>735 のときから気になってんだけど、なんで金額のsum出してるの?
同一日時のデータがあったときにどうするか不明だからとりあえず合計しといた not existsと相関サブクエリが遅そうな気がしたから その二つを使わないで出来ないかと思っただけなんだ
なるほど、同一商品を同一日時に複数購入した場合、かぁ。
>>741 group by を使ったときに集約関数を使わずに
,金額 という具合に参照できるの?
できないよ
MySQL以外では出来ない
>>744 あー、やっぱりそうなのか。でも随分と危なっかしい
仕様だな。
GROUP BY してるのに,金額としたらどう表示されんのよ。
>>746 出てきた順、ORDER BYと組み合わせることで、
>>4 だと
SELECT * FROM (SELECT * FROM table_a ORDER BY `date` DESC)AS A GROUP BY id ;
で済ませることが出来る。
PostgreSQLのDISTINCT ONと似た感じなんだが、
暗黙過ぎてあまり使う気にはなれないけどね。
補足、つーか抜けていた。
>>747 は「MySQLの場合だと」という例ね。
なるほどねぇ。 糞仕様だねぇw
MySQL以外のデータベースってないの?
ないよ
唯我独尊
753 :
NAME IS NULL :2010/12/21(火) 15:49:03 ID:iIvJtzy4
>>4 のような状況で、
各IDごとにランダムで2件抽出して、そのDATEを
現在の日時に更新するにはどうすればよいでしょうか?
MySQL 5.0.77です
まずランダムとかムリだろ
order by を指定しなければ順番は保証されないからランダムではある おそらくデータ更新しない限りは順番は変わらないだろうが
いや、RANDあるんだけど、、、なんで無理なんだ?
758 :
NAME IS NULL :2010/12/22(水) 14:02:32 ID:reTSwFFm
MySQLで、複数の行のそれぞれ狙った項目だけを一度に更新する方法はありますか? 例えばhoge( key, value1, value2 )というテーブルに対して、やりたいこととしては INSERT INTO hoge( key, value1, value2 )VALUES (1, 10, 今の値), (2, 今の値, 20) ON DUPLICATE KEY UPDATE value1 = VALUES(value1), value2 = VALUES(value2) こんなイメージです。変えたいトコだけ変えて、他は現状維持してくれっていう。
「変えたいトコだけ」の条件判断の方法が明確なら普通にUPDATEすればいいだろ
専用のアプリ作ってループでWHERE文発行すればいい気がする
今sql習っているがnullとかinsertとかいやらしすぎるだろ
ヘルス2回も行ってしまったわ
>>761 と同類の人が作ったであろうコピペを昔みたと思うけど、ちょっと探す気が起きない
ANALYSISテーブル( ANALYSIS_ID, .......) PLANテーブル( PLAN_ID, ANALYSIS_ID not null, ........) というイメージなんですが、意図は PLANテーブルには必ず一つ以上のANALYSISレコードを保持する。 ANALYSISテーブルの各レコードは複数のPLANテーブルで参照される。 です。 多:多の結合になると思うんですが、中間テーブルにすると PLAN.ANALYSIS not null の満たし方がわかりません。 こういう場合、通常どう設計するものなのでしょうか? 配列型フィールドとか思い浮かんでしまうんですが・・
ユニークキーってどうやって生成しますか? 自分はシーケンスで生成させているのですが、 一度割り振られた番号はカラムが削除されても再利用されないので いずれ枯渇するかLong型に収まらない数値になると思います。 SQLだけでキーを生成するべきではないのでしょうか
>>766 long型シーケンスを使い切って0に戻ったらどうなるのか?
PostgreSQLの作りそのものは0に戻るはずだが、ちゃんと戻るのか?
そもそも使い切ったシステムはあるのか? そのときどうなったのか?
という話題をかれこれ10年近く前に石井氏がMLだったか講習だったで
していたような気がする。
当時はまだ使い切ったようなシステムが存在しない。だったかな。
俺がかかわるようなシステムでシーケンスの枯渇は気にすることないな。
というのが当時の結論。今もそうだが。
枯渇が気になるのなら(年+シーケンス)とかにして、年に1度シーケンスは
リセットさせればいいんじゃね。
postgresのobjectid(int)だったらこれまでも何回か使い切ったな。 longは使い切れるもんなのかな?事前発行サイズを大きくとってそれを どんどん捨ててしまうシステムだったらもしかしたら?という感じだな
> 枯渇が気になるのなら(年+シーケンス)とかにして Longのままなら結局使える幅が減る→文字列型にする→パフォーマンス悪いんですけどどうしましょう 何度か見かけた流れだな。
年とシーケンスを別カラムにして、複合キーに …ってのも誰でも思いつくよな…
シーケンス使わないで 複合ユニークキー生成サーバー作るとか?
772 :
NAME IS NULL :2011/01/13(木) 06:59:45 ID:Af2KeuAx
わざわざユニークキー生成する必要ないだろ 項目自体に一意性のセットが存在しないなら おそらくそんなデータは更新かけることも無いだろうから 不安なら登録日時を項目として付け加えとけばいい
773 :
NAME IS NULL :2011/01/13(木) 08:55:46 ID:febmxaew
sqlite3を使用しています。 並び変えたデータの100番目以降を消したいのですが方法が見つかりません。 どなたか分かる方がいましたらよろしくお願いします。
こんな感じ? delete from TableName where KEY_COLUMN in (select KEY_COLUMN from TableName order by ORDER_COLUMN limit -1 offset 100);
775 :
NAME IS NULL :2011/01/13(木) 21:03:39 ID:febmxaew
>774 ありがとうございます!うまくいきました。 自分が女の子だったら惚れるところでした。
男でも774はこばまないよ。
777 :
NAME IS NULL :2011/01/14(金) 16:41:55 ID:8a2gtYfg
【質問テンプレ】 ・DBMS名とバージョン Mysql 4.0.27 ・テーブルデータ a_tbl ID | DATE | DATA --+----------+----- 1 | 20081111 | あああ 2 | 20071111 | いいい b_tbl ID | DATE | DATA --+----------+----- 1 | 20081110 | えええ 2 | 20081109 | おおお c_tbl ID | DATE | DATA --+----------+----- 1 | 20081108 | ききき 2 | 20071109 | くくく ・欲しい結果 ID | DATE | DATA --+----------+----- a1 | 20081111 | あああ b1 | 20081110 | えええ b2 | 20081109 | おおお c1 | 20081108 | ききき ・説明 上記のような別々の3つのテーブルからDATEの新しい順にソートして、 かつ各テーブルの行データを出力したいのですが、 どのようにすればよいでしょうか?
>>777 a1とかb1とか言うのはa_tblのID=1とかb_tblのID=1とかって言う意味か?
a2とかc2はなぜ出力されないんだ?
779 :
NAME IS NULL :2011/01/14(金) 17:59:20 ID:8a2gtYfg
>>778 >a1とかb1とか言うのはa_tblのID=1とかb_tblのID=1とかって言う意味か?
その通りです。
>a2とかc2はなぜ出力されないんだ?
すいません、勝手に脳内LIMITしておりました。
全て出力されれば
ID | DATE | DATA
--+----------+-----
a1 | 20081111 | あああ
b1 | 20081110 | えええ
b2 | 20081109 | おおお
c1 | 20081108 | ききき
a2 | 20071111 | いいい
c2 | 20071109 | くくく
となります。
4.1以上なら select * from ( select ID,DATE,DATA from a_tbl union all select ID,DATE,DATA from b_tbl union all select ID,DATE,DATA from c_tbl ) order by DATE; でいいと思うけど、サブクエリが使えない4.0じゃどうすっかねぇ・・・
781 :
NAME IS NULL :2011/01/14(金) 19:03:59 ID:8a2gtYfg
ご教授いただいたコードで一応試してみたところ、 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near : というエラーが出てしまいました。
サブクエリが使えないんだから一旦テンポラリテーブルにだして、そこから… でないと駄目なんじゃ?
783 :
780 :2011/01/14(金) 20:57:42 ID:???
ふと思ったけどorder byの評価順はunionより後なんだっけ? select ID,DATE,DATA from a_tbl union all select ID,DATE,DATA from b_tbl union all select ID,DATE,DATA from c_tbl order by DATE; でいけるような気がしてきた。 試してないけど。
785 :
780 :2011/01/14(金) 22:07:09 ID:???
MySQL 5.1 + InnoDBで、 Entries: id (PRIMARY), title, ..., x_date, ... Tags: id (PRIMARY), name, ... E_T: entry_id, tag_id (複合PRIMARY KEY) こんな感じの交差テーブルがあるとき、 SELECT <Entriesのカラムだけ全部> FROM Entries INNER JOIN E_T ON Entries.id = E_T.entry_id WHERE E_T.tag_id = 12345 ORDER BY Entries.x_date DESC LIMIT 50 OFFSET 1000; これと同じ結果を出すのに最適な方法ってどんなんですかね? クエリの改善、インデックスの張り方、あるいはテーブル構成自体の変更なども含めて。
>>785 正しくはというならSQL標準を引用すべきだし、最後の1文は蛇足。
Oracle「俺の出番のようだな」
「MySQLがやられたようだな…」 「ククク…奴は四天王の中でも最弱…」
残りの三天王って誰?
OSSなら PostgreSQL、Firebird、SQLite 現実的にはMySQLとPostgreSQLの2強だよね。
アプリに組み込まれたSQLiteもかなり使われていると思うけどな。 FirefoxやGoogleChromeもSQLiteだし。おかげでvacuumやreindexを たまにやらないと重くなるが。
先生助けてっ! Firebirdちゃんが息をしてないのっっ!! FireBirdちゃんが使われてるシーンってマジで無いよね 何に採用されてるんだろ
count(*) でとった値にmaxってムリポそうなんですが、 他になにかいい方法ってナイスかね?
mysqlです
count(*)で取った値がmaxじゃなかったらと思うと背筋が凍るわ。
こういうことじゃないの? select max(cnt) from (select hoge, count(*) as cnt from fuga group by hoge);
>>786 とりあえず Entries に (id, x_date) のインデックス張ってみれば?
あとは、それぞれのテーブルの件数、条件にヒットする件数、
現在のSQLの実行計画なんかもないと誰も何も答えてくれないと思う。
>>786 最近のプランナやオプチマイザが優秀だから、適当に組み替えると思うが、
(全て)JOINする前にtag_id=12345だけ抜き出した方が速いだろうから、
SELECT Entries.* FROM (SELECT * FROM E_T WHERE tag_id = 12345) AS Tet
JOIN Entries ON Tet.entry_id = Entries.id
ORDER BY ...(略
とかね。
DBに因ってはINもかなり速くなって、上にかいたように下手に気を遣うより、素直に
SELECT * FROM Entries WHERE id IN (SELECT entry_id FROM E_T WHERE tag_id = 12345)
ORDER BY....(略
と書いた方が最適解を見つけたりして、一昔前は遅いSQLの見本なんだがなw
あと、E_Tはtag_id単体で効くインデックスと、Entriesもid単体で効くインデクスは要る。
NHKホワイトボックスで、SQLインジェクションってやっていた。 なんかびっくりしたんだが、一般ユーザがネット販売を利用するとき、メールアドレスにSQL言語を書き込むと、言語が動作して、過去のネットで購入した他人のリストが表示されて個人情報が抜かれてしまうとか。 これって、めちゃ基本的な話で、そもそもMySQLとかORACLEのSQL言語を扱うサーバーアプリ側で、入力データのチェックをしていたらよかったんじゃないの? おれもこの世界やってたの、もう10年以上前だからよく憶えてないけど [a-z0-9]+@[a-z0-9]+.ne.jp とかって、適当に書いたけどなんかあったよね。perlとかでも。なんて言ったっけ。 なんとかマッチングだっけかな。 ホワイトボックスでは、WAFを入れてみたいなこと言ってたけど、SQLサーバーアプリ側がバージョンアップして入力制限すべきじゃないの? そんなんじゃだめなのかな。
あ、これってサーバーアプリを組む側がわざわざ入力制限プログラムをコーディングして作らなくてもメール入力フォームとかを組むと自動的にデフォルトで入るように、という意味
メールアドレスとは限らないだろタコ それにいまどきSQLインジェクションされてないWebアプリなんか作られてねーから
スレ違い よそでやれ
>>801 メールアドレスってもっとゆるいから、正しくメールアドレスにマッチさせるものを書いた場合に
それだけではSQLインジェクションを防げないかと。
変な奴だな。そんなわけないだろ。それって、正しいメールアドレスを書いてもSQLインジェクションでおかしな動作をするメアドがあるってことになるぞ。ありえねえ
この話はここまでにしような。はい終わり
つい最近まで自分とこで保守してるwebシステムに、 SQLインジェクションでログインできるものがあったな〜…
スレ違いだってのに
>>806 のようなよく知らない人ほどレスしたがる。
ほかで勉強してきてください。
MySQL5.1を使っています。 datetime型のテーブルに 元データ 16/Jan/2011:06:26:54 のフォーマットで入力したいのですが、どのようにすればよいのでしょうか。 色々調べましたが、わかりません。 是非、アドバイスください。おねがいします!
アプリでやるほうがいいかも。SQLだけなら力技だけど。MySQL限定 SET @dttm = '16/Jan/2011:06:26:54'; SET @mon = 'Jan,Feb,Mar,Apr,May,Jun,Aug,Sep,Oct,Nov,Dec'; SET @dt = SUBSTRING_INDEX(@dttm, ':', 1); SET @tm = SUBSTRING_INDEX(@dttm, ':', -3); SET @y = SUBSTRING_INDEX(@dt, '/', -1); SET @m = FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(@dt, '/', 2), '/', -1), @mon); SET @d = SUBSTRING_INDEX(@dt, '/', 1); SELECT CONCAT(@y, '-', @m, '-', @d, ' ', @tm); -- INSERT INTO foo SET bar = CONCAT(@y, '-', @m, '-', @d, ' ', @tm);
>> 811 ご連絡遅くなりました。アドバイスありがとうございます! やはり、アプリでおこなった方が無難と確信しました。sedとawkでフォーマットを YYYY-MM-DD HH:MM:SS とすることに致しました。上記のような方法もあるということをご教授いただき勉強になりました。 ありがとうございました!
order byの使い方の確認させて グループ番号で先ずソートして抜き出して、 さらに同一のグループ番号内の更新日時でソートする場合は、これでいい? SELECT * FROM テーブル ORDER BYグループ番号、更新日時 1 2010-09-03 16:53:37 1 2010-09-03 17:02:57 0 2010-09-03 17:03:18 0 2010-09-03 17:03:26 0 2010-09-03 17:03:30 1 2010-09-10 11:18:19 0 2010-09-10 11:18:40 頭の[0,1]はグループ番号なんだけど、mysqlの5.0.88で、 こんな感じでバラバラで表示されるんだけど、oreder by間違ってる?
SELECT * FROM テーブル ORDER BYグループ番号、更新日時 ならこうだろ 0 2010-09-03 17:03:18 0 2010-09-03 17:03:26 0 2010-09-03 17:03:30 0 2010-09-10 11:18:40 1 2010-09-03 16:53:37 1 2010-09-03 17:02:57 1 2010-09-10 11:18:19 1 2010-09-03 16:53:37 1 2010-09-03 17:02:57 0 2010-09-03 17:03:18 0 2010-09-03 17:03:26 0 2010-09-03 17:03:30 1 2010-09-10 11:18:19 0 2010-09-10 11:18:40 にしたければこうだろ SELECT * FROM テーブル ORDER BY更新日時
815 :
NAME IS NULL :2011/01/24(月) 21:18:49 ID:3dHm5YQ9
こんなレコードが格納されているとします。 ID,NO 100,1 102,2 101,3 200,1 201,2 202,3 この場合、IDを100番台、200番台でグルーピングして、各グループ毎にNO順にSELECTで出力しようとした場合、どのような条件にすればよいでしょうか? IDはこの例では3桁ですが、実際には6桁程あり、出来ればWHERE句で数字を入れて指定するような事はせずに並び替えが出来ると嬉しいです。 要は、レコードが増えてグループが増えたりしても、条件を変えずに並び替えが出来るようなものが良いのですが、可能でしょうか? 宜しくご教示願います。
816 :
815 :2011/01/24(月) 21:20:43 ID:???
すみません、補足です。 >815は出力結果の並び順と思って頂ければよいです。 実際にはレコードの順番は滅茶苦茶だったりします。 IDで並べ替えてから、NOで並べ替えるという事が出来ればきっと実現出来ると思うのですがやり方がわからなくて・・・orz
IDを100で割った値(切り捨て)、NOでソート。
order by floor(ID/100), NO
819 :
815 :2011/01/24(月) 22:26:31 ID:???
おぉ、なるほど!お二方、どうも有難う御座います!
820 :
NAME IS NULL :2011/01/25(火) 12:25:17 ID:92np3pBK
eventId / eventName / userId の3つのカラムがあるイベント登録用テーブル(tableE)があります。 これに対して、 reportId / reportTxt / userId / eventId の形のイベントの感想用テーブル(tableR)を作ってます。 この時 tableE.eventId==tableR.eventIdとしたいです。(※) この条件で、tableRへのinsertクエリを書くとしたらどうすべきでしょうか。 プログラム(php)からはeventIdがパラメータ(getメソッド)で送られてくるので 単純にその値をセットしてのinsertは出来るんですが、getメソッドのためユーザーが いくらでもeventIdを変更できてしまいます sql文だけで、(※)の条件を実現することは出来ますか? サブクエリ?を使うのかなと思ったもののよくわかりません
なんていうか、つっこみどころ満載だな eventIdは誰が発番するんだ? tableEとtableRのどの行(どの条件)がeventIdが等しくならないとだめなんだ? SQLの問題じゃなくて、まず仕様をちゃんと明確にしろよ
822 :
820 :2011/01/25(火) 13:55:40 ID:92np3pBK
>>821 ありがとうございます
>eventIdは誰が発番するんだ?
tableEのeventIdは主キーでオートインクリメントです、書き忘れました、すみません。
>tableEとtableRのどの行(どの条件)がeventIdが等しくならないとだめなんだ?
「この時 tableE.eventId==tableR.eventIdとしたいです。(※)」ってことです
下記のクエリで、イベント(eventIdは100)の感想を登録できるんですが
INSERT INTO tableR (reportId,reportTxt,userId,reportId) VALUES (0, '面白かった',10,5);
これだとURLの &eventId=5 の部分をユーザが50とか500とか勝手に書き換えた上で
そのままインサートできてしまいます。
感想レポート記入画面で、パラメータのeventId(とuserId)を条件にtableEにチェックのクエリを
実行すれば良いのはもちろんですが、そうではなく感想レポートの登録クエリ時に
チェックしたい(insert文の一部として)と思っています
そんなことは出来るんでしょうか?
FOREIGN KEY
だからこの時ってのはどの時なんだよ
eventIdが5じゃないとダメな理由は?
tableEに存在しないeventIdはダメっていうなら
>>823 でも5も50も500もtableEに存在してるなら、5はよくて50や500がダメな理由はなんだ?
なんでそんな怒ってるんだよw
>>820 =
>>822 tableE に存在しさえすればユーザが勝手に eventId を指定してきても良い
ってことなら
>>823 の通り tableR の eventId を外部キーにすればいい。
そうじゃないならそのデータはクライアントから受け取るべきじゃない。
サーバ側で保持すべき。
826 :
825 :2011/01/25(火) 19:45:20 ID:???
あ、「そのデータ」ってのは eventId のことね。
>「この時 tableE.eventId==tableR.eventIdとしたいです。(※)」ってことです がわけわかめなんだよね。 「この時 tableE.eventId=tableR.eventIdでなければ(=存在しないtableE.eventIdであれば)エラーにしたい(INSERTしたくない)」 なら意味がわかる。 >感想レポート記入画面で、パラメータのeventId(とuserId)を条件にtableEにチェックのクエリを >実行すれば良いのはもちろんですが、そうではなく感想レポートの登録クエリ時に >チェックしたい(insert文の一部として)と思っています >そんなことは出来るんでしょうか? からすると823でいいんじゃないかと思うけど、結局のところGETなら何が来るかわからないので、SQLとは別なんだけど 投稿する前にセッションとかにeventIdを保存しておいてGETでは渡さないという工夫も必要かも。
どこかのプロジェクトで、SQLで四則演算してはいけない という コーディングルールがあったんだけど、その理由を失念してしまいました。 なにか、該当するような事象ってあるんですかね?計算結果がおかしくなるとか.
830 :
NAME IS NULL :2011/01/27(木) 14:16:13 ID:xnnaktpA
MySQL 5.1を使っているのですが、 リストにabc,def,ghiのように単語が登録されているとき、 "hogehogeabchogehogehogeghihogejklhogehoge"という文から、 abc,ghiという単語がこの文に含まれているというような検索をしたいのですが、書き方がわかりません。 それ以前に、データベースでこのような検索は可能ですか?
>>829 桁数とか丸め誤差がアプリケーションと違っちゃうのが嫌だったとか
>>830 where COL_NAME like '%abc%' and COL_NAME like '%ghi%'
とか?
832 :
NAME IS NULL :2011/01/28(金) 01:13:12 ID:sGatbyEv
>>831 リストには数百個あるので、それだと結構大変かもしれないです。
具体的には、文の中でリストにある単語のどれが出たかを出力するものを制作しています。
単語ごとに区切ってデータベースにアクセスすればできそうですが、制約上単語ごとに区切れないもので・・・。
単語が複数見つかった場合はどうすんだろ。
リストというテーブルの単語という項目に単語が含まれてるとして select 単語 from リスト where 対象の文 like '%' + 単語 + '%' こんな感じってことじゃ? (+で文字列連結できる前提な) MySQLで動くかどうかしらんが
835 :
NAME IS NULL :2011/01/28(金) 03:51:46 ID:uBEDX4Y3
文って単数なの、 複数なの(テーブルにひとつの項目として保存されてるの)?
上の回答は単語数100などは想定していない。 このままコードで単語数が10数個を越えたら、 組み合わせの処理でスタックオーバーフローに なって失敗する。 length(_単語ならび,Len) を止めて、単語の 最大数(現在のLen)を限定して指定する必要が あります。
スレ違いかつ要件を満たしていないことを自信満々に言われてもどうしたらいいのか
スルーすればいいよ。
スレ違いは覚悟の上なんだが、ここで指摘したかったのは
リストからどのように検索対象を取り出すかということ。
この質問はその部分があいまい。全部の可能性を考えると
こんなアルゴリズムになりますよというのが
>>836 あとから
>>832 を読んで、数百個あるので、というところで
マイッタw
841 :
NAME IS NULL :2011/01/29(土) 10:06:10 ID:Kh+BN3Cq
>>833 単語が重複した場合が数えません。
>>835 文は特にデータベースに保存しません。
単語数のみをデータベースに保存していきます。
やっぱりデータベースで全てやるのは難しいみたいですね・・・。
Perl等で単語をデータベースから1個ずつ引っ張って、文の中に含まれるかを検索するほうが無難かもしれませんね。
ありがとうございました。
842 :
NAME IS NULL :2011/01/29(土) 10:21:46 ID:wMofn+ab
>>841 それだったら
>>834 ちょこっと変えればいけるんじゃないの
文の結合はoracleだったら||でやればいいし
あとはGROUP BY 単語 をかませれば単語毎のカウントもできるだろ
843 :
NAME IS NULL :2011/01/29(土) 11:24:53 ID:wMofn+ab
やっぱSQLじゃ無理そうだな 同じ一文だとカウントされそうもないや 全部一件になるな
>>834 のcountでなにが悪いのかがわからんや
845 :
NAME IS NULL :2011/01/29(土) 18:13:29 ID:wMofn+ab
>>844 多分検索対象の文を一字づつ削った文
文の文字数分の行を用意しないとだめ
・DBMS名とバージョン Microsoft Access2000 ・テーブルデータ 【ITEM_COUNT】 item_id | year | month | count --------------------------- 00001 | 2010 | 1 | 150 00001 | 2010 | 1 | 400 00001 | 2010 | 2 | 20 (中略) 00001 | 2010 | 11 | 120 00001 | 2010 | 11 | 50 00001 | 2010 | 11 | 200 00001 | 2010 | 12 | 30 00001 | 2011 | 1 | 500 00001 | 2011 | 1 | 100 00002 | 2010 | 1 | 10 00002 | 2010 | 2 | 30 00002 | 2010 | 2 | 100 (後略) ※同じid, year, monthのデータが複数存在します。 ・欲しい結果 item_id | 1月 | 2月 | … | 11月 | 12月 | 13月 -------------------------------------------- 00001 | 550 | 20 | … | 370 | 30 | 610 00002 | 10 | 130 | … | (後略) ※2010/1〜2011/1の13ヶ月間を1月〜13月として(2011/1を13月として)それぞれ集計を求めたい ・説明 現状は、 ------------------------------------ SELECT year, item_id FROM ITEM_COUNT WHERE year=2010 GROUP BY item_id, year PIVOT Format(month,"#0月") In ("1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"); ------------------------------------ 上のクエリを「集計1」、WHERE句を"year=2011"に変更したものを「集計2」とし、 次のクエリで結果を求めています。 ------------------------------------ SELECT 集計1.item_id, 集計1.[1月], 集計1.[2月], … , 集計1.[12月],集計2.[1月] AS 13月 FROM 集計1 LEFT JOIN 集計2 ON 集計1.[item_id] = 集計2.[item_id] ------------------------------------ しかしこれだと2011/1にのみデータがあるitem_idが集計から外れてしまいます。 これを回避するにはどのように直したら良いでしょうか。
2010/01から2011/01までに存在するitem_idの一覧を求めるクエリに 集計1と集計2をleft outer joinすりゃいいんじゃね?
848 :
846 :2011/02/01(火) 22:24:47 ID:???
>>847 レスありがとうございます。
なんとなくイメージが掴めたのでやってみます。
k - Nearest Neighborがsqlで出来ると聞いたのですが どんな書き方するのでしょうか
SQL以外の言語でなら、どう書けばいいのか知ってるってこと? そのアルゴリズムと、それが前提とするデータ構造を、RDB&SQLに移すだけなら、できるだろ。 それが効率がいいのかどうかは別の問題だが。 どんな書き方をするかというより、RDBと親和性の高いデータ構造を前提にした 効率のよいアルゴリズムがあるのかって話なんだろう。
ほとんど見かけないのですが SQLの計算量の良い資料ってあるんでしょうか 基本的な関係代数の 和、差、交わり、直積、制限、射影、結合、商 ぐらいは知りたいです
それが演算の話か結合の話か 演算の場合の計算量はめったに必要にならないと思うけど、それが必要? 結合の場合はプラン見たら大体分かるだろ
ああ、関係代数って明記してんのか。 じゃあ何でわかんないんだ?って話じゃね?
855 :
NAME IS NULL :2011/02/04(金) 19:55:24 ID:w30Vjyrb
はじめまして。 sql教えて欲しいです。 商品CD │ グループCD ──────────── 01 A 02 A 03 B 04 A このようなデータの中から02という商品CDが所属するグループの全ての商品CDを 抽出したいのですが、どのようなSQLがよろしいかと。宜しくお願いします。
select 商品CD from TableName where グループCD = (select グループCD from TableName where 商品CD = 02);
商品CDが主キーじゃなかったりしたらあれだから in 使う方がいいんじゃね
2つの項目でユニークなマスターに、 元データの追加があったときだけInsertする方法を教えてください。 例えば、都道府県と市町村と日時だけのマスターテーブルがあります。 顧客データが更新された時、マスターにない都道府県と市町村のデータがあったら、 マスターに日時データをインサートして、 その市町村からの初めての登録日マスターみたないなテーブルを作りたいのですが、 よく分かりません。 教えて下さい。
トリガでいいんじゃないのかな。
>>858 顧客データを都道府県、市町村でGROUP BYして登録日の最小をとるのではダメ?
861 :
859 :2011/02/05(土) 06:35:55 ID:???
顧客データの更新時を考えるとちょっとそれ面倒だなぁとおもって、トリガを薦めてみたよ。
mergeが使えるならそれで行けるかも だめなら素直にselectしてデータあるか判断してからinsertすれば? ホストアプリでやるかストアドやトリガでやるかはお好きに
863 :
860 :2011/02/06(日) 09:05:35 ID:???
>861, 862 ちょと勘違いしてるっぽいネ。 顧客データを都度検索すれば「その市町村からの初めての登録日」は取得できるから、「都道府県と市町村と日時だけのマスターテーブル」を作らないって手もあるよということ。 参照の頻度や検索コストにもよるけど。
864 :
860 :2011/02/06(日) 09:07:48 ID:???
顧客データの登録頻度が高くて、市町村登録日のリアルタイム性がそこまで必要ないのであれば、トリガより定期処理で更新するほうがよかったり、いろいろだね
>>863 元々の質問者
>>858 の書いた通りに解釈すると
最初の出現日付ということになるから、その後
元データが削除されるとどうなるか、ですね。
866 :
861 :2011/02/07(月) 00:42:41 ID:???
>>863 そりゃあ、顧客情報の市町村情報を変更した日を別途持ってるならそれでいいとおもうよ。
けど、普通持たなくない?顧客情報の初期登録日時と最終更新日時くらいなら持ってる場合も多いだろうけど。
867 :
NAME IS NULL :2011/02/07(月) 17:11:10 ID:Hb9WsdcY
はじめまして。ど素人なので教えを請いたいです。 ・MySQL使用 ・テーブルデータ test_table id|memo -------- 1| 1st -------- 1| win -------- 1| 1 -------- 2| 1 -------- 2| 2nd -------- これをこっちからidやmemoの値を入力して、テーブルのその部分だけ表示させた いのです。 例えば ・idの欄に1、memoの欄にwinを入力し、実行ボタンを押す ↓ id|memo -------- 1| win -------- が表示される ・memoの欄に1を入力し、idは空欄 ↓ id|memo -------- 1| 1 -------- 2| 1 -------- が表示される といったように、片方だけの入力でも両方の入力でもそれに対応したものが表示 されるものが作りたいのです。 どうかよろしくおねがいします。
868 :
NAME IS NULL :2011/02/07(月) 19:36:00 ID:W2mKkHfb
>>867 SELECT * FROM test_table
WHERE (id = 入力値1 OR LENGTH(入力値1) = 0)
AND (memo = 入力値2 OR LENGTH(入力値2) = 0)
SELECT * FROM test_table WHERE id = COALESCE(入力値1, id) AND memo = COALESCE(入力値2, memo) とか
・DBバージョン MySQL5.0.77 id|_regDate_| --+----------+ 1 |2010-10-10| 2 |2010-10-10| 3 |2010-10-10| 4 |2010-10-11| 5 |2010-10-11| 6 |2010-10-11| 7 |2010-10-11| 8 |2010-10-12| 9 |2010-10-12| 10|2010-10-12| 11|2010-10-12| ・欲しい結果 何件レコードがあったかを日別に出力したい。 例として _regDate__|件数 ----------+- 2010-10-10|3 2010-10-11|4 2010-10-12|4 こうなってるとうれしいです。 ・説明 自前でクリックカウンターを作っております。 ほかにカラムとしてユーザーエージェントやリモートホストのアドレスを設置してありまして 日別に集計して見れるようにしたいと思ったのですが、PHPでループを書いて出力するより SQLの問い合わせ段階で、上記のように整形できないかなと思いました。 よろしくお願いいたします。
count, group by あたりでググレよ。 日付順にもしたいだろうから order by もいるかな。 ちなみに、2010-10-11|0 も欲しいとか言うなよ。
875 :
873 :2011/02/09(水) 00:29:44 ID:???
ヒントありがとうございます。 なるほど。GOURPってイマイチ、何に使うのかピンと来なかったのですが 納得しました。 SELECT DATE_FORMAT( RegDate, '%Y年%m月%d日' ) , count( RegDate ) FROM accessLog GROUP BY RegDate >ちなみに、2010-10-11|0 も欲しいとか言うなよ。 欲しいですが…なんとかします。 本当にありがとうございましたー。
postgresqlのgenerate_seriesかなんかみたいなやつのmysql版はないのかな
877 :
NAME IS NULL :2011/02/09(水) 11:49:03 ID:nceEl1Ww
878 :
877 :2011/02/09(水) 14:51:45 ID:???
自己解決しました。
・DBMS名とバージョン oracle9i / 10g ・テーブルデータ 部署ID 大分類 中分類 小分類 予算 -------+------+-------+------+--- 01 01 01 01 2500 01 02 01 01 1000 01 02 02 02 100 02 01 01 01 100 02 01 02 02 0 02 02 02 02 1000 02 03 01 01 0 03 01 01 01 500 ・欲しい結果 上のテーブルの時、 (大分類=01and予算>50) and (大分類=02and予算<2000) の部署IDを取得したい。 ・説明 とりたい結果は01,02ですが上のSQLを そのまま書くと当然結果はnullですし、 括弧の間のandをorにすると03も取得されてしまいます。 なにか良い方法はないでしょうか?
まず条件を日本語で説明してみろよ。
881 :
879 :2011/02/11(金) 14:24:55 ID:???
大分類が01で予算が50以上、かつ、大分類が02で予算が2000以下、 という両方の条件を満たすレコードを持つ部署IDを取得したい、ということです。 解りずらかったらすみません。
WHERE (大分類=01 AND 予算>50) AND (大分類=02 AND 予算<2000) でいけんじゃないの
883 :
NAME IS NULL :2011/02/11(金) 14:45:44 ID:gp61lhlu
相関副問合せでNOT EXISTS使え 条件はそれぞれの逆条件でorで結びつけろ
>>881 > 大分類が01で予算が50以上、かつ、大分類が02で予算が2000以下、
> という両方の条件を満たすレコード
ひとつのレコードには大分類はひとつしかないんだから、
大分類が 01 で かつ 大分類が 02 のレコード
なんてあるわけないだろ。
どうせ、(大分類 01 で予算 50 以上) と (大分類 02 で予算 2000 以下) の両方の
レコードをもつ部署 ID が得たいんだろうな。
どうすればいいかは、すぐには思いつかないけど…。
副問い合わせかintersect使え
886 :
879 :2011/02/11(金) 15:03:36 ID:???
>>882 そのSQLだと結果が得られません。
>>883 ありがとうございます、調べて実践してみます。
>>884 その通りです。
テーブル設計から変えるべきでしょうか?
887 :
NAME IS NULL :2011/02/11(金) 15:04:44 ID:gp61lhlu
WHERE (大分類 01 で予算 50 以上) EXISTS(SELECT * FROM table b WHERE a.部署ID = b.部署ID and (大分類 02 で予算 2000 以下) ) だな
Oracleならintersectでいいんじゃね? select 部署ID from TableName where 大分類=01 AND 予算>50 intersect select 部署ID from TableName where 大分類=02 AND 予算<2000 ;
>>879 パターン1
条件のどれかに合う行の部署ID, 大分類のペアを重複無しで
出してから部署IDでGROUP BY、個々のグループに含まれる
行数で絞り込む。
例のように条件が2個であれば、各グループに2行含まれて
いれば両方の条件にマッチすることになる。
SELECT 部署ID
FROM (
SELECT DISTINCT 部署ID, 大分類
FROM T
WHERE
(大分類=01 AND 予算>50) OR
(大分類=02 AND 予算<2000)
)
GROUP BY 部署ID
HAVING COUNT(*) = 2
パターン2
条件1にあう部署ID、条件2に合う部署ID...をそれぞれ
求めてから、全ての論理積を取る。
分かりやすくするため下の例ではINTERSECTを使って
いるが、JOINを使ってもEXISTS述語を使っても似たよう
なことは出来る。
SELECT DISTINCT 部署ID
FROM T
WHERE 大分類=01 AND 予算>50
INTERSECT
SELECT DISTINCT 部署ID
FROM T
WHERE 大分類=02 AND 予算<2000
一番素直なやり方はinをandで結べば良いだけなんじゃ? select .... where 部署ID in (select 部署ID from table where 大分類=01and予算>50 ) and 部署ID in (select 部署ID from table where 大分類=02and予算<2000)
一番素直なのはexistsだとおもうけど、どれでもいいと思うよ。
あと、
>>884 が「どうせ」という言葉を出してるところに驚いた。
existsだと相関サブクエリ必要じゃね? まあたしかにどれでも良いんだけど
893 :
879 :2011/02/11(金) 19:12:52 ID:???
INTERSECT が有効な場面を初めて見た。
895 :
NAME IS NULL :2011/02/13(日) 20:43:39 ID:4kN5gBix
MYSQLで動的ホームページを作っています。 fileテーブルにそれぞれのページのデータを保存していて accessテーブルにアクセスログを保存しています。 fileテーブルにはAIのページIDカラムがあり accessテーブルにはアクセスされたページのIDカラムがあります ページ一覧ページで各ページのアクセス数を表示したいです。 SELECT * FROM fileの結果と、SELECT count(*) FROM access WHERE id='アクセスされてページのID'の結果を結合させるSQL文を教えてください。 いまはPHPで $query="SELECT * FROM file ORDER BY id DESC LIMIT"; $result=mysql_query($query); while ($row = mysql_fetch_array($result)) { $query2="SELECT count(*) AS id FROM access WHERE id = {$row['id']}"; $result2=mysql_query($query2); $row2= mysql_fetch_array($result2); } という書き方をしていますが、クエリを一回で済ませたいです。 よろしくお願いします。
select T1.*, T2.cnt from file T1 inner join (select id, count(*) as cnt from access group by id) T2 on T1.id = T2.id ;
まずは、inner join とかでググレ。
joinいらんだろ? select t1.*,(select count(*) from access where id=t1.id) as cnt from file t1 でできないのか?
899 :
NAME IS NULL :2011/02/13(日) 21:17:25 ID:4kN5gBix
>>896 願いどおりの結果になりました。
ありがとうございました。
901 :
NAME IS NULL :2011/02/13(日) 21:20:26 ID:4kN5gBix
>>898 こっちでも出来ました。
しかし自分には違いがわからない。
結果は同じなんだが、サブクエリもjoinなのか?
id=t1.id ←ここが結合条件
相関サブクエリをJOINというのは珍しいような。全部JOINになるな
相関でもいいけどそれはスカラサブクエリって呼ぼうぜ。 JOINとは呼ばんけどやってることは広義の外部結合だわな。
ああ、確かに。相関じゃないなすまん。
相関サブクエリー(サブクエリが外部クエリの現在評価中の行に依存する)だし 同時にスカラーサブクエリー(結果が必ず一行一列)だよ。 この二つは直交する概念だから、組み合わせもごく普通にあり得る。
つーかMySQLのJOINって
>>896 みたいな変態チックなやつなのか
きわめて一般的な形に見えるけど、どのあたりが変態チックにみえたんだろ
言えない AS句がなかったからサブクエリをそのままだと脊髄反射で読み替えてしまっただなんて 言えない
ASを句とは言わない。
データベースのファイルを読み込んでもどんなテーブルが作成されているのか 分からなければ、SELECT文で命令しようもないと思うのですが、 テーブル一覧を表示させるための方法をネットで調べると、 データベースソフトの種類によって、どれもまちまちなんですね? SQLで統一された構文があるかどうか探してみたのですが、よく分からなかったです。
>912 思いつくキーワードと共にSHOWでググルヨロシネ。
SHOW TIME;
って、それだけじゃよくないな。
>>912 統一された構文はないよ
>>912 各RDBMSによって異なるので、統一的なものはありません。
Oracle準拠のSQLの実装に熱心なPostgreSQLやMySQLでも、
さすがにありません。
>>916 SQLの設計思想の問題、といえばいいのだろうか。
SQL標準にないのに真似したら訴えられそうだからとか 相手優れていることを認めたことになるとかいう消極的理由っぽ
RDBMSごとにその中では統一されてるんだよね。
923 :
NAME IS NULL :2011/02/19(土) 11:17:44 ID:hGdu7TlU
初歩的ですみません、相関副問合せでinの仕組みを教えてください。 in句の使いかたとして、 select * from テーブル where フィールド1 in (値1,値2,値3) みたいな使い方は素直に読めるのですが、 社員テーブル syain id | name | busyoid | ------------------ 1 | yamada |100 | 2 | satou |100 | 3 | suzuki |200 | 部所テーブル busyo busyoid | busyomei | ------------------ 100 | soumu | 200 | eigyo | の2つのテーブルで、総務所属の人のみ抽出するとき、 select * from syain where 'soumu' in (select busyomei from busyo where busyo.busyoid = syain.busyoid) で答えが出るとき、where 'soumu' in(select文)のin句の使い方はどう理解すれば良いのでしょうか。
>>912 SQLレベルではバラバラだが、ODBCやJDBCなどでは規定があってドライバ側で
差異を吸収するようにされている。
CSEやEQなどODBC/JDBCを使ったツールならDBMSの種類によらずスキーマ
情報の表示が可能だし、自プログラムでならばそれらドライバマネージャの
APIを使う。
>>923 (1)syainからレコードをひとつ取り出す
(2)そのレコードに対してサブクエリを実行して条件判定する
の繰り返しだと思えばいい
実際にそう動いているかは別としても、結果はそうなる
> select busyomei from busyo where busyo.busyoid = syain.busyoid によって、部署名の一覧が出るだろ? で↓のようになると。 > 'soumu' in ('soumu', 'soumu', 'eigyo') 一致する id=1、id=2の行が抽出されるってわけ。
てかクソみたいなSQLだな それ作った奴殴っておけよ
相関副問い合わせじゃなくていいし。 mysql> select * from syain where busyoid in (select busyoid from busyo where busyomei = 'soumu'); +------+--------+---------+ | id | name | busyoid | +------+--------+---------+ | 1 | yamada | 100 | | 2 | satou | 100 | +------+--------+---------+ 2 rows in set (0.00 sec)
>>926 > 'soumu' in ('soumu')
しかでないのでは。
931 :
930 :2011/02/19(土) 12:43:35 ID:???
まちがえた。 > 'soumu' in ('soumu') > 'soumu' in ('soumu') > 'soumu' in ('eigyo') がそれぞれでるのでは。
where 'soumu' in ('soumu') は where trueと同じで3行表示される。 where 'soumu' in ('eigyo') は where falseと同じ。
933 :
923 :2011/02/20(日) 00:23:22.90 ID:f2cCeKTH
レス、ありがとうございます! where true又はwhere falseで出力するしないを判断する記述なんですね。 目からウロコです。やっと理解できました。 ウラ技っぽいinの使い方ですね。 ありがとうございました!m(__)m
>>929 select syain.* from syain join busyo using (busyoid) where busyomei = 'soumu';
のほうが素直な気もするけれど、どうなんだろね
>>934-935 俺もそう思うけど、SQL 始めたばかり頃は2重ループを作ればいいと
>>929 みたいに考えてた。
要するに、join をちゃんと理解してなかっただけだが。
937 :
912 :2011/02/20(日) 11:55:48.13 ID:???
>>912 です。みなさん、いろいろご回答、どうもありがとうございました。
たいへん参考になりました。
USINGなんて句があるのか ポスグレのCOPYぐらいでしか見たことがない 知らんかったー
カラム名が同じならちょっとだけすっきりしていいよ
940 :
NAME IS NULL :2011/02/27(日) 00:23:41.31 ID:Lx8QjMnN
既存のテーブルのcreate文を確認する方法ってありますか? ご存知でしたら教えていただきたいです。
Oracle対応本が多いですが、MySQLとどのように違うのでしょうか? Web系ではMySQLの方が多いと思うのですが、データウェアハウスとか事務系はOracleが多いのでしょうか?
>>940 SQLで一般化された方法はない
つかってるDBMSのスレで聞いてみたら
>>942 ありがとうございます。専門スレで聞いてみます
>>941 DBへの考え方が根本から違う。
けれどSQL一般で言えばどちらも方言を含有した英語程度に共通。
用途はやっぱMySQLはWeb、Oracleは事務でしょうか?
お金がある人はOracle、無い人はMySQL
変人さんはPostgreSQL
PostgreSQLのシェアがMySQLより低いのが相変わらず謎。 なんでMySQLなの?
簡単だからでしょ
管理が?
MySQLは早くから(当初から?)Windowsに対応していたしね。 ちなみにPostgreSQL本家のみなさんは、名前が長く発音しにくいのが問題では? と、一時期名称変更を検討したらしいw ただ、日本のシェアはこれでも他所と比べて高いらしく、 石井氏が名前の所為じゃないと反論していました。
本屋に行ってDBの勉強しようと探したら、MySQLの本はあってもPostgreの本なんて見つからん (大きな本屋に行けばそれなりにあるけどさ
いらねーだろそんなもん。
PostgreSQL は Postgres + SQL でダブってる S をひとつにしちゃった名前 なんだけど、ここが敗因だという説にはひとつの説得力があるような気もする。 知らない人には当然 Postgre + SQL にしか見えないし そう見えちゃうと何て読むのか分かんない。
そういえばSQLite も読み方がわからなくて使う気にならない
DBの研究開発界隈には何故か画家の名前が時々ある。 PostgreSQLの名前もIngresから来たものだし。
なぜか昔、PostageSQLに空目して、しばらくポステージだと思ってたのは内緒
"PostageSQL" - Google 検索 約 5,120 件 (0.06 秒)
>>953 日本人からすると、マイSQLなんて名前だと、業務に使うには力不足なのかと思ってしまうんじゃないかな。
PostgreSQLがMySQLに比して日本語に強かったのもあるだろうけど。
マイドキュメントとかマイピクチャとかマイにはうんざりしてるんですよ。
965 :
958 :2011/03/01(火) 06:22:03.74 ID:???
立てれんかった・・・
>>222 は、間をとってこんな感じでどう?
(問)
年月(YYYYMM)を指定し、その年月に対応する年月日を取得したい
例:201006を指定したら、以下の結果を得たい
20100601
20100602
・
・
・
20100630
(答)
SQLでは存在しないデータを生成することはできません。
この問いの場合は素直にカレンダーテーブルを用意しましょう。
どうしてもやりたければ以下のような方法もなくはないですが、
再帰問合せの本来の使い方ではありません。
やめておくことを強くお奨めします。
(PostgreSQLのgenerate_series()関数なら辛うじてセーフかもしれませんが
賛否の分かれるところでしょう。)
with TEMP (NUM) as (
select 1 from dual
union all
select NUM + 1 from TEMP where NUM < 31
)
select to_char(to_date('201006', 'YYYYMM') + NUM - 1, 'YYYYMMDD')
from TEMP
where to_date('201006', 'YYYYMM') + NUM - 1 < add_months(to_date('201006', 'YYYYMM'), 1)
;
※上記はOracleの場合です。(11gR2以降)
※再帰問合せをサポートするDBMSならこれを適当に改変すれば動きますが
どのみちお奨めしません。
generate_seriesって賛否が分かれるのか。。。
967 :
NAME IS NULL :2011/03/02(水) 15:49:08.57 ID:Jy55ShzU
教えてください。 ■DBMS名とバージョン Firebird 1.03 ■テーブルデータ (注文データベースのようなものです) ・Main 主情報テーブルMIDがキー MID HACCHUBI 1 2011/03/01 ・DETAIL 明細テーブルDIDがキー MID,IIDが参照キー DID MID IID KOSU 1 1 1 100 2 1 2 30 ・ITEM 商品テーブルIIDがキー IID NAMAE ZAIKO OPOINT BIKO 1 HOGE 20 10 0 2 HUGA 50 5 1 ■欲しい結果 2011/1/1から1/31の注文のあった各アイテムの個数(DETAIL.KOSU)の平均値が10以上の場合、 該当するアイテムの注文ライン(ITEM.OPOINT)に、その平均値を入れて更新。平均値が10未満の場合、 ITEM.OPOINTに0を入れる。ただしアイテムの備考(ITEM.BIKO)が1の場合は、ITEM.OPOINTには 空欄(NULL)にする。 ■説明 といいますか、上記の欲しい結果が全てなのですが、どう書いていいのかちょっと見当がつきません。 在庫の仕入れをする個数(ITEM.OPOINT)の設定をコンピュータにさせようとしているのですが・・・。 丸投げで申し訳ありませんが、どなたかお願いします。
GROUP BY,HAVING,AVG・・・
こんな感じかなぁ。テストしてないしFirebirdで通るかよく解らん。 概念としてはこんな感じ。 UPDATE ITEM SET OPOINT = ( SELECT CASE WHEN AVG(DETAIL.KOSU) >= 10 THEN AVG(DETAIL.KOSU) WHEN ITEM.BIKO = 1 THEN NULL ELSE 0 FROM Main, DETAIL WHERE Main.MID = DETAIL.MID AND Main.HACCHUBI BETWEEN '01/01/2011' and '31/01/2011' AND DETAIL.IID = ITEM.IID ) WHERE EXISTS ( SELECT * FROM Main, DETAIL WHERE Main.MID = DETAIL.MID AND Main.HACCHUBI BETWEEN '01/01/2011' and '31/01/2011' AND DETAIL.IID = ITEM.IID )
説明でよく解らん点は以下の二点。 > 注文のあった各アイテムの個数 とあるけれども、最低一個でも注文があったアイテムの行だけ更新の 対象にするのかな。それとも全く注文がなかったアイテムは平均値0 として考えるのかな。後者なのなら上記のクエリのWHERE EXISTS以下 は削除する。 > ただしアイテムの備考(ITEM.BIKO)が1の場合は 平均値が10未満でBIKOが1の場合はNULLを入れる。これは疑いがない。 でも平均値が10以上でBIKOが1の場合はどうなんだろう? もしこの場合もNULLを入れるのであれば、CASE文の二つのWHENの順序 を入れ替える。
1文でできなくはないけど邪魔くさいなぁ 俺なら 1 平均値でOPOINTを更新 2 OPOINTが10未満を0に更新 3 BIKOが1ならNULLに更新 の3ステップでやるが
972 :
967 :2011/03/02(水) 22:22:06.44 ID:???
>>969 ,970
ありがとうございます。中途半端な説明ですみません。
・最低一個でも注文のあったアイテムのみ更新
・BIKOが1であれば、平均値に関わらずNULLを入れる。
です。
■平均値を求める際、GROUP BY が必要と思っていたのですが不要なのでしょうか。
>>971 ありがとうございます。1 平均値でOPOINTを更新 なのですが
これは、SET OPINT = 平均値のSELECT文 WHERE EXIST 期間のSELECT文 で該当するアイテムの
OPOINTが更新されるのでしょうか?
つまり、(書き方が間違っているのでご指導ください)
UPDATE ITEM SET OPINT = (
SELECT AVG(DETAIL.KOSU) FROM Main, DETAIL
WHERE Main.MID = DETAIL.MID AND Main.HACCHUBI BETWEEN '01/01/2011' and '31/01/2011'
AND DETAIL.IID = ITEM.IID
GROUP BY ITEM.IID
) WHERE EXISTS (
SELECT * FROM MAIN, DETAIL
WHERE Main.MID = DETAIL.MID AND Main.HACCHUBI BETWEEN '01/01/2011' and '31/01/2011'
AND DETAIL.IID = ITEM.IID
GROUP BY ITEM.IID;
あぁ全然ダメです。グループ化するITEM.IIDが表示項目にないから通らないと
思います。どうしたらよいでしょう。
■副照会(?)を用いた更新のやり方
■平均値の出し方(使い方)
(単純に平均値を表示するなら
SELECT ITEM.IID, AVG(DETAIL.KOSU) FROM MAIN, ITEM, DETAIL
WHERE .... GROUP BY ITEM.IID でいけると思うのですが)
>>972 ちと訂正。
>>969 の"ELSE 0"のあとにENDを忘れていた。
あとGROUP BYはいらないよ。
SELECT AVG(T.a) FROM T WHERE T.b = "B1" なんて
クエリはGROUP BYが無くても通るよね。それと同じ。
あとSELECT AVG(T.a) FROM T GROUP BY T.b も正しい。
GROUP BYでT.bが指定されたからといって、必ず選択リストに
T.bを含める必要があるかというと、それは無いわけで。
>>972 group byは表示する項目じゃ無くても通ると思うけど、fromにITEMはないから
ITEM.IIDではなくDETAIL.IIDでgroup byする必要がある
where条件でDETAIL.IIDが搾られてるから、あっても無くても同じ結果になるが
existsのサブクエリは集約関数つかってないのでgroup byなんてそもそも不要
>>974 文法的にはそのGROUP BYも必要ない。
あっても通るし結果も同じだけど。
GROUP BY の項目が表示項目でないと通らないと思ってました。 さて、GROUP BY をつけないと、全部の平均値になってしますのですが どこがいけないのでしょうか。 SELECT AVG(DETAIL.KOSU) FROM Main, DETAIL, ITEM WHERE (Main.MID = DETAIL.MID) AND (Main.HACCHUBI BETWEEN '01/01/2011' AND '01/31/2011') AND (ITEM.IID = DETAIL.IID) --GROUP BY DETAIL.IID ; GROUP BY有りだと期待通り、各アイテムごとの個数の平均値を抽出できるのですが GROUP BY無しだと、アイテム全部の個数の平均値(例"12"の1レコードのみとなって しまいます。 ■書き方が間違っていますでしょうか。 ■GROUP BY無しをUPDATE ITEM SET OPOINT = (SELECT AVG(...とすると 全てに全体の平均値が入ってしまうと思うのですが・・・。 (WHERE EXISTS句も同様の間違いで動作しない可能性もありますが)
>>976 969や972のクエリを見てもらえれば分かるけど、内側のクエリの
FROM句にITEMテーブルは指定されていないよね(指定しちゃダメだよ)。
ITEMテーブルは外側のUPDATE文の中で指定されている。
このようにサブクエリの中から外側のクエリのテーブルを参照する
のを「相関サブクエリ」というので、調べてみると良いと思う。
この例だと、内側のクエリのITEM.IIDには「UPDATE文が現在処理
しているITEMテーブルの行のIID」が入る。なので内側のクエリ全体
では、「UPDATE文が現在処理しているITEMテーブルの行のIIDに
対応するアイテムの発注量の平均」を計算することになる。
>>976 のクエリの場合だと、GROUP BY無しだと全体の平均値が
「ただ一つ」だけ出てくるよね。これも正解。というかこの例では
内側のクエリの答えは必ず一行一列でなければならない。
SELECT文の選択リストやUPDATE文の更新値としてサブクエリを
書くときは、そのサブクエリは結果として必ず一行一列を返す必要
がある(スカラーサブクエリ)という規則があって、クエリを書くとき
も結果がただ1行であることを保証しなくちゃいけない。
なので内側のクエリにはGROUP BYは付けるべきじゃない。むしろ
実装によってはGROUP BY付けるとエラーで弾かれるかも。
いずれにしても相関サブクエリにする事で内側のクエリは「UPDATE
文が現在処理しているITEMテーブルの行のIIDに対応するアイテムの
発注量の平均」を返すので、問題はない。
>>976 心配だったらこんなクエリを実行してみたらどうだろう。
SELECT
ITEM.*,
(SELECT AVG(DETAIL.KOSU)
FROM Main, DETAIL
WHERE
Main.MID = DETAIL.MID
AND
Main.HACCHUBI BETWEEN '01/01/2011' and '31/01/2011'
AND
DETAIL.IID = ITEM.IID
) AS NEW_OPOINT
FROM ITEM
WHERE EXISTS (
SELECT * FROM Main, DETAIL
WHERE
Main.MID = DETAIL.MID
AND
Main.HACCHUBI BETWEEN '01/01/2011' and '31/01/2011'
AND
DETAIL.IID = ITEM.IID
)
980踏んだ人は次スレよろ
だが断る!
こういうときは
>>979 がたてとけばいいんだけど、よくあるチキンレースですかね
>>4 の手法って何か呼び方があるのでしょうか?
あと、↓のような相関サブクエリで求める方法もよく見かけるのですが、
どちらの方が効率が良いのでしょう?
select A.ID,
A.DATE,
A.DATA
from TableName A
where A.DATE =
(
select max(B.DATE)
from TableName B
where A.ID = B.ID
)
>>982 データの偏りによってでが、サブクエリの呼び出しが一回で済む
>>4 の方が効率がよい。
ただ、WHERE句にもう一つ条件が付いて抽出される行が限定される場合で、
>>982 の
サブクエリでインデックス検索が効く場合は
>>982 の方が速い時もある。
982の方法は考え方が古そう・・・
古いってか、joinが一般的になる前から通用する方法だから オーソドックスだと思うが
joinが
>>4 の"inner join"句の事を言っているのであれば、
これが無くても
>>4 相当のことは相関サブクエリ無しで
出来るけど。
988 :
976 :2011/03/04(金) 15:39:25.12 ID:???
>>977 ,978
丁寧な解説ありがとうございます。
自分にとって新しい用語がたくさん出てきて勉強になります。
内容についての理解はまだ半分くらいですw
FROMにITEMを入れたのは、単純に平均値を出すSQLを書こうと思ったからですが
よくよく考えてみれば平均値を出すだけならITEM自体が不要ですね。
本当にありがとうございました。精進します。
>>985 詳しいソースは覚えていないが,
ISOとANSIの関係でSQL92/99ではFROM内にJOINを使う方を推奨していた.
OracleがWHERE句に書いていたので982の書き方が広まったのだと思う.
>>987 cross joinでしょうか?
それって
>>4 相当なんでしょうか。
>>990 cross joinも何も、
SELECT ... FROM T1 INNER JOIN T2 ON T1.a = T2.a
SELECT ... FROM T1, T2 WHERE T1.a = T2.a
上の二つは等価な結果を返すわけだから、INNER JOIN句を使えなく
たって相関サブクエリを使わずに書ける。
>>989 手元にDateの教科書とRamakrishnanの教科書という、共に米国で
よく使われる教科書(のそこそこ新しい版)があるのだけれども、面白い
ことに共にSQLの例でJOIN句の類は殆ど使われていないんだよね。
こういう教科書では結合演算は直積と制限の合成演算という理解から
入るから、JOIN句の類は単なるシンタックスシュガーなんでしょう。
JOINを使った場合と使わなかった場合で実行プランが大きく変わって
くる事って実際にあるのかなぁ。使った方がオプティマイザには優し
そうな気はしますが。
その教科書にはOUTER JOINはどう書いてあるの?
>>992 そこが「殆ど」に対する例外で、Date本では三値論理について説明した
章の中で「余談」と称して外部結合を紹介。外部結合とこの演算を関係
モデルに導入したCoddを散々Disってから、最後に申し訳程度にSQLに
おけるNullのサポートの一部としてOUTER JOINを紹介している。
ちなみに「SQL概論」は第4章だけど、この三値論理の章は第18章w
そんぐらい後回し、というかDate先生はNullがお嫌いらしい。
Ramakrishnan本でもSQLでのNullのサポートに関する節の中で軽く
OUTER JOINについて触れて終了。ここ以外にJOIN句は出てこない。
質問です。
・DBMS名とバージョン
MySQL5.5.9
・テーブルデータ(商品データみたいなもの)
ID CREATED
--+---------------------
1 サムネイルA 商品名A
2 サムネイルB 商品名B
3 NONE 商品名C
4 NONE 商品名D
5 NONE 商品名E
・欲しい結果
3,4,5行目の2列目にある『NONE』をテキストデータから変更したい。
・説明
テキストデータ上には
http:// 〜〜
http:// 〜〜
とアドレスがいっぱい並べて保存してあって、そのデータをデータベースの『NONE』と変更したいです。
何か方法があれば教えてほしいです。
>>994 その説明ではデータというのが何を指すか、
どういう鍵で取ってくるのかさっぱりわからない。
テーブルも列の数と列名が不整合だし・・・。
もう一度書きなおして。
>>991 自分はSQL初心者だけども、これ、気になってた。
上のほうは、JOINした結果に対してSELECT
下は直積した結果に対してSELECTするのだから、
普通に考えると効率的には明らかに上のがいいと思う
下から上への変換ってWHERE句の中身まで解析しなければ機械的には出来無いよね?
一般的な実装はどうなのだろう
なんかスレ終わりかけで尻切れトンボな感じになってしまってあれだけど
>>991 > SELECT ... FROM T1, T2 WHERE T1.a = T2.a
ってcross joinって呼ぶのかと思ってました。
それはともかく、どっちかというと
>>4 というより
>>982 と同じ感じになりそうなのですが。
>>996 SQLという言語の基本的な事柄なのだけれども、SQLは宣言型言語なので、
どういうデータを得たいのかという問題は記述するけど、どうやって結果
を得るのか方法については記述しないんだよね。そこはRDBMSの実装が
勝手に判断して最適な評価プランを選択するというスタンスになっている。
もちろん今日のまともな実装なら下のクエリについても直積はとらない。
推測のとおりWHERE句の中身も解析して上のクエリと大差ない評価プラン
を大抵は選択する。
>>997 CROSS JOIN句ってのもあるじゃないですか。それを言っているのかと。
ともあれサブクエリが相関かそうでないかは評価プランの選択にかなり
大きな影響を与える。
単純にFROM INNER JOIN - ON をFROM - WHEREに書き換えた程度では
オプティマイザは大抵は殆ど変わらない評価プランを選択すると思いますが、
相関サブクエリにした場合は全く異なるプランを選択する可能性も大かと。
1000 :
NAME IS NULL :2011/03/04(金) 23:18:06.54 ID:QTGnVt6O
null po
1001 :
1001 :
Over 1000 Thread このスレッドは1000を超えました。 もう書けないので、新しいスレッドを立ててくださいです。。。